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

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