Add element to JSON object in Postgres

Question

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?

Answer 1

If you upgrade to PG9.5.1, then you can use sql operator || to merge jsonb, example

select '{"a":1}'::jsonb || '{"a":2, "b":2}'::jsonb 

will return {"a": 2, "b": 2}

If you can't upgrade to pg9.5.1, IMHO, doing the job in your code will be a better choice. You can parse old jsonb string as a map, and then update the map, then convert to string and update db-record.

And if we want to update (add) a JSONB field:

UPDATE <table> SET <field-name> = <field-name> || '{"a": 1}'::jsonb WHERE id = <some id>