thepriebe thepriebe - 5 months ago 86
JSON Question

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?

Answer

PostgreSQL does not yet have much in the way of JSON support functions: all I can see are ones like array_to_json, which could be useful if there was a corresponding way to convert the original JSON into an array, which you could then manipulate to add that additional element before converting back to JSON.

Possibly the best thing is to use a PL language to manipulate the JSON. One obvious one would be PLV8, which provides JavaScript programming functionality in PostgreSQL. You would write a user-defined function in JavaScript which would manipulate the JSON blob accordingly:

Of course, many other PL languages like Java, Python or Perl may be just as good at working with JSON data, and possibly easier to install on your system. User-defined functions can be written in each of these if you have them set up.