I'm using postgres 9.6, and my question is about updating a jsonb array column.

My table looks more or less like that:

ID Name MyArray
5  John [{"key":1,"key2":a}]
6  Mike [{"key":4,"key2":b}]

ID integer,Name varchar, MyArray jsonb

I would like to update the jsonb array column with new data, without overriding the existing data.

I tried the following without success:

UPDATE myTable SET MyArray= myArray || {"key3":4} 

UPDATE myTable SET MyArray= myArray::jsonb || {"key3":4}::jsonb

Any suggestions? Thanks

  • Your method worked for me. What do you want exactly? Can you please share what output you want to see? – Yavuz Selim Oct 8 '18 at 11:13

The || operator works since PostgreSQL 9.5 for jsonb types, as described on https://www.postgresql.org/docs/9.5/static/functions-json.html


SELECT '{"key":1, "key2": 3}'::jsonb as input,
('{"key":1, "key2": 3}'::jsonb || '{"key3":4}'::jsonb) as output;

| input              | output                      |
| ------------------ | --------------------------- |
| {"key":1,"key2":3} | {"key":1,"key2":3,"key3":4} |
  • This is not exactly what I was looking for. I'm trying to update array with new values, not to select them – Chenko Oct 8 '18 at 9:23
  • @Chenko It is trivial to extend that to an update, it was just to show you that || works but with jsonb. So your added update should work as is. – Patrick Mevzek Oct 8 '18 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.