On 28th of January, Andrew Dunstan committed patch:

New json functions.
 
json_build_array() and json_build_object allow for the construction of
arbitrarily complex json trees. json_object() turns a one or two
dimensional array, or two separate arrays, into a json_object of
name/value pairs, similarly to the hstore() function.
json_object_agg() aggregates its two arguments into a single json object
as name value pairs.
 
Catalog version bumped.
 
Andrew Dunstan, reviewed by Marko Tiikkaja.

Andrew is the man when it comes to JSON in PostgreSQL. So it's not a surprise that newest addition to JSON support is also made by him.

This time we got these new functions:

• json_build_array
• json_build_object
• json_object
• json_to_record
• json_to_recordset

So, what can these be used for?

json_build_array

This can be used to build an json array based on values with different datatypes. So you can create array where some elements are texts, and other are numbers, and the array will be properly quoted:

$ SELECT json_build_array( 'depesz', 123, 3.14, 'postgresql' );
          json_build_array           
-------------------------------------
 ["depesz", 123, 3.14, "postgresql"]
(1 ROW)

json_build_object

Returns object (hash?) that is based on arguments, assuming that odd arguments are keys and even arguments are values, like here:

$ SELECT json_build_object( 'pi', 3.14, 'database', 'postgresql' );
            json_build_object             
------------------------------------------
 {"pi" : 3.14, "database" : "postgresql"}
(1 ROW)

json_object

There are two functions, with the same name, but different arguments.

First version takes text array as it's only argument, and works just like json_build_object:

$ SELECT json_object( '{pi,3.14,database,postgresql}'::text[] );
                json_object                 
--------------------------------------------
 {"pi" : "3.14", "database" : "postgresql"}
(1 ROW)

The second version takes two arrays – one with keys, the other with values:

$ SELECT json_object( '{e,pi,tau}'::text[], '{2.718,3.141,6.283}'::text[] );
                   json_object                    
--------------------------------------------------
 {"e" : "2.718", "pi" : "3.141", "tau" : "6.283"}
(1 ROW)

json_to_record

Converts given JSON value to record with customizable (on call) datatypes:

$ SELECT * FROM json_to_record( '{"pi" : "3.14", "database" : "postgresql"}', TRUE ) AS j(pi NUMERIC, DATABASE text);
  pi  |  DATABASE  
------+------------
 3.14 | postgresql
(1 ROW)

What is the “true" as second argument?

From what I was able to deduce by my test, it only means that if you have nested structures in the array (i.e. value for given key in object is not a scalar, but is another object or array) and if the 2nd argument to json_to_record is “false" – it will fail with:

ERROR:  cannot CALL json_populate_record ON a nested object

In this way providing a safeguard, of sorts, against “unpacking" more complicated structures.

json_to_recordset

Just like json_to_record produces a record out of json object, json_to_recordset produces many records out of json array of objects:

$ SELECT * FROM json_to_recordset(
    '[{"name":"e","value":2.718},{"name":"pi","value":3.141},{"name":"tau","value":6.283}]',
    TRUE
    ) AS x (name text, VALUE NUMERIC);
 name | VALUE 
------+-------
 e    | 2.718
 pi   | 3.141
 tau  | 6.283
(3 ROWS)

All things said – it looks generally cool. With each and every change, the JSON support in PostgreSQL looks better. Thanks a lot, Andrew.