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
  • 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
$$ 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;

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.


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
If you ignore this warning and it
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).


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.