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

Question

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.

with t(x) as (values('{"0":["1"],"1":["2"]}'::jsonb)) select x, jsonb_set(x, '{0}', x->'0'||'"3"') from t; — Abelisto
– Abelisto, Commented Jun 11, 2017 at 18:58

Vao Tsun · Accepted Answer · 2017-06-12 13:44:53Z

3

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)

answered Jun 12, 2017 at 13:44

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

1 Comment

codeisforeva Over a year ago

Ya that works :D! However now I have to do a compound query to retrieve the first result. Ok I will structure my data differently.

Collectives™ on Stack Overflow

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

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