1

Let's say I have a database jsonb column with a json array in the following format:

[
  {
    "test1key": "test1Value"
  },
  {
    "test2key": "test2Value"
  },
  {
    "test3key": "test3Value"
  }
]

Is it possible, in a single query, to update the value of "test1key" as well as "test2key"? If so, how?

3
  • @a_horse_with_no_name Good question - this is something that bothers me as well. Is it possible also to update an array that contains more than one element? But at least, for my implementation for now, I will only have a single element for every array. Commented Oct 14, 2019 at 8:53
  • I see one array with three JSON elements... Commented Oct 14, 2019 at 8:54
  • Should they get the same value or different? Please add expected output Commented Oct 14, 2019 at 8:55

1 Answer 1

1

The main question is why do you have to do it? The JSON structure is unnecessarily complex and illogical. You should keep JSON data as simple as possible. You can store the same data in a single object:

{"test1key": "test1Value", "test2key": "test2Value", "test3key": "test3Value"}

and then the update is as simple as

update my_table
set json_col = json_col || '{"test1key": "newValue1", "test2key": "newValue2"}'
where id = 1

Even if the data comes from outside, you can always convert it to a simpler and more efficient form before saving to the database. Too complex data structure makes its processing (especially updates) difficult and inefficient:

update my_table t1
set json_col = new_array
from (
    select id, jsonb_agg(jsonb_build_object(old_key, coalesce(new_value, old_value))) as new_array
    from my_table
    cross join jsonb_array_elements(json_col) as a(elem)
    cross join jsonb_each_text(elem) as e(old_key, old_value)
    left join jsonb_each_text(
        '{"test1key": "newValue1", "test2key": "newValue2"}'
        ) as v(new_key, new_value) 
    on old_key = new_key
    group by id
    ) t2
where t1.id = 1 and t2.id = t1.id;

Online demo: db<>fiddle.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.