Add another key/value pair to all objects inside a jsonb array in POSTGRES-CodePudding

I have a table, let's call it myTable with the following structure

ID   | data
____________
uuid | jsonb

The data in the jsonb field is an array structured in the following way:

[
  {
    "valueA": "500",
    "valueB": "ABC",
  },
  {
    "valueA": "300",
    "valueB": "CDE",
  }
]

What I want to do is transform that data by adding a valueC and set it to valueA, for all the objects inside the 'data' jsonb array.

This is the result I want:

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

I tried doing it with the following query:

UPDATE myTable
SET data = d.json_array
FROM (
    SELECT
        jsonb_agg(
                 jsonb_set(elems, '{valueC}', elems->'valueA')
        ) as json_array
    FROM
        myTable,
        jsonb_array_elements(data) elems
) d;

This worked for some on the entries in myTable, but for some it went crazy and created 300 additional entries, along with the ones I previosly had.

What am I missing in my query?

CodePudding user response：

Those FROM statements are unnecessary. You shouldn't need one on the UPDATE clause at all - your problem is that the subquery does a FROM myTable and then basically self-joins (by cartesian product) this subquery result against the whole myTable. The last (or really, any) UPDATE to each row wins, depending on the join order, making your result unpredictable.

I'd move the subquery in the SET clause where it only refers to the data of the row that is being updated:

UPDATE myTable
SET data = (
    SELECT jsonb_agg(
        jsonb_set(elems, '{valueC}', elems->'valueA')
    )
    FROM jsonb_array_elements(data) elems
);

Alternatively, you would need to add a JOIN condition between the updated table and the FROM result:

UPDATE myTable u
SET data = d.json_array
FROM (
    SELECT
        id,
        jsonb_agg(
                 jsonb_set(elems, '{valueC}', elems->'valueA')
        ) as json_array
    FROM
        myTable,
        jsonb_array_elements(data) elems
    GROUP BY
        id
) d
WHERE
    u.id = d.id;