如何使用JSON_TABLE处理无序ARRAY并按指定结构存储数据

SQL Server 解决方案 #

首先需要确保JSON数据为合法数组格式（原数据缺少外层数组包裹，修正后格式见代码），使用OPENJSON结合条件聚合实现：

DECLARE @json NVARCHAR(MAX) = N'[
    {
        "plaza": {
            "employee": 19769,
            "nodosEstructura": [
                {
                    "estructura": 5,
                    "descripcion": "IMI 2"
                },
                {
                    "estructura": 1,
                    "descripcion": "TKS"
                }
            ]
        }
    },
    {
        "plaza": {
            "employee": 19770,
            "nodosEstructura": [               
                {
                    "estructura": 1,
                    "descripcion": "INDUSTRIAL"
                },
                {
                    "estructura": 5,
                     "descripcion": "IMI 1"
                }
            ]
        }
    }
]';

SELECT 
    employee AS Employee,
    MAX(CASE WHEN estructura = 1 THEN descripcion END) AS Estructura1,
    MAX(CASE WHEN estructura = 5 THEN descripcion END) AS Estructura5
FROM 
    OPENJSON(@json)
    WITH (
        employee INT '$.plaza.employee',
        nodosEstructura NVARCHAR(MAX) '$.plaza.nodosEstructura' AS JSON
    )
    CROSS APPLY OPENJSON(nodosEstructura)
    WITH (
        estructura INT '$.estructura',
        descripcion VARCHAR(100) '$.descripcion'
    )
GROUP BY 
    employee;

逻辑说明 #

1. 用OPENJSON解析外层JSON，提取员工号employee和未解析的nodosEstructura数组。
2. 通过CROSS APPLY将数组拆分为多行数据，每行对应一个estructura条目。
3. 用CASE语句配合MAX聚合函数，根据estructura的值将描述信息映射到对应的列，不受数组顺序影响。

Python 解决方案 #

借助pandas和json库快速实现数据转换：

import pandas as pd
import json

# 修正后的合法JSON数据
json_data = '''
[
    {
        "plaza": {
            "employee": 19769,
            "nodosEstructura": [
                {
                    "estructura": 5,
                    "descripcion": "IMI 2"
                },
                {
                    "estructura": 1,
                    "descripcion": "TKS"
                }
            ]
        }
    },
    {
        "plaza": {
            "employee": 19770,
            "nodosEstructura": [               
                {
                    "estructura": 1,
                    "descripcion": "INDUSTRIAL"
                },
                {
                    "estructura": 5,
                     "descripcion": "IMI 1"
                }
            ]
        }
    }
]
'''

# 解析JSON并整理数据
data = json.loads(json_data)
rows = []
for item in data:
    plaza_info = item['plaza']
    emp_id = plaza_info['employee']
    # 将nodosEstructura转为字典，直接通过estructura值取描述
    struct_map = {entry['estructura']: entry['descripcion'] for entry in plaza_info['nodosEstructura']}
    rows.append({
        'Employee': emp_id,
        'Estructura1': struct_map.get(1),
        'Estructura5': struct_map.get(5)
    })

# 生成目标格式的DataFrame
result_df = pd.DataFrame(rows)
print(result_df)

逻辑说明 #

1. 解析JSON数据后，遍历每个员工条目。
2. 将每个员工的nodosEstructura数组转为字典，键为estructura的数值，值为对应的描述文本。
3. 根据字典键直接提取Estructura1和Estructura5的内容，不受数组顺序影响，最后组合成目标表结构的DataFrame。

内容的提问来源于stack exchange，提问作者Luis Guerra