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.

1 files changed, 158 insertions, 0 deletions

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
        <entry><literal>json_typeof('-123.4')</literal></entry>
        <entry><literal>number</literal></entry>
       </row>
+      <row>
+       <entry>
+         <indexterm>
+          <primary>json_build_array</primary>
+         </indexterm>
+         <literal>json_build_array(VARIADIC "any")</literal>
+       </entry>
+       <entry><type>json</type></entry>
+       <entry>
+         Builds a heterogeneously typed json array out of a variadic argument list.
+       </entry>
+       <entry><literal>SELECT json_build_array(1,2,'3',4,5);</literal></entry>
+       <entry>
+<programlisting>
+ json_build_array
+-------------------
+ [1, 2, "3", 4, 5]
+ </programlisting>
+       </entry>
+      </row>
+      <row>
+       <entry>
+         <indexterm>
+          <primary>json_build_object</primary>
+         </indexterm>
+         <literal>json_build_object(VARIADIC "any")</literal>
+       </entry>
+       <entry><type>json</type></entry>
+       <entry>
+         Builds a JSON array out of a variadic agument list.  By convention, the object is 
+         constructed out of alternating name/value arguments.
+       </entry>
+       <entry><literal>SELECT json_build_object('foo',1,'bar',2);</literal></entry>
+       <entry>
+<programlisting>
+   json_build_object
+------------------------
+ {"foo" : 1, "bar" : 2}
+ </programlisting>
+       </entry>
+      </row>
+      <row>
+       <entry>
+         <indexterm>
+          <primary>json_object</primary>
+         </indexterm>
+         <literal>json_object(text[])</literal>
+       </entry>
+       <entry><type>json</type></entry>
+       <entry>
+         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.
+       </entry>
+       <entry><literal>select * from json_object('{a, 1, b, "def", c, 3.5}')  or <literal>select * from json_object('{{a, 1},{b, "def"},{c, 3.5}}')</literal></literal></entry>
+       <entry>
+<programlisting>
+              json_object
+---------------------------------------
+ {"a" : "1", "b" : "def", "c" : "3.5"}
+ </programlisting>
+       </entry>
+      </row>
+      <row>
+       <entry>
+         <literal>json_object(keys text[], values text[])</literal>
+       </entry>
+       <entry><type>json</type></entry>
+       <entry>
+         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.
+       </entry>
+       <entry><literal>select * from json_object('{a, b}', '{1,2}');</literal></entry>
+       <entry>
+<programlisting>
+      json_object
+------------------------
+ {"a" : "1", "b" : "2"}
+ </programlisting>
+       </entry>
+      </row>
+      <row>
+       <entry>
+         <indexterm>
+          <primary>json_to_record</primary>
+         </indexterm>
+         <literal>json_to_record(json, nested_as_text bool)</literal>
+       </entry>
+       <entry><type>record</type></entry>
+       <entry>
+         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.
+       </entry>
+       <entry><literal>select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}',true) as x(a int, b text, d text) </literal></entry>
+       <entry>
+<programlisting>
+ a |    b    | d 
+---+---------+---
+ 1 | [1,2,3] | 
+ </programlisting>
+       </entry>
+      </row>
+      <row>
+       <entry>
+         <indexterm>
+          <primary>json_to_recordset</primary>
+         </indexterm>
+         <literal>json_to_recordset(json, nested_as_text bool)</literal>
+       </entry>
+       <entry><type>setof record</type></entry>
+       <entry>
+         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.
+       </entry>
+       <entry><literal>select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]',true) as x(a int, b text);</literal></entry>
+       <entry>
+<programlisting>
+ a |  b
+---+-----
+ 1 | foo
+ 2 |
+ </programlisting>
+       </entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
@@ -10326,6 +10457,17 @@ table2-mapping
 
   <note>
     <para>
+       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.
+    </para>
+  </note>
+
+  <note>
+    <para>
       The <xref linkend="hstore"> extension has a cast from <type>hstore</type> to
       <type>json</type>, so that converted <type>hstore</type> values are represented as JSON objects,
       not as string values.
@@ -11775,6 +11917,22 @@ NULL baz</literallayout>(3 rows)</entry>
      <row>
       <entry>
        <indexterm>
+        <primary>json_object_agg</primary>
+       </indexterm>
+       <function>json_object_agg(<replaceable class="parameter">expression</replaceable>)</function>
+      </entry>
+      <entry>
+       <type>("any", "any")</type>
+      </entry>
+      <entry>
+       <type>json</type>
+      </entry>
+      <entry>aggregates name/value pairs as a JSON object</entry>
+     </row>
+
+     <row>
+      <entry>
+       <indexterm>
         <primary>max</primary>
        </indexterm>
        <function>max(<replaceable class="parameter">expression</replaceable>)</function>