Postgres 13.9：如何拼接其他JSON元素值更新嵌套JSON元素

环境与数据背景 #

我们使用Postgres 13.9，现有表结构及测试数据如下：

CREATE TABLE eco_val (
    Client varchar(50) NOT NULL,
    id varchar(50) NOT NULL,
    eco_js json NULL,
    CONSTRAINT pk_eco_val PRIMARY KEY (Client, ID)
);

INSERT INTO eco_val (Client,ID,eco_js) VALUES ('testclient','7193497_1',
  '{"ecoOptions": [
    {
      "ecoValue": [
        {
          "name": "A",
          "locale": "en_US"
        }
      ],
      "ecoOptionID": "7193497_1_1",
      "seq": 1,
      "defaultIndicator": false,
      "correctecoIndicator": false
    },
    {
      "ecoValue": [
        {
          "name": "1",
          "locale": "en_US"
        }
      ],
      "ecoOptionID": "7193497_1_2",
      "seq": 2,
      "defaultIndicator": false,
      "correctecoIndicator": true
    },
    {
      "ecoValue": [
        {
          "name": "2",
          "locale": "en_US"
        }
      ],
      "ecoOptionID": "7193497_1_1",
      "seq": 3,
      "defaultIndicator": false,
      "correctecoIndicator": true
    },
    {
      "ecoValue": [
        {
          "name": "5",
          "locale": "en_US"
        }
      ],
      "ecoOptionID": "7193497_1_7",
      "seq": 4,
      "defaultIndicator": false,
      "correctecoIndicator": true
    },
    {
      "ecoValue": [
        {
          "name": "ab",
          "locale": "en_US"
        }
      ],
      "ecoOptionID": "7193497_1_1",
      "seq": 5,
      "defaultIndicator": false,
      "correctecoIndicator": false
    },
    {
      "ecoValue": [
        {
          "name": "ad",
          "locale": "en_US"
        }
      ],
      "ecoOptionID": "7193497_1_2",
      "seq": 6,
      "defaultIndicator": false,
      "correctecoIndicator": false
    }
  ]
}');

问题说明 #

理想状态下，ecoOptionID的格式应为ID（如7193497_1）拼接下划线与对应元素的seq值，但因程序bug，JSON数组中的ecoOptionID出现错误值（如下表中加粗部分）：

比如seq=6的元素，ecoOptionID应为7193497_1_6而非7193497_1_2；seq=4的元素，ecoOptionID应为7193497_1_4而非7193497_1_7。

可通过以下SQL查询错误数据：

SELECT client,
    id, 
    ab ->>'ecoOptionID' ecoOptionID,
    ab ->>'seq' seq,
    count(*) OVER (PARTITION BY client,id,ab ->>'ecoOptionID' ORDER BY ab ->>'ecoOptionID') cnt,
    reverse(SUBSTRING(REVERSE(ab ->>'ecoOptionID') FROM 1 FOR POSITION('_' IN REVERSE(ab ->>'ecoOptionID')) -1)) last_digit_id
FROM eco_val a,
    jsonb_array_elements(eco_js::jsonb->'ecoOptions') ab
WHERE id = '7193497_1';

修正需求 #

需要批量将所有ecoOptionID更新为ID + 下划线 + seq的格式，且ecoOptions数组的元素数量不固定。修正后的JSON示例如下：

{"ecoOptions": [
    {
      "ecoValue": [
        {
          "name": "A",
          "locale": "en_US"
        }
      ],
      "ecoOptionID": "7193497_1_1",
      "seq": 1,
      "defaultIndicator": false,
      "correctecoIndicator": false
    },
    {
      "ecoValue": [
        {
          "name": "1",
          "locale": "en_US"
        }
      ],
      "ecoOptionID": "7193497_1_2",
      "seq": 2,
      "defaultIndicator": false,
      "correctecoIndicator": true
    },
    {
      "ecoValue": [
        {
          "name": "2",
          "locale": "en_US"
        }
      ],
      "ecoOptionID": "7193497_1_3",
      "seq": 3,
      "defaultIndicator": false,
      "correctecoIndicator": true
    },
    {
      "ecoValue": [
        {
          "name": "5",
          "locale": "en_US"
        }
      ],
      "ecoOptionID": "7193497_1_4",
      "seq": 4,
      "defaultIndicator": false,
      "correctecoIndicator": true
    },
    {
      "ecoValue": [
        {
          "name": "ab",
          "locale": "en_US"
        }
      ],
      "ecoOptionID": "7193497_1_5",
      "seq": 5,
      "defaultIndicator": false,
      "correctecoIndicator": false
    },
    {
      "ecoValue": [
        {
          "name": "ad",
          "locale": "en_US"
        }
      ],
      "ecoOptionID": "7193497_1_6",
      "seq": 6,
      "defaultIndicator": false,
      "correctecoIndicator": false
    }
  ]
}

解决方案 #

可以通过拆解JSON数组、逐个修正字段、重新聚合数组的方式实现批量更新，无需依赖固定的数组长度。具体SQL如下：

UPDATE eco_val
SET eco_js = (
  SELECT json_build_object(
    'ecoOptions',
    json_agg(
      jsonb_set(
        option_element::jsonb,
        '{ecoOptionID}',
        to_jsonb(concat(id, '_', (option_element->>'seq')::text))
      )::json
    )
  )
  FROM json_array_elements(eco_js->'ecoOptions') AS option_element
)
-- WHERE id = '7193497_1'; -- 如需单条更新则保留此条件，批量更新则删除

逻辑说明 #

1. 使用json_array_elements拆解ecoOptions数组，将每个元素单独处理；
2. 对每个元素，用jsonb_set替换ecoOptionID字段值，新值由id、下划线和seq拼接而成；
3. 用json_agg将修正后的元素重新聚合成数组；
4. 用json_build_object重构包含ecoOptions键的JSON对象，替换原eco_js字段值。

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