jsonstringpostgresqlselectreplace

Add element to JSON object in Postgres

I have a text field in a database (Postgres 9.2.1) with a JSON blob in it. It looks something similar to this except all on a single line, obviously:

{
  "keyword": {
    "checked": "1",
    "label": "Keyword"
  },
  "agency_name": {
    "checked": "0",
    "label": "Agency Name"
  }
}

I need to add an element to json array so that it looks like this:

{
  "keyword": {
    "checked": "1",
    "label": "Keyword"
  },
  "something_new": {
    "checked": "1",
    "label": "Something New"
  },
  "agency_name": {
    "checked": "0",
    "label": "Agency Name"
  }
}

I'm not as concerned about the placement of the new array element. It could be after "agency_name". Is there an easy way to do this in Postgres?

Solution

  • Even I had the same problem, I wanted to dynamically append new elements to jsonb[].

    Assume column_jsonb[] = [{"name":"xyz","age":"12"}]

    UPDATE table_name
   SET column_jsonb[] = array_append(column_jsonb[],'{"name":"abc","age":"22"}');

    Result : [{"name":"xyz","age":"12"},{"name":"abc","age":"22"}]