postgresql:如何更新JSONB以在嵌套数组中添加新键 [英] postgresql: How to update JSONB to add new key in nested array

问题描述

如何更新JSONB以将所有记录的新键添加到嵌套数组(对于数组的所有项目)中.

How to update the JSONB to add new key into nested array (for all items of array) for all the records.

我指的是链接 表结构为:

CREATE TABLE orders (
    id   serial PRIMARY KEY,
    data jsonb
);

给定的json是:

{
  "Number": "555",
  "UserId": "1",
  "Items": [
    {
      "ProductId": "1", 
      "Name": "TV",
      "Price": "300.00"
    }, 
    {
      "ProductId": "2", 
      "Name": "Mechanical Keyboard",
      "Price": "120.00"
    }
  ]
}

要向每个数组项中添加新元素，请执行以下查询:

To add new element into each array item the following query is given:

UPDATE orders
SET data = jsonb_set(
    data, 
    '{Items}',      -- the array in which we operate
    to_jsonb(
    (WITH ar AS(
      WITH temp AS(
        SELECT data->'Items' AS items   -- the array in which we operate
        FROM orders
        WHERE id = 1    -- the filtered order we are updating
      )
      SELECT jsonb_set(
        jsonb_array_elements(items),
        '{Quantity}',   -- the new field we are adding
        '"1"',          -- the value of the new field
        true)
      FROM temp)
     SELECT (array_agg(ar.jsonb_set))
     FROM ar)),
  false)
WHERE id = 1;

执行上述查询后的输出:

Output after executing above query:

{
  "Number": "555",
  "UserId": "1",
  "Items": [
    {
      "ProductId": "1", 
      "Name": "TV",
      "Price": "300.00",
      "Quantity": "1"
    }, 
    {
      "ProductId": "2", 
      "Name": "Mechanical Keyboard",
      "Price": "120.00",
      "Quantity": "1"
    }
  ]
}

但是上面只会在id=1处更新json.对于订单中的所有行，更新与上面相同的JSON需要进行哪些更改?

But above will update the json only where id=1. What changes are required to update JSON same as above for all rows in orders ?

推荐答案

您无需在SET语句中进行SELECT data->'Items' AS items FROM orders WHERE id = 1 CTE-您只需直接引用data->'Items'，它将采用当前更新的行，就像您在data = jsonb_set(data, …)中所做的一样.因此，您可以简化为

You don't need to do that SELECT data->'Items' AS items FROM orders WHERE id = 1 CTE inside the SET statement - you can just refer to data->'Items' directly and it will take the currently updated row, just like you already do in data = jsonb_set(data, …). So you can simplify to

UPDATE orders
SET data = jsonb_set(
    data, 
    '{Items}',      -- the array in which we operate
    (SELECT jsonb_agg(jsonb_set(
        item,
        '{Quantity}',   -- the new field we are adding
        '"1"',          -- the value of the new field
        true))
     FROM jsonb_array_elements(data->'Items')) AS item, -- the array in which we operate
  false)
WHERE id = 1;

(我也摆脱了其他CTE，并用jsonb_agg替换了to_jsonb(array_agg(…)))

(I also got rid of the other CTE and replaced to_jsonb(array_agg(…)) with jsonb_agg)

现在，更新所有行所需要做的就是省略WHERE子句.

Now all you need to do for updating all rows is omitting the WHERE clause.

这篇关于postgresql:如何更新JSONB以在嵌套数组中添加新键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！