我们正在使用Postgres 13.9和下面的表和JSON结构。

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。由于应用程序中的一个错误，json在创建时使用了错误的ecoOptionID值，如粗体所示（例如，下表中的7193497_1_2应该是7193497_1_6，7193497_1_7应该是7193497_1_4）：
| ID|拟生态群落|seq| CNT|最后数字标识|
| --|--|--|--|--|
| 邮编：7193497_1| 7193497_1_1| 1 | 3 | 1 |
| 邮编：7193497_1|7193497_1| 3 | 3 | 1 |
| 邮编：7193497_1|7193497_1| 5 | 3 | 1 |
| 邮编：7193497_1| 7193497_1_2| 2 | 2 | 2 |
| 邮编：7193497_1|7193497_1| 6 | 2 | 2 |
| 邮编：7193497_1|7193497_1_7| 4 | 1 | 7 |

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';

我们有一些数据需要更正。
我已经看到jsonb_set可以用于指定索引或路径的地方。不幸的是，我们的JSON中的ecoOptionID的数量没有预定义。
是否有办法更新所有要设置为字符串的ecoOptionID，该字符串是该元素的ID、下划线和seq的串联？
生成的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
    }
  ]
}');