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"
          }
      ]

1. 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"
          }
      ]

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

Appreciate any help.Thanks...

Answer 1

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

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

Answer 2

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).

Answer 3

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