CODEWITHSUNDEEP
arrayspostgresqlsql-updatejsonb
virgo47
virgo47

Reputation: 2333

How to modify array under specific JSONB key in PostgreSQL?

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 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.

Upvotes: 0

Views: 1088

Answers (1)

AlexElin
AlexElin

Reputation: 1589

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>

Upvotes: 0

Related Questions