PostgreSQL中jsonb_set处理嵌套集合返回null问题求助

尝试使用jsonb_set更新PostgreSQL表中的嵌套JSON结构，目标是为payload字段内slots -> bannerXCreatorSettings -> additionalFields数组的每个元素添加selectOptions空数组字段，但执行SQL后，返回的payload_update中slots数组变为[null, null]。

表中原始数据 #

原始JSON payload #

{
  "slots": [
    {
      "bannerXCreatorSettings": {
        "additionalFields": [
          {
            "label": "test-label",
            "enabled": true,
            "mandatory": false,
            "additionalFieldId": "label",
            "maxCharacterLimit": 22,
            "additionalFieldType": "TEXT",
            "colorHexOptionsList": []
          },
          {
            "label": "test-label-2",
            "enabled": true,
            "mandatory": false,
            "additionalFieldId": "label2",
            "maxCharacterLimit": 55,
            "additionalFieldType": "TEXT",
            "colorHexOptionsList": []
          }
        ]
      }
    }
  ]
}

期望的JSON结构 #

{
  "slots": [
    {
      "bannerXCreatorSettings": {
        "additionalFields": [
          {
            "label": "test-label",
            "enabled": true,
            "mandatory": false,
            "additionalFieldId": "label",
            "maxCharacterLimit": 22,
            "additionalFieldType": "TEXT",
            "colorHexOptionsList": [],
            "selectOptions": [] <-- 新增字段
          },
          {
            "label": "test-label-2",
            "enabled": true,
            "mandatory": false,
            "additionalFieldId": "label2",
            "maxCharacterLimit": 55,
            "additionalFieldType": "TEXT",
            "colorHexOptionsList": [],
            "selectOptions": [] <-- 新增字段
          }
        ]
      }
    }
  ]
}

执行的SQL语句 #

select
    id,
    payload,
    row_version,
    jsonb_set(
        payload,
        '{slots}',
        (select
            jsonb_agg(
                jsonb_set(
                    slot_elem,
                     '{bannerXCreatorSettings}',
                    jsonb_set(
                        slot_elem -> 'bannerXCreatorSettings',
                        '{additionalFields}',
                        (
                           select
                              jsonb_agg(
                                jsonb_set(
                                   field_element,
                                   '{selectOptions}',
                                      jsonb_build_array()))
                                    from jsonb_array_elements(
                                        slot_elem -> '{bannerXCreatorSettings}' #>'{additionalFields}') WITH ORDINALITY a_t(field_element, idx_1))      
                                    )
                            )
                        )
                        FROM
                            jsonb_array_elements(
                                payload #> '{slots}') WITH ORDINALITY t(slot_elem, idx))) as payload_update

实际错误结果 #

{
  "slots": [
    null,
    null
  ]
}

1. JSON路径语法错误：在jsonb_array_elements(slot_elem -> '{bannerXCreatorSettings}' #>'{additionalFields}')中，->操作符后误用带大括号的键名写法，正确的路径引用应该直接使用键名（无需大括号），比如slot_elem -> 'bannerXCreatorSettings' -> 'additionalFields'。
2. 嵌套操作返回值异常：内层jsonb_set因路径错误返回null，最终被jsonb_agg聚合为包含null的数组。

方案1：用JSON合并简化逻辑 #

SELECT
    id,
    payload,
    row_version,
    jsonb_set(
        payload,
        '{slots}',
        (
            SELECT jsonb_agg(
                jsonb_set(
                    slot_elem,
                    '{bannerXCreatorSettings, additionalFields}',
                    (
                        SELECT jsonb_agg(
                            field_element || '{"selectOptions": []}'::jsonb
                        )
                        FROM jsonb_array_elements(slot_elem -> 'bannerXCreatorSettings' -> 'additionalFields') AS field_element
                    )
                )
            )
            FROM jsonb_array_elements(payload -> 'slots') AS slot_elem
        )
    ) AS payload_update

方案2：修正路径的嵌套jsonb_set写法 #

SELECT
    id,
    payload,
    row_version,
    jsonb_set(
        payload,
        '{slots}',
        (
            SELECT jsonb_agg(
                jsonb_set(
                    slot_elem,
                    '{bannerXCreatorSettings}',
                    jsonb_set(
                        slot_elem -> 'bannerXCreatorSettings',
                        '{additionalFields}',
                        (
                            SELECT jsonb_agg(
                                jsonb_set(field_element, '{selectOptions}', '[]'::jsonb)
                            )
                            FROM jsonb_array_elements(slot_elem -> 'bannerXCreatorSettings' -> 'additionalFields') AS field_element
                        )
                    )
                )
            )
            FROM jsonb_array_elements(payload -> 'slots') AS slot_elem
        )
    ) AS payload_update

说明 #

• 使用||操作符合并JSON对象是更简洁的方式，避免多层嵌套jsonb_set的复杂度。
• 修正路径写法：通过->操作符逐层引用嵌套键，确保正确获取目标数组。
• 如果需要仅给特定类型的字段添加selectOptions，可在子查询中添加WHERE条件，比如WHERE field_element ->> 'additionalFieldType' = 'SELECT'。

内容的提问来源于stack exchange，提问作者Christopher Wade