PostgreSQL - Add key to each objects of an JSONB array

Question

My database contains a table which has a column with jsonb type, and I want to update a part of these data using functions/operators from postgreSQL. Given we have this:

{
  "A":[
    {"index":"1"},
    {"index":"2"}
  ],
  "B":[
    {"index":"3"},
    {"index":"4"}
  ]
}

Let's say we went to add a key with an empty array to objects from "A" array, in order to have:

{
  "A":[
    {"index":"1", "myArray":[]},
    {"index":"2", "myArray":[]}
  ],
  "B":[
    {"index":"3"},
    {"index":"4"}
  ]
}

How can I proceed?

I've already tried this kind of things without success:

UPDATE myTable SET myColumn = (myColumn::jsonb)->>'A' || '{"myArray":[]}'

UPDATE myTable SET myColumn = (
  SELECT jsonb_agg(jsonb_set(
    element, 
    array['A'], 
    to_jsonb(((element ->> 'A')::jsonb || '{"myArray":[]}')::jsonb)
  ))
  FROM jsonb_array_elements(myColumn::jsonb) element
)::json

UPDATE myTable SET myColumn = (
  SELECT jsonb_each((element ->> 'A')::jsonb) || '{"myArray":[]}'::jsonb
  FROM jsonb_array_elements(myColumn::jsonb) element
)::json

Obviously, all of these tests have been big failure. I have difficulties to understand how works postgreSQL functions.

Somebody can help?

Bergi · Accepted Answer · 2019-11-20 17:13:48Z

4

The approach with jsonb_array_elements and jsonb_set was the right idea, but somehow you nested them the wrong way round:

UPDATE myTable SET myColumn = jsonb_set(myColumn, '{A}', (
  SELECT jsonb_agg( element || '{"myArray":[]}' )
  FROM jsonb_array_elements(myColumn -> 'A') element
));

^{(online demo)}

Btw if your column already has jsonb data type, you shouldn't need any casts.

answered Nov 20, 2019 at 17:13

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

6 Comments

EVDW Over a year ago

Thanks a lot, it works! But I added some changes because in fact, my column was json :') so I have included "myColumn" into a to_jsonb() ;)

fedor.belov Over a year ago

had to add WHERE jsonb_array_length(myColumn -> 'A') > 0;

Bergi Over a year ago

@fedor.belov The query was written under the assumption that every json object has an A property that is an array. If that's not the case, yes, you need to add a condition or create the array.

Bergi Over a year ago

@denyzprahy It's an alias for the relation returned by the jsonb_array_elements function. You can add AS before it to clarify. It ambiguously denotes either the column or the whole single-column relation iirc.

Bergi Over a year ago

@denyzprahy See postgresql.org/docs/current/sql-select.html#SQL-FROM and in detail postgresql.org/docs/current/…

|

Collectives™ on Stack Overflow

PostgreSQL - Add key to each objects of an JSONB array

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related