Monday, May 6, 2013

Utilities for building JSON

As we get more experience in using JSON in Postgres we get a better idea of what we need to make its use simple and effective. This work is the result of some such experience.

Today I'm releasing a new extension, json_build, which provides a couple of utility functions for building JSON, build_json_array() and build_json_object(). Both of these functions can take any number of arguments of any type, and they will build a JSON value of the appropriate kind. Note that JSON arrays, unlike Postgres arrays, can be heterogenous, and the values do not all need to be of the same type. When building an object, the arguments are treated pairwise, so it's an error if build_json_object() gets an odd number of arguments. The first item in each pair must be a non-null scalar, (i.e. not an array or record) and must not be a JSON value.

Any JSON value is simply passed through, either as an object field value or as an array element, so, since these functions return JSON, you can nest calls to them and thus build up your JSON values in a fairly natural way.

Examples:

SELECT build_json_object( 
       'a', build_json_object('b',false,'c',99), 
       'd', build_json_object('e',array[9,8,7]::int[],
           'f', (select row_to_json(r) from ( select relkind, oid::regclass as name 
                                              from pg_class where relname = 'pg_class') r)));
                                    build_json_object                                        
-------------------------------------------------------------------------------------------------
 {"a" : {"b" : false, "c" : 99}, "d" : {"e" : [9,8,7], "f" : {"relkind":"r","name":"pg_class"}}}
(1 row)

 SELECT build_json_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}');
                           build_json_array                            
-----------------------------------------------------------------------
 ["a", 1, "b", 1.2, "c", true, "d", null, "e", {"x": 3, "y": [1,2,3]}]
(1 row)

Enjoy!

No comments:

Post a Comment