How to push a JSON object to a array in jsonb column in postgresql

Question

I m new in using jsonb in postgresql.

I have a following structure 

      [
          {
              "Id":1,
              "Name":"Emilia"
          },
          {
              "Id":2,
              "Name":"sophia"
          },
          {
              "Id":3,
              "Name":"Anna"
          },
          {
              "Id":4,
              "Name":"Marjoe"
          }
      ]

This structure is stored in jsonb column (items).I need to append {"Id":5,"Name":"Linquin"} in this array.and set it back to jsonb column items.

I need something like this.

     [
          {
              "Id":1,
              "Name":"Emilia"
          },
          {
              "Id":2,
              "Name":"sophia"
          },
          {
              "Id":3,
              "Name":"Anna"
          },
          {
              "Id":4,
              "Name":"Marjoe"
          },
          {
              "Id":5,
              "Name":"Linquin"
          }
      ]

And please let me know any way to delete objects based on id....

Appreciate any help.Thanks...

what's your postgres version?.. – Vao Tsun May 03 '17 at 07:05 — Vao Tsun, May 03 '17 at 07:05
the version of postgres is PostgreSQL 9.6.1 .thanks – Rajesh kumar May 03 '17 at 09:07 — Rajesh kumar, May 03 '17 at 09:07

score 6 · Answer 1 · answered Oct 11 '17 at 08:33

6

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

UPDATE s101
SET j = j || '{"Id":5,"Name":"Linquin"}'::jsonb
WHERE id = 1;

answered Oct 11 '17 at 08:33

Sandip Mavani

120
2
5

Oto Shavadze · Accepted Answer · 2017-05-03T09:58:43.603

For add new element to jsonb array, as @VaoTsun said you can use concatenate

select j||'{"Id":5,"Name":"Linquin"}'::jsonb as newjosn   from (
    select '[
      {
          "Id":1,
          "Name":"Emilia"
      },
      {
          "Id":2,
          "Name":"sophia"
      },
      {
          "Id":3,
          "Name":"Anna"
      },
      {
          "Id":4,
          "Name":"Marjoe"
      }
    ]'::jsonb as j
) t

for removing element based on id, one way is this: (for example remove element which id=2)

select to_jsonb(array_agg(lst)) as newjson from (
    select  jsonb_array_elements('[
      {
          "Id":1,
          "Name":"Emilia"
      },
      {
          "Id":2,
          "Name":"sophia"
      },
      {
          "Id":3,
          "Name":"Anna"
      },
      {
          "Id":4,
          "Name":"Marjoe"
      }
    ]'::jsonb) as lst
) t
where lst->'Id' <> '2'

So, this method just gets all json array values as records of json elements, then selects all elements except element which have id=2, and then makes again json array from selected records.

Note, if you need actually update column, then this method requires that table should have unique/identity column (id column normally), because every json array should be grouped by its own id column. (Don't mix this id with your json arrays Id field).

score 0 · Answer 3 · edited May 23 '17 at 12:26

here is answer working for version >= 9.5 (9.4 method was introduced here earlier)

to update jsonb value, you need to run update set column = new_value where ...

to construct new value you can use jsonb_set if you know the exact index

, or just || operator:

t=# select jsonb_set(j,'{5}','{"Id":5, "Name":"x"}'),j||'{"Id":5, "Name":"x"}' from s101;
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------
jsonb_set | [{"Id": 1, "Name": "Emilia"}, {"Id": 2, "Name": "sophia"}, {"Id": 3, "Name": "Anna"}, {"Id": 4, "Name": "Marjoe"}, {"Id": 5, "Name": "x"}]
?column?  | [{"Id": 1, "Name": "Emilia"}, {"Id": 2, "Name": "sophia"}, {"Id": 3, "Name": "Anna"}, {"Id": 4, "Name": "Marjoe"}, {"Id": 5, "Name": "x"}]

to delete array item use - (Assuming Id key is accurate you should use Id-1 math to get the index):

t=# select j - 3 from s101;
-[ RECORD 1 ]-----------------------------------------------------------------------------------
?column? | [{"Id": 1, "Name": "Emilia"}, {"Id": 2, "Name": "sophia"}, {"Id": 3, "Name": "Anna"}]

my sample build:

t=# create table s101(j jsonb);
CREATE TABLE
Time: 37.734 ms
t=# insert into s101 select  ' [
t'#           {
t'#               "Id":1,
t'#               "Name":"Emilia"
t'#           },
t'#           {
t'#               "Id":2,
t'#               "Name":"sophia"
t'#           },
t'#           {
t'#               "Id":3,
t'#               "Name":"Anna"
t'#           },
t'#           {
t'#               "Id":4,
t'#               "Name":"Marjoe"
t'#           }
t'#       ]'::jsonb;
INSERT 0 1
Time: 1.070 ms

this works if i know the index to append.What if i dont know the index. How to do this.What comes inside jsonb_set method??.And for delete what if i want to delete only the 3rd object but i need to keep the 4th object.Thanks.... — Rajesh kumar, May 03 '17 at 09:10
to append without index, use '||' - it is in the answer. to delete only 3rd, use `jsonb_col - 2` as per formula in answer (Id-1) — Vao Tsun, May 03 '17 at 09:21

How to push a JSON object to a array in jsonb column in postgresql

3 Answers3