Add new JSON processing functions and parser API.
The JSON parser is converted into a recursive descent parser, and
exposed for use by other modules such as extensions. The API provides
hooks for all the significant parser event such as the beginning and end
of objects and arrays, and providing functions to handle these hooks
allows for fairly simple construction of a wide variety of JSON
processing functions. A set of new basic processing functions and
operators is also added, which use this API, including operations to
extract array elements, object fields, get the length of arrays and the
set of keys of a field, deconstruct an object into a set of key/value
pairs, and create records from JSON objects and arrays of objects.
Catalog version bumped.
Andrew Dunstan, with some documentation assistance from Merlin Moncure.

This is huge change, and one that makes me very happy. So far we were able to create JSONs, and store them. But reading of data from JSON was not really possible.

Now – thanks to Andrew's work – we got huge number of additional features that can be used to actually use the data from within the jsons.

First, let's talk about new operators. There are 2 new operators, each with 2 variants.

First operator is -> – this is used to extract data from json value. Returned value is also json, so you can “chain" it:

$ select'{"a":[1,2,3]}'::json->'a'->2;
?column?
----------3(1row)

This operator can take either integer or text on the right side. If it's integer – it assumes the left side of operator is json array, and returns element that has given index (0 based).

If the right side is text, it is used to get data from JSON objects (hashes).

First argument to the function – NULL::test, is any value typed to whatever you want to get from the function. Since each table is also a type, and simplest value is null, you generally want NULL::table_name.

If value for any field is not provided in source data – it will be returned as NULL.

Difference between json_populate_record() and json_populate_recordset() is that json_populate_record expects to get single object, and returns single row, while the json_populate_recordset works on array of objects and returns (potentially) multiple rows.

I, for am, am grateful to Andrew for his work, as now JSON seems to be really useful for DB programming. So far it was more or less only for sending data back to application, but now we can use JSON to do many cool things ourselves.

The only thing missing is modifying existing JSONs. Like – adding key to object, or adding value to array, or changing existing value within array/object to something else. It can be done now, but in a not-so-pleasant way.

11 comments

One thing about the new API is that it makes writing functions like json_set() a heck of a lot easier. I could probably undertake to write something along these line in a fairly short time, although it does have some wrinkles (e.g. what do you do if the arguments say to set element 5 of a non-existent array? Set items 0 .. 4 to null?)

This will be really a huge change, thanks for implementing it and writing about! One question popped into my mind: how about indexing arbitrary JSONs? Is there anything like that? (Would be a sort of NoSQL in a yeSQL column

My thought was that just as hstore can be indexed, probably a flat JSON could be indexed, too. I see for achieving this, one should be able to determine if a JSON is flat, though. I have no idea for non-flat sructures…

yeah, I figured. Given current limitations for both datatypes, I would say that they should be used in different scenarios:

if you want indexable searches, and you’re fine with “no-nesting” limitation: use hstore.
if you need nested, complicated structures, and don’t care about indexing – use json.
and if you need the best from both world – you’ll have to wait to see what, and when, the future will bring.