Json Enhancements for PostgreSQL 9.2

This is a backport of two pieces of work I did making JSON more user friendly and useful for PostgreSL 9.3. The JSON type was introduced in PostgreSQL 9.2, but is somewhat lacking in useful features - there are no functions to pull data out of JSON or otherwise process it, and while there are some generator functions they have some limitations and can be a bit inefficient to use. This work is an attempt to remedy those defects.

These enhancements do a these things:

  • to_json(any) turns any data value into valid json
  • json_agg(anyrecord) aggregates record values and returns a single piece of json. This can be used instead of array_to_json(array_agg(record)) which is both ugly and very inefficient.
  • to_json(hstore) and a cast that uses it that turns an hstore into json
  • to_json_loose(hstore) also turns an hstore into json but heuristically tries to detect numeric and boolean values and avoid quoting them
  • use by to_json() and json_agg() of a cast to json function, if one exists, when rendering a datum of a non-builtin type to json. This means, for example, that a record containing an hstore will have the hstore rendered sanely as a json object rather than as an opaque piece of text.
  • certain functions and operators below have text variants. They return the results as text rather than as json, and if the value returned is a json string value, it is dequoted, so you get back foo"bar instead of "foo\"bar". The text variants also turn join nulls into SQL nulls.
  • note that json arrays are always numbered from 0, unlike the SQL default.
  • operators -> and ->> to extract an object key if the right hand operand is a string, or array element if the right hand operand is an integer. ->> is the text variant as above.
  • operators #> and #>> take an array of text as the right hand operand, and return the corresponding item. #>> is the text variant as above.
  • json_extract_path(json, variadic text[]) and json_extract_path_text(json, variadic text[]) return the element at the denoted path. Elements in the path can be field names if the element at this nesting level is a json object or textified integers if the item is an array. json_extract_path_text() is the text variant as above.
  • json_object_keys(json) returns the set of keys in the json object
  • json_array_length(json) returns the length of the json array
  • json_array_elements(json) returns the elements in the json array.
  • json_each(json) and json_each_text(json) return the set of key/value pairs in a json object. json_each_text() returns the text variant values as above.
  • json_populate_record(anyrecord, json, use_json_as_text_bool) returns the input record with fields with identical names to the fields in a the json object set to the corresponding values. The third boolean parameter defaults to false, which means it will raise an error if it finds that a name maps to a json object or array instead of a scalar value. If it is true it will instead try to use the text of the nested value to populate the record field.
  • json_populate_recordset(anyrecord, json, use_json_as_text_bool) does the same as json_populate_record() but for each object element of the input array, i.e. it turns json which is an array of objects into a set of records.

Examples of all of these should be found in the test files.

You can also use these building blocks to build further useful functions, such as things like this:

$ create or replace function json_pluck(j json, field text)
returns text[]
language sql
as
$$ select array_agg(j->>$2) from json_array_elements($1) j $$;

$ create operator | (procedure = json_pluck, leftarg = 'json', rightarg = 'text');

and then you can do:

$ select json '[{"name" : "moe", "age" : 40}, {"name" : "larry", "age" : 50}, {"name" : "curly", "age" : 60}]' 
| 'name' as names;
       names       
-------------------
 {moe,larry,curly}

What's missing?

The proposed changes for 9.3 alter the behaviour of the array_to_json() and row_to_json() functions so that they honor casts from non-builtin types to json, as described above. This change could not be included in this extension. Just use to_json() instead.

Caveats

This extension requires the hstore extension to be loaded. What is more, either it needs to be built against the installed hstore library or the hstore library needs to be preloaded in your installation. See the Makefile for details.

If you can't get it to build this way, try building with NOHSTORE defined. If you do that you'll miss out on the hstore extras above, of course.

This module might make using pg_upgrade difficult or even impossible. This use has not been tested. IF YOU ARE PLANNING TO USE pg_upgrade THEN DO NOT USE THIS EXTENSION WITHOUT UPGRADE TESTING. If you ignore this warning and it breaks you get to keep all the pieces.

Further extension

It should be possible to build further json processing modules that leverage this library. The API is in the file jsonapi.h, and the extension should link against this library in the same way as we link against hstore (see above).

Credits

The original work from which this was drawn was supported by Heroku. The porting was supported by IVC. The original Json parser in PostgreSQL release 9.2 which is adapted here was written by Robert Haas.