current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question
1  
Try reading stackoverflow.com/questions/10560394/… . It may help you. –  Igor Romanchenko Nov 28 '12 at 22:49
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
1  
Obviously not the answer I wanted, but agreeable none the less. Thanks. –  thepriebe Nov 29 '12 at 17:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.