How to append object in postgresql jsonb field while update

Question

I want to update my jsonb field it store array of object. i want to add new object in it.

CREATE TABLE justjson ( id INTEGER, doc JSONB);
INSERT INTO justjson VALUES ( 1, '[
  {
    "name": "abc",
    "age": "22"
  },
  {
    "name": "def",
    "age": "23"
  }
]');

then value like

select doc from justjson;

doc
[{"age": "22", "name": "abc"}, {"age": "23", "name": "def"}]

Now I want to append new object in this jsonb

{"age": "45", "name": "xyz"}

How can i update this field?

my output such as

doc
    [{"age": "22", "name": "abc"}, {"age": "23", "name": "def"},{"age": "45", "name": "xyz"}]

Look here for Concatenate two jsonb values into a new jsonb value. — klin
– klin, Commented Oct 10, 2017 at 6:47

Laurenz Albe · Accepted Answer · 2017-10-10 08:26:36Z

6

Use the concatenation operator || to append an element to an array:

UPDATE justjson
SET doc = doc || '{"age": "45", "name": "xyz"}'::jsonb
WHERE is = 1;

edited Oct 10, 2017 at 8:26

answered Oct 10, 2017 at 7:01

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

3 Comments

Toby Speight Over a year ago

Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation would greatly improve its long-term value by showing why this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please edit your answer to add some explanation, including the assumptions you've made.

Laurenz Albe Over a year ago

@YvetteColomb Such an ardent appeal cannot remain unheard.

Sandip Mavani Over a year ago

@LaurenzAlbe How can I update in jsonb such as i want to set age by name Ex. where name = xyz set age = 46 in above table

Collectives™ on Stack Overflow

How to append object in postgresql jsonb field while update

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related