主页 / dba / 问题 / 315685
Accepted
sharknado
Asked: 2022-08-17 08:54:06 +0800 CST

将键和值添加到键尚不存在的 jsonb 数组的元素中

  • 772

我有一个tbl具有以下结构的表：

CREATE TABLE tbl (
  org  text
, data jsonb
);

字段中的数据jsonb是一个数组，结构如下：

INSERT INTO tbl VALUES
('SOMETHING'
, '[
      {
        "type": "XYZ",
        "valueA": "500",
        "valueB": "ABC"
      },
      {
        "type": "ABC",
        "valueA": "300",
        "valueB": "CDE"
      }
   ]')
;

我想将一个键添加到对象具有值为 的键valueC的元素中。data"type""XYZ"

valueC的值将是一个字符串数组。数组的值将取决于org列的值。

我想对所有行执行此操作，如果存在特定行org，并且列中的jsonb数组data包含带有 的对象"type": "XYZ"，那么我会得到以下结果：

[
  {
    "type": "XYZ",
    "valueA": "500",
    "valueB": "ABC",
    "valueC": ["SOMETHING"],
  },
  {
    "type": "ABC",
    "valueA": "300",
    "valueB": "CDE",
  }
]

我还想确保此脚本仅valueC在与条件匹配的对象中不存在时才运行，因此除非需要，否则在迁移/回滚期间不会重新运行。

这是我到目前为止所拥有的，但是当它没有找到子查询的结果并且我无法弄清楚如何仅在valueC不存在的情况下运行它时它不起作用：

UPDATE tbl SET  
  data = jsonb_set(
    data, 
    '{data}', 
   (SELECT jsonb_agg(elem ||'{"valueC":["SOMETHING"]}') FROM jsonb_array_elements(data->'data') as elem where elem ->> 'type' = 'XYZ')
  )
WHERE org = 'SOMETHING';
postgresql update

1 个回答

  1. Best Answer

    SQL/JSON 路径表达式使这变得简短而高效。需要Postgres 12 或更高版本：

    UPDATE tbl
SET    data = (SELECT jsonb_agg(CASE WHEN jsonb_path_exists(elem, '$ ? (@.type == "XYZ") ? (!exists (@.valueC))')
                                     THEN elem || '{"valueC":["SOMETHING"]}'
                                     ELSE elem END)
               FROM   jsonb_array_elements(data) AS elem)
WHERE  org = 'SOMETHING'
AND    jsonb_path_exists(data, '$[*] ? (@.type == "XYZ") ? (!exists (@.valueC))')
RETURNING *;

    db<>在这里摆弄

    这允许一次更新多个符合条件的数组元素。

    WHERE首先，在子句中廉价地识别符合条件的行。（我们不想处理所有行。）

    然后，在取消嵌套之后jsonb_array_elements()，重新检查CASE表达式以仅修改符合条件的数组元素。

    如果表很大，一定要有索引。看：

    • 2

相关问题