codeisforeva codeisforeva -4 years ago 124
JSON Question

How can I append a given key to a PostgreSQL JSONB array?

I have a JSONB store.

requests: {
"0000": ["1111"],
"1111": ["2222"]
}


I can add into the store just fine when I have an initial value with:

UPDATE users SET requests = jsonb_insert(requests::jsonb, '{2222}', $$["3333"]$$);


However if I need to append to the array, it acts like I'm trying to replace the value and throws:

UPDATE users SET requests = jsonb_insert(requests::jsonb, '{0000}', $$["4444"]$$);
ERROR: cannot replace existing key
HINT: Try using the function jsonb_set to replace key value.


Desired output would be:

requests: {
"0000": ["1111", "4444"],
"1111": ["2222"]
}


Seems I'm missing a simple
array_append
or something but can't manage to figure it out.

Answer Source

I was not sure about your structure, but with such:

create table users (requests jsonb);

You are missing array index in your path:

t=# select * from users ;
      requests
--------------------
 {"0000": ["1111"]}
 {"1111": ["2222"]}
(2 rows)

Time: 0.257 ms
t=# UPDATE users SET requests = jsonb_insert(requests::jsonb, '{0000,1}', '"4444"');
UPDATE 2
Time: 7.552 ms
t=# select * from users ;
          requests
----------------------------
 {"0000": ["1111", "4444"]}
 {"1111": ["2222"]}
(2 rows)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download