How to modify array under specific JSONB key in PostgreSQL?

Question

We're storing various heterogeneous data in a JSONB column called ext and under some keys we have arrays of values. I know how to replace the whole key (||). If I want to add one or two values I still need to extract the original values (that would be ext->'key2' in the example lower) - in some cases this may be too many. I realize this is trivial problem in relational world and that PG still needs to overwrite the whole row anyway, but at least I don't need to pull the unchanged part of the data from DB to the application and push them back.

I can construct the final value of the array in the select, but I don't know how to merge this into the final value of ext so it is usable in UPDATE statement:

select ext, -- whole JSONB
    ext->'key2', -- JSONB array
    ARRAY(select jsonb_array_elements_text(ext->'key2')) || array['asdf'], -- array + concat
    ext || '{"key2":["new", "value"]}' -- JSONB with whole "key2" key replaced (not what I want)
from (select '{"key1": "val1", "key2": ["val2-1", "val2-2"]}'::jsonb ext) t

So the question: How to write such a modification into the UPDATE statement?

Example uses jsonb_*_text function, some values are non-textual, e.g. numbers, that would need non _text function, but I know what type it is when I construct the query, no problem here.

We also need to remove the values from the arrays as well, in which case if the array is completely empty we would like to remove the key from the JSONB altogether. Currently we achieve this with this expression in the UPDATE statement coalesce(ext, '{}')::jsonb - <array of items to delete> || <jsonb with additions> (<parts> are symbolic here, we use single JDBC parameter for each value). If the final value of the array is empty, the key for that value goes into the first array, otherwise the final value appears int he JSONB after || operator.

To be clear:

I know the path to the JSONB value I want to change - it's actually always a single key on the top level.
I know whether that key stores single value (no problem for those) or array (that's where I don't have satisfying solution yet), because we know the definitions of each key, this is stored separately.
I need to add and/or remove multiple values I provide, but I don't know what is in the array at that moment - that's the whole point, so that application doesn't need to read it.
I may also want to replace the whole array under the key, but this is trivial case and I know how to do this.
Finally, if removal results in an empty array, we'd like to get rid of the key as well.

I could probably write a function doing it all if necessary but I've not committed to that yet.

Obviously, restructuring the data out of that JSONB column is not an option. Eventually I want to make it more flexible and data with these characteristics would go to some other table, but at this moment we're not able to do it with our application.

I think it's better to split up the question to 2 specific ones: modify and remove (make as new one). — AlexElin
– AlexElin, Commented Sep 25, 2021 at 10:03
I understand, but for me it's a single problem. I know what to add and remove at the same time and I need solution, possible one, that actually works together for both types of modifications. — virgo47
– virgo47, Commented Sep 26, 2021 at 14:07

AlexElin · Accepted Answer · 2021-09-25 10:58:21Z

0

You can use jsonb_set to modify an array which is placed under some key.

To update a value in an array you should specify a zero-based index within the array in the below example.
To add a new element on a start/end - specify negative/positive index which is greter than array's length.

UPDATE <table>
SET ext = jsonb_set(ext, '{key2, <index>}', '5')
WHERE <condition>

answered Sep 25, 2021 at 10:58

AlexElin

1,7971 gold badge19 silver badges29 bronze badges

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

1 Comment

virgo47 Over a year ago

Thanks for the answer, I realized I need to clarify the question, so I added more explicit bullets of what I work with. I can use jsonb_insert for adding a single value, but that probably means I'd need to nest these to add multiple values?

Collectives™ on Stack Overflow

How to modify array under specific JSONB key in PostgreSQL?

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