My database contains a table which has a column withjsonb type, and I want to update a part of these data usingfunctions/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)::jsonUPDATE myTable SET myColumn = ( SELECT jsonb_each((element ->> 'A')::jsonb) || '{"myArray":[]}'::jsonb FROM jsonb_array_elements(myColumn::jsonb) element)::jsonObviously, all of these tests have been big failure. I have difficulties to understand how works postgreSQL functions.
Somebody can help?
1 Answer1
The approach withjsonb_array_elements andjsonb_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));Btw if your column already hasjsonb data type, you shouldn't need any casts.
Sign up to request clarification or add additional context in comments.
6 Comments
EVDW
Thanks a lot, it works! But I added some changes because in fact, my column was
json :') so I have included "myColumn" into ato_jsonb() ;)
fedor.belov
had to add
WHERE jsonb_array_length(myColumn -> 'A') > 0;
Bergi
@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
@denyzprahy It's an alias for the relation returned by the
jsonb_array_elements function. You can addAS before it to clarify. It ambiguously denotes either the column or the whole single-column relation iirc.Bergi
@denyzprahy Seepostgresql.org/docs/current/sql-select.html#SQL-FROM and in detailpostgresql.org/docs/current/…
|
Explore related questions
See similar questions with these tags.