如何用SQL解析JSON中的bandEUTRA字段并拆分至不同列？

问题场景 #

现有如下JSON数据片段：

"supportedBandCombinationList": [
    {
        "bandList": [
            {"eutra": {"bandEUTRA": 1, "ca-BandwidthClassDL-EUTRA": "a"}},
            {"eutra": {"bandEUTRA": 3, "ca-BandwidthClassDL-EUTRA": "c"}},
            {"eutra": {"bandEUTRA": 28, "ca-BandwidthClassDL-EUTRA": "a", "ca-BandwidthClassUL-EUTRA": "a"}},
            {"nr": {"bandNR": 78, "ca-BandwidthClassDL-NR": "a", "ca-BandwidthClassUL-NR": "a"}}
        ],
        "mrdc-Parameters": {"dynamicPowerSharingENDC": "supported", "simultaneousRxTxInterBandENDC": "supported"},
        "featureSetCombination": 0
    }
]

当前使用的SQL语句会把所有bandEUTRA字段提取到同一列，需求是：

1. 单独引用第一个bandEUTRA字段
2. 将所有bandEUTRA拆分到不同列

解决方案 #

1. 提取第一个bandEUTRA字段 #

通过在JSON路径中指定数组索引[0]，结合jsonb_path_query_first函数直接定位第一个eutra的bandEUTRA：

TRIM(
    jsonb_path_query_first(
        layer3::jsonb,
        '$[*].layers[*].layers[*].fields.pdu."UE-MRDC-Capability"."rf-ParametersMRDC".supportedBandCombinationList[0].bandList[0].eutra.bandEUTRA'
    )::VARCHAR,
    '[]'
) AS "bandeutra_first"

或者使用jsonb_extract_path_text简化定位：

jsonb_extract_path_text(
    layer3::jsonb,
    '{layers,0,layers,0,fields,pdu,UE-MRDC-Capability,rf-ParametersMRDC,supportedBandCombinationList,0,bandList,0,eutra,bandEUTRA}'
) AS "bandeutra_first"

2. 将所有bandEUTRA拆分到不同列 #

由于bandList数组混合了eutra和nr类型条目，先过滤出eutra条目，再按位置拆分到不同列，用CTE展开数组后结合条件聚合实现：

WITH expanded_sbc AS (
    SELECT
        id, -- 替换为你的表主键，用于关联原记录
        jsonb_array_elements(
            layer3::jsonb #> '{layers,0,layers,0,fields,pdu,UE-MRDC-Capability,rf-ParametersMRDC,supportedBandCombinationList}'
        ) AS sbc_item
    FROM your_table -- 替换为你的表名
),
expanded_bands AS (
    SELECT
        id,
        jsonb_array_elements(sbc_item -> 'bandList') AS band_item,
        row_number() OVER (PARTITION BY id) AS band_seq
    FROM expanded_sbc
),
filtered_eutra AS (
    SELECT
        id,
        band_seq,
        (band_item -> 'eutra' ->> 'bandEUTRA') AS bandeutra
    FROM expanded_bands
    WHERE band_item ? 'eutra' -- 仅保留eutra类型的频段条目
)
SELECT
    id,
    MAX(CASE WHEN band_seq = 1 THEN bandeutra END) AS bandeutra_1,
    MAX(CASE WHEN band_seq = 2 THEN bandeutra END) AS bandeutra_2,
    MAX(CASE WHEN band_seq = 3 THEN bandeutra END) AS bandeutra_3
    -- 可根据实际bandEUTRA数量添加更多CASE分支
FROM filtered_eutra
GROUP BY id;

内容的提问来源于stack exchange，提问作者AQEL AL KHARASANI