Monday, May 04. 2015

Lately I've been experimenting with building semi-schemaless apps. These are apps
where much of the data may never be used for reporting aside from story telling and also that as time goes by some of these may be revisited
and converted to more structured fields for easier roll-up and reporting. For the front-end UI, I'm using AngularJS which naturally spits out data as JSON and can autobind to JSON data of any complexity.
My stored functions in PostgreSQL take JSON blobs as inputs spit it out into various tables and throws the whole thing in a jsonb field for later consumption (it's a bit redundant). Similarly they return JSON back. One of the things I wanted to be able to do was take this jsonb blob and tack on additional properties from well-structured fields or even a whole set of data like sub recordsets to feed back to my app in JSON.
While there are lots of functions in PostgreSQL 9.3/9.4 that can easily build json objects from records, aggregate rows, etc. I couldn't find a function that allowed me to just add a property to an existing JSON object, so I went to my tried and true old-pal PL/V8 for some comfort. Here is a quickie function I created in PL/V8 that did what I needed. Hopefully it will be of use to others or others might have other ideas of doing this that I missed.

ujson_add_prop function: Add/Update properties of a JSON object

I used PL/V8 1.4.3, binaries for windows users here. I settled on taking json and outputting json from this function instead of jsonb, because when I tried to swap out json with jsonb, I got back the same json object as what I put in. Not sure why.

The function also doubles as a setter. If I had for example a key already defined called "Header" in root of my JSON object, it would replace the old value with the new value.

On a side-note, I also discovered that my other tried and true friend hstore is a great companion to JSON allowing me to easily remorph JSON arrays such as survey questions into key-value for easier querying. More on that later.