From 105639900bf83fd3e3eb5b49f49b4d74d6347b9b Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Tue, 28 Jan 2014 17:48:21 -0500 Subject: 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. --- doc/src/sgml/func.sgml | 158 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 158 insertions(+) (limited to 'doc/src') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index c0a75de0e73..10db2f00613 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10300,6 +10300,137 @@ table2-mapping json_typeof('-123.4') number + + + + json_build_array + + json_build_array(VARIADIC "any") + + json + + Builds a heterogeneously typed json array out of a variadic argument list. + + SELECT json_build_array(1,2,'3',4,5); + + + json_build_array +------------------- + [1, 2, "3", 4, 5] + + + + + + + json_build_object + + json_build_object(VARIADIC "any") + + json + + Builds a JSON array out of a variadic agument list. By convention, the object is + constructed out of alternating name/value arguments. + + SELECT json_build_object('foo',1,'bar',2); + + + json_build_object +------------------------ + {"foo" : 1, "bar" : 2} + + + + + + + json_object + + json_object(text[]) + + json + + Builds a JSON object out of a text array. The array must have either + exactly one dimension with an even number of members, in which case + they are taken as alternating name/value pairs, or two dimensions + such that each inner array has exactly two elements, which + are taken as a name/value pair. + + select * from json_object('{a, 1, b, "def", c, 3.5}') or select * from json_object('{{a, 1},{b, "def"},{c, 3.5}}') + + + json_object +--------------------------------------- + {"a" : "1", "b" : "def", "c" : "3.5"} + + + + + + json_object(keys text[], values text[]) + + json + + The two argument form of JSON object takes keys and values pairwise from two separate + arrays. In all other respects it is identical to the one argument form. + + select * from json_object('{a, b}', '{1,2}'); + + + json_object +------------------------ + {"a" : "1", "b" : "2"} + + + + + + + json_to_record + + json_to_record(json, nested_as_text bool) + + record + + json_to_record returns an arbitrary record from a JSON object. As with all functions + returning 'record', the caller must explicitly define the structure of the record + when making the call. The input JSON must be an object, not a scalar or an array. + If nested_as_text is true, the function coerces nested complex elements to text. + Also, see notes below on columns and types. + + select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}',true) as x(a int, b text, d text) + + + a | b | d +---+---------+--- + 1 | [1,2,3] | + + + + + + + json_to_recordset + + json_to_recordset(json, nested_as_text bool) + + setof record + + json_to_recordset returns an arbitrary set of records from a JSON object. As with + json_to_record, the structure of the record must be explicitly defined when making the + call. However, with json_to_recordset the input JSON must be an array containing + objects. nested_as_text works as with json_to_record. + + select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]',true) as x(a int, b text); + + + a | b +---+----- + 1 | foo + 2 | + + + @@ -10324,6 +10455,17 @@ table2-mapping + + + In json_to_record and json_to_recordset, type coercion from the JSON is + "best effort" and may not result in desired values for some types. JSON + elements are matched to identical field names in the record definition, + and elements which do not exist in the JSON will simply be NULL. JSON + elements which are not defined in the record template will + be omitted from the output. + + + The extension has a cast from hstore to @@ -11772,6 +11914,22 @@ NULL baz(3 rows) aggregates records as a JSON array of objects + + + + json_object_agg + + json_object_agg(expression) + + + ("any", "any") + + + json + + aggregates name/value pairs as a JSON object + + -- cgit v1.2.3