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

Question

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?

Bergi · Accepted Answer · 2022-02-09 13:08:08Z

2

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;

edited Feb 9, 2022 at 13:08

answered Feb 9, 2022 at 12:54

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Franjo Pintarić Over a year ago

Thank you, both for the correct query and the explanation why mine was acting weird, tested the first one and it works great

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related