SQL/JSON: documentation
Started by Nikita Glukhovover 7 years ago11 messages
Attached patch with draft of SQL/JSON documentation written by
Liudmila Mantrova, Oleg Bartunov and me.
Also it can be found in our sqljson repository on sqljson_doc branch:
https://github.com/postgrespro/sqljson/tree/sqljson_doc
We continue to work on it.
--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:
sqljson-docs-v01.patchtext/x-patch; name=sqljson-docs-v01.patchDownload
diff --git a/doc/src/sgml/biblio.sgml b/doc/src/sgml/biblio.sgml
index 4953024..a7771dc 100644
--- a/doc/src/sgml/biblio.sgml
+++ b/doc/src/sgml/biblio.sgml
@@ -136,6 +136,17 @@
<pubdate>1988</pubdate>
</biblioentry>
+ <biblioentry id="sqltr17">
+ <title>SQL Technical Report</title>
+ <subtitle>Part 6: SQL support for JavaScript Object
+ Notation (JSON)</subtitle>
+ <edition>First Edition.</edition>
+ <biblioid>
+ <ulink url="http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip"></ulink>.
+ </biblioid>
+ <pubdate>2017.</pubdate>
+ </biblioentry>
+
</bibliodiv>
<bibliodiv>
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 3d36cca..f30baa7 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -149,6 +149,12 @@
</row>
<row>
+ <entry><type>jsonpath</type></entry>
+ <entry></entry>
+ <entry>binary JSON path</entry>
+ </row>
+
+ <row>
<entry><type>line</type></entry>
<entry></entry>
<entry>infinite line on a plane</entry>
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index f010cd4..a60de6c 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -18,6 +18,7 @@
<!ENTITY ddl SYSTEM "ddl.sgml">
<!ENTITY dml SYSTEM "dml.sgml">
<!ENTITY func SYSTEM "func.sgml">
+<!ENTITY func-sqljson SYSTEM "func-sqljson.sgml">
<!ENTITY indices SYSTEM "indices.sgml">
<!ENTITY json SYSTEM "json.sgml">
<!ENTITY mvcc SYSTEM "mvcc.sgml">
diff --git a/doc/src/sgml/func-sqljson.sgml b/doc/src/sgml/func-sqljson.sgml
new file mode 100644
index 0000000..1f2881d
--- /dev/null
+++ b/doc/src/sgml/func-sqljson.sgml
@@ -0,0 +1,3562 @@
+<!-- doc/src/sgml/func-sqljson.sgml -->
+
+<sect1 id="functions-json">
+ <title>JSON Functions, Operators and Expressions</title>
+
+ <para>
+ The functions, operators, and expressions described in this section
+ operate on JSON data:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ SQL/JSON functions and expressions conforming to the
+ SQL/JSON standard (see <xref linkend="functions-sqljson"/>).
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ PostgreSQL-specific functions and operators for JSON
+ data types (see <xref linkend="functions-pgjson"/>).
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <para>
+ To learn more about the SQL/JSON standard, see <xref linkend="sqltr17"/>.
+ For details on JSON types supported in <productname>PostgreSQL</productname>, see <xref linkend="datatype-json"/>.
+ </para>
+
+ <sect2 id="functions-sqljson">
+ <title>SQL/JSON Functions and Expressions</title>
+ <indexterm zone="functions-pgjson">
+ <primary>SQL/JSON</primary>
+ <secondary>functions and expressions</secondary>
+ </indexterm>
+
+ <para>
+ To provide native support for JSON data types within the SQL environment,
+ <productname>PostgreSQL</productname> implements the <firstterm>SQL/JSON data model</firstterm>.
+ This model comprises sequences of items. Each item can hold SQL scalar values,
+ with an additional SQL/JSON null value, and composite data structures that use JSON
+ arrays and objects.
+ </para>
+
+ <para>
+ SQL/JSON enables you to handle JSON data alongside regular SQL data,
+ with transaction support:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ Upload JSON data into a relational database and store it in
+ regular SQL columns as character or binary strings.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Generate JSON objects and arrays from relational data.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Query JSON data using SQL/JSON query functions and SQL/JSON path
+ language expressions.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <para>
+ All SQL/JSON functions fall into one of the two groups.
+ <link linkend="functions-sqljson-producing">Constructor functions</link>
+ generate JSON data from values of SQL types. <link linkend="functions-sqljson-querying">Query functions</link>
+ evaluate SQL/JSON path language expressions against JSON values
+ and produce values of SQL/JSON types, which are converted to SQL types.
+ </para>
+
+ <sect3 id="functions-sqljson-producing">
+ <title>Producing JSON Content</title>
+
+ <para>
+ <productname>PostgreSQL</productname> provides several functions
+ that generate JSON data. Taking values of SQL types as input, these
+ functions construct JSON objects or JSON arrays represented as
+ SQL character or binary strings.
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ <xref linkend="functions-jsonobject"/>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <xref linkend="functions-jsonobjectagg"/>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <xref linkend="functions-jsonarray"/>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <xref linkend="functions-jsonarrayagg"/>
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <refentry id="functions-jsonobject">
+ <refnamediv>
+ <refname>JSON_OBJECT</refname>
+ <refpurpose>create a JSON object</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+JSON_OBJECT (
+[ { <replaceable class="parameter">key_expression</replaceable> { VALUE | ':' }
+ <replaceable class="parameter">value_expression</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ] }[, ...] ]
+[ { NULL | ABSENT } ON NULL ]
+[ { WITH | WITHOUT } UNIQUE [ KEYS ] ]
+[ RETURNING <replaceable class="parameter">data_type</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]
+)
+
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>JSON_OBJECT</function> function generates a <acronym>JSON</acronym>
+ object from <acronym>SQL</acronym> or <acronym>JSON</acronym> data.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+ <variablelist>
+
+ <varlistentry>
+ <term>
+ <literal><replaceable class="parameter">key_expression</replaceable> { VALUE | ':' }
+ <replaceable class="parameter">value_expression</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]</literal>
+ </term>
+ <listitem>
+ <para>
+ The input clause that provides the data for constructing a <acronym>JSON</acronym> object:
+ </para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ <replaceable class="parameter">key_expression</replaceable> is a scalar expression defining the
+ <acronym>JSON</acronym> key, which is implicitly converted
+ to the <type>text</type> type.
+ The provided expression cannot be <literal>NULL</literal> or belong to a type that has a cast to <type>json</type>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <replaceable class="parameter">value_expression</replaceable> is an expression
+ that provides the input for the <acronym>JSON</acronym> value.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The optional <literal>FORMAT</literal> clause is provided to conform to the SQL/JSON standard.
+ </para>
+ </listitem>
+ </itemizedlist>
+ <para>
+ You must use a colon or the <literal>VALUE</literal> keyword as a delimiter between
+ the key and the value. Multiple key/value pairs are separated by commas.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ NULL | ABSENT } ON NULL</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines whether <literal>NULL</literal> values are allowed in the constructed
+ <acronym>JSON</acronym> object:
+ </para>
+ <variablelist>
+ <varlistentry>
+ <term><literal>NULL</literal></term>
+ <listitem>
+ <para>
+ Default. <literal>NULL</literal> values are allowed.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>ABSENT</literal></term>
+ <listitem>
+ <para>
+ If the value is <literal>NULL</literal>,
+ the corresponding key/value pair is omitted from the generated
+ <acronym>JSON</acronym> object.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ WITH | WITHOUT } UNIQUE [ KEYS ]</literal>
+ </term>
+ <listitem>
+ <para>Defines whether duplicate keys are allowed:
+ </para>
+ <variablelist>
+ <varlistentry>
+ <term><literal>WITHOUT</literal></term>
+ <listitem>
+ <para>
+ Default. The constructed
+ <acronym>JSON</acronym> object can contain duplicate keys.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>WITH</literal></term>
+ <listitem>
+ <para>
+ Duplicate keys are not allowed.
+ If the input data contains duplicate keys, an error is returned.
+ This check is performed before removing JSON items with NULL values.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ <para>
+ Optionally, you can add the <literal>KEYS</literal> keyword for semantic clarity.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>RETURNING <replaceable class="parameter">data_type</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]</literal>
+ </term>
+ <listitem>
+ <para>
+ The output clause that specifies the type of the generated <acronym>JSON</acronym> object.
+ For details, see <xref linkend="sqljson-output-clause"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+ <para>Alternatively, you can construct <acronym>JSON</acronym> objects by using
+ <productname>PostgreSQL</productname>-specific <function>json_build_object()</function>/
+ <function>jsonb_build_object()</function> functions.
+ See <xref linkend="functions-json-creation-table"/> for details.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+ <para>
+ Construct a JSON object from the provided key/value pairs of various types:
+ </para>
+<screen>
+SELECT JSON_OBJECT(
+-- scalar JSON types
+ 'key1': 'string',
+ 'key2': '[1, 2]',
+ 'key3' VALUE 123, -- alternative syntax for key-value delimiter
+ 'key4': NULL,
+-- other types
+ 'key5': ARRAY[1, 2, 3], -- postgres array
+ 'key6': jsonb '{"a": ["b", 1]}', -- composite json/jsonb
+ 'key7': date '2017-09-30', -- datetime type
+ 'key8': row(1, 'a'), -- row type
+ 'key9': '[1, 2]' FORMAT JSON, -- same value as for key2, but with FORMAT
+-- key can be an expression
+ 'key' || 'last' : TRUE
+ABSENT ON NULL) AS json;
+ json
+----------------------------------------------------
+{"key1" : "string", "key2" : "[1, 2]", "key3" : 123,
+ "key5" : [1,2,3], "key6" : {"a": ["b", 1]},
+ "key7" : "2017-09-30", "key8" : {"f1":1,"f2":"a"},
+ "key9" : [1, 2], "keylast" : true}
+(1 row)
+</screen>
+
+ <para>
+ From the <structname>films</structname> table, select some data
+ about the films distributed by Paramount Pictures
+ (<literal>did</literal> = 103) and return JSON objects:
+ </para>
+<screen>
+SELECT
+JSON_OBJECT(
+ 'code' VALUE f.code,
+ 'title' VALUE f.title,
+ 'did' VALUE f.did
+) AS paramount
+FROM films AS f
+WHERE f.did = 103;
+ paramount
+----------------------------------------------------
+{"code" : "P_301", "title" : "Vertigo", "did" : 103}
+{"code" : "P_302", "title" : "Becket", "did" : 103}
+{"code" : "P_303", "title" : "48 Hrs", "did" : 103}
+(3 rows)
+</screen>
+ </refsect1>
+ </refentry>
+
+ <refentry id="functions-jsonobjectagg">
+ <refnamediv>
+ <refname>JSON_OBJECTAGG</refname>
+ <refpurpose>create a JSON object as an aggregate of the provided data</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>JSON_OBJECTAGG (
+[ { <replaceable class="parameter">key_expression</replaceable> { VALUE | ':' } <replaceable class="parameter">value_expression</replaceable> } ]
+[ { NULL | ABSENT } ON NULL ]
+[ { WITH | WITHOUT } UNIQUE [ KEYS ] ]
+[ RETURNING <replaceable class="parameter">data_type</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]
+)
+
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>JSON_OBJECTAGG</function> function aggregates the provided data
+ into a <acronym>JSON</acronym> object. You can use this function to combine values
+ stored in different table columns into pairs. If you specify a <command>GROUP BY</command>
+ or an <command>ORDER BY</command> clause, this function returns a separate JSON object
+ for each table row.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal><replaceable class="parameter">key_expression</replaceable> { VALUE | ':' } <replaceable class="parameter">value_expression</replaceable></literal>
+ </term>
+ <listitem>
+
+ <para>
+ The input clause that provides the data to be aggregated as a <acronym>JSON</acronym> object:
+ </para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ <replaceable class="parameter">key_expression</replaceable> is a scalar expression defining the
+ <acronym>JSON</acronym> key, which is implicitly converted
+ to the <type>text</type> type.
+ The provided expression cannot be <literal>NULL</literal> or belong to a type that has a cast to <type>json</type>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <replaceable class="parameter">value_expression</replaceable> is an expression
+ that provides the input for the <acronym>JSON</acronym> value preceded by its type.
+ For <acronym>JSON</acronym> scalar types, you can omit the type.
+ </para>
+ <note>
+ <para>
+ The input value of the <literal>bytea</literal> type must be stored in <literal>UTF8</literal>
+ and contain a valid <literal>UTF8</literal> string. Otherwise, an error occurs.
+ <productname>PostgreSQL</productname> currently supports only <literal>UTF8</literal>.
+ </para>
+ </note>
+ </listitem>
+ </itemizedlist>
+ <para>
+ You must use a colon or the <literal>VALUE</literal> keyword as a delimiter between
+ keys and values. Multiple key/value pairs are separated by commas.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ NULL | ABSENT } ON NULL</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines whether <literal>NULL</literal> values are allowed in the constructed
+ <acronym>JSON</acronym> object:
+ </para>
+ <variablelist>
+ <varlistentry>
+ <term><literal>NULL</literal></term>
+ <listitem>
+ <para>
+ Default. <literal>NULL</literal> values are allowed.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>ABSENT</literal></term>
+ <listitem>
+ <para>
+ If the value is <literal>NULL</literal>,
+ the corresponding key/value pair is omitted from the generated
+ <acronym>JSON</acronym> object.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ WITH | WITHOUT } UNIQUE [ KEYS ]</literal>
+ </term>
+ <listitem>
+ <para>Defines whether duplicate keys are allowed:
+ </para>
+ <variablelist>
+ <varlistentry>
+ <term><literal>WITHOUT</literal></term>
+ <listitem>
+ <para>
+ Default. The constructed
+ <acronym>JSON</acronym> object can contain duplicate keys.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>WITH</literal></term>
+ <listitem>
+ <para>
+ Duplicate keys are not allowed.
+ If the input data contains duplicate keys, an error is returned.
+ This check is performed before removing JSON items with NULL values.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ <para>
+ Optionally, you can add the <literal>KEYS</literal> keyword for semantic clarity.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>RETURNING <replaceable class="parameter">data_type</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]</literal>
+ </term>
+ <listitem>
+ <para>
+ The output clause that specifies the type of the generated <acronym>JSON</acronym> object.
+ For details, see <xref linkend="sqljson-output-clause"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+ <para>Alternatively, you can create <acronym>JSON</acronym> objects by using
+ <productname>PostgreSQL</productname>-specific <function>json_object_agg()</function>/
+ <function>jsonb_object_agg()</function> aggregate functions.
+ See <xref linkend="functions-aggregate"/> for details.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ For films with <literal>did</literal> = 103, aggregate key/value pairs
+ of film genre (<literal>f.kind</literal>) and title (<literal>f.title</literal>)
+ into a single object:
+ </para>
+<screen>
+SELECT
+JSON_OBJECTAGG(
+ f.kind VALUE f.title)
+ AS films_list
+FROM films AS f
+where f.did = 103;
+ films_list
+----------------------------------------------------
+{ "Action" : "Vertigo", "Drama" : "Becket", "Action" : "48 Hrs" }
+</screen>
+
+ <para>
+ Return the same object as <type>jsonb</type>. Note that only a single film of
+ the action genre is included as the <type>jsonb</type> type does not allow duplicate keys.
+ </para>
+<screen>
+SELECT
+JSON_OBJECTAGG(
+ f.kind VALUE f.title
+ RETURNING jsonb)
+AS films_list
+FROM films AS f
+where f.did = 103;
+ films_list
+----------------------------------------------------
+{"Drama": "Becket", "Action": "48 Hrs"}
+</screen>
+
+ <para>
+ Return objects of film titles and length, grouped by the film genre:
+ </para>
+<screen>
+SELECT
+ f.kind,
+ JSON_OBJECTAGG(
+ f.title VALUE f.len
+) AS films_list
+FROM films AS f
+GROUP BY f.kind;
+
+ kind | films_list
+-------------+----------------------------------
+Musical | { "West Side Story" : "02:32:00", "The King and I" : "02:13:00", "Bed Knobs and Broomsticks" : "01:57:00" }
+Romantic | { "The African Queen" : "01:43:00", "Une Femme est une Femme" : "01:25:00", "Storia di una donna" : "01:30:00" }
+Comedy | { "Bananas" : "01:22:00", "There's a Girl in my Soup" : "01:36:00" }
+Drama | { "The Third Man" : "01:44:00", "Becket" : "02:28:00", "War and Peace" : "05:57:00", "Yojimbo" : "01:50:00", "Das Boot" : "02:29:00" }
+Action | { "Vertigo" : "02:08:00", "48 Hrs" : "01:37:00", "Taxi Driver" : "01:54:00", "Absence of Malice" : "01:55:00" }
+(5 rows)
+</screen>
+ </refsect1>
+ </refentry>
+
+ <refentry id="functions-jsonarray">
+ <refnamediv>
+ <refname>JSON_ARRAY</refname>
+ <refpurpose>create a JSON array</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>JSON_ARRAY (
+[ { <replaceable class="parameter">value_expression</replaceable> [ FORMAT JSON ] } [, ...] ]
+[ { NULL | ABSENT } ON NULL ]
+[ RETURNING <replaceable class="parameter">data_type</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]
+)
+</synopsis>
+<synopsis>JSON_ARRAY (
+[ <replaceable class="parameter">query_expression</replaceable> ]
+[ RETURNING <replaceable class="parameter">data_type</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]
+)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>JSON_ARRAY</function> function constructs a <acronym>JSON</acronym> array from
+ the provided <acronym>SQL</acronym> or <acronym>JSON</acronym> data.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal><replaceable class="parameter">value_expression</replaceable></literal>
+ </term>
+ <listitem>
+
+ <para>
+ The input clause that provides the data for constructing a JSON array.
+ The <replaceable class="parameter">value_expression</replaceable> is an expression
+ that provides the input for the <acronym>JSON</acronym> value preceded by its type.
+ For <acronym>JSON</acronym> scalar types, you can omit the type.
+ </para>
+ <note>
+ <para>
+ The input value of the <literal>bytea</literal> type must be stored in <literal>UTF8</literal>
+ and contain a valid <literal>UTF8</literal> string. Otherwise, an error occurs.
+ <productname>PostgreSQL</productname> currently supports only <literal>UTF8</literal>.
+ </para>
+ </note>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal><replaceable class="parameter">query_expression</replaceable></literal>
+ </term>
+ <listitem>
+ <para>
+ An SQL query that provides the data for constructing a JSON array.
+ The query must return a single column that holds the values to be
+ used in the array.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ NULL | ABSENT } ON NULL</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines whether <literal>NULL</literal> values are allowed in the generated <acronym>JSON</acronym> array:
+ </para>
+ <variablelist>
+ <varlistentry>
+ <term><literal>NULL</literal></term>
+ <listitem>
+ <para>
+ <literal>NULL</literal> values are allowed.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>ABSENT</literal></term>
+ <listitem>
+ <para>
+ Default. If the value is <literal>NULL</literal>,
+ the corresponding key/value pair is omitted from the generated
+ <acronym>JSON</acronym> object.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ <para>
+ This clause is only supported for arrays built from an explicit list of values.
+ If you are using an SQL query to generate an array, NULL values are always
+ omitted.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>RETURNING <replaceable class="parameter">data_type</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]</literal>
+ </term>
+ <listitem>
+ <para>
+ The output clause that specifies the return type of the constructed <acronym>JSON</acronym> array.
+ For details, see <xref linkend="sqljson-output-clause"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+</variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+ <para>Alternatively, you can create <acronym>JSON</acronym> arrays by using
+ <productname>PostgreSQL</productname>-specific <function>json_build_array()</function>/
+ <function>jsonb_build_array()</function> functions.
+ See <xref linkend="functions-json"/> for details.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+ <para>
+ From the <structname>films</structname> table, select some data
+ about the films distributed by Paramount Pictures
+ (<literal>did</literal> = 103) and return JSON arrays:
+ </para>
+<screen>
+SELECT
+JSON_ARRAY(
+ f.code,
+ f.title,
+ f.did
+) AS films
+FROM films AS f
+WHERE f.did = 103;
+ films
+----------------------------------------------------
+["code" : "P_301", "title" : "Vertigo", "did" : 103]
+["code" : "P_302", "title" : "Becket", "did" : 103]
+["code" : "P_303", "title" : "48 Hrs", "did" : 103]
+(3 rows)
+</screen>
+ <para>
+ Construct a JSON array from the list of film titles returned from the
+ <structname>films</structname> table by a subquery:
+ </para>
+<screen>
+SELECT
+JSON_ARRAY(
+ SELECT
+ f.title
+FROM films AS f
+where f.did = 103)
+AS film_titles;
+ film_titles
+----------------------------------------------------
+["Vertigo", "Becket", "48 Hrs"]
+(1 row)
+</screen>
+ </refsect1>
+ </refentry>
+
+ <refentry id="functions-jsonarrayagg">
+ <refnamediv>
+ <refname>JSON_ARRAYAGG</refname>
+ <refpurpose>aggregate a JSON array</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>JSON_ARRAYAGG (
+[ <replaceable class="parameter">value_expression</replaceable> ]
+[ ORDER BY <replaceable class="parameter">sort_expression</replaceable> ]
+[ { NULL | ABSENT } ON NULL ]
+[ RETURNING <replaceable class="parameter">data_type</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]
+)
+
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>JSON_ARRAYAGG</function> function aggregates the provided <acronym>SQL</acronym>
+ or <acronym>JSON</acronym> data into a <acronym>JSON</acronym> array.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal><replaceable class="parameter">value_expression</replaceable></literal>
+ </term>
+ <listitem>
+
+ <para>
+ The input clause that provides the input data to be aggregated as a <acronym>JSON</acronym> array.
+ The <replaceable class="parameter">value_expression</replaceable>
+ can be a value or a query returning the values to be used as input in array construction.
+ You can provide multiple input values separated by commas.
+ </para>
+ </listitem>
+ </varlistentry>
+
+<varlistentry>
+ <term>
+ <literal>ORDER BY</literal>
+ </term>
+ <listitem>
+ <para>
+ Sorts the input data to be aggregated as a <acronym>JSON</acronym> array.
+ For details on the exact syntax of the <literal>ORDER BY</literal> clause, see <xref linkend="sql-orderby"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ NULL | ABSENT } ON NULL</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines whether <literal>NULL</literal> values are allowed in the constructed array:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>NULL</literal> — <literal>NULL</literal> values are allowed.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>ABSENT</literal> (default) — <literal>NULL</literal>
+ values are omitted from the generated array.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>RETURNING <replaceable class="parameter">data_type</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]</literal>
+ </term>
+ <listitem>
+ <para>
+ The output clause that specifies the return type of the constructed <acronym>JSON</acronym> array.
+ For details, see <xref linkend="sqljson-output-clause"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+</variablelist>
+ </refsect1>
+
+<refsect1>
+ <title>Notes</title>
+ <para>Alternatively, you can create <acronym>JSON</acronym> arrays by using
+ <productname>PostgreSQL</productname>-specific <function>json_agg()</function>/
+ <function>jsonb_agg()</function> functions.
+ See <xref linkend="functions-aggregate"/> for details.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+ <para>
+ Construct an array of film titles sorted in alphabetical order:
+ </para>
+<screen>
+SELECT
+JSON_ARRAYAGG(
+ f.title
+ORDER BY f.title ASC) AS film_titles
+FROM films AS f;
+ film_titles
+----------------------------------------------------
+["48 Hrs", "Absence of Malice", "Bananas", "Becket", "Bed Knobs and Broomsticks", "Das Boot", "Storia di una donna", "Taxi Driver", "The African Queen", "The King and I", "There's a Girl in my Soup", "The Third Man", "Une Femme est une Femme", "Vertigo", "War and Peace", "West Side Story", "Yojimbo"]
+(1 row)
+</screen>
+ </refsect1>
+ </refentry>
+ </sect3>
+
+ <sect3 id="functions-sqljson-querying">
+ <title>Querying JSON</title>
+
+ <para>
+ SQL/JSON query functions evaluate SQL/JSON path language expressions
+ against JSON values, producing values of SQL/JSON types, which are
+ converted to SQL types. All SQL/JSON query functions accept several
+ common clauses described in <xref linkend="sqljson-common-clauses"/>.
+ For details on the SQL/JSON path language,
+ see <xref linkend="functions-sqljson-path"/>.
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ <xref linkend="functions-jsonexists"/>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <xref linkend="functions-jsonvalue"/>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <xref linkend="functions-jsonquery"/>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <xref linkend="functions-jsontable"/>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <xref linkend="functions-isjson-predicate"/>
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <para>
+ In some usage examples for these functions,
+ the following small table storing some JSON data will be used:
+<programlisting>
+CREATE TABLE my_films (
+ js text );
+
+INSERT INTO my_films VALUES (
+'{ "favorites" : [
+ { "kind" : "comedy", "films" : [
+ { "title" : "Bananas",
+ "director" : "Woody Allen"},
+ { "title" : "The Dinner Game",
+ "director" : "Francis Veber" } ] },
+ { "kind" : "horror", "films" : [
+ { "title" : "Psycho",
+ "director" : "Alfred Hitchcock" } ] },
+ { "kind" : "thriller", "films" : [
+ { "title" : "Vertigo",
+ "director" : "Alfred Hitchcock" } ] },
+ { "kind" : "drama", "films" : [
+ { "title" : "Yojimbo",
+ "director" : "Akira Kurosawa" } ] }
+ ] }');
+</programlisting>
+ </para>
+
+ <refentry id="functions-jsonexists">
+ <refnamediv>
+ <refname>JSON_EXISTS</refname>
+ <refpurpose>check whether a JSON path expression can return any SQL/JSON items</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>JSON_EXISTS (
+ <replaceable class="parameter">json_api_common_syntax</replaceable>
+[ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ]
+)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>JSON_EXISTS</function> function checks whether the provided
+ <acronym>JSON</acronym> path expression can return any <acronym>SQL/JSON</acronym> items.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal><replaceable class="parameter">json_api_common_syntax</replaceable></literal>
+ </term>
+ <listitem>
+
+ <para>
+ The input data to query, the JSON path expression defining the query, and an optional <literal>PASSING</literal> clause.
+ See <xref linkend="sqljson-input-clause"/> for details.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ TRUE | FALSE | UNKNOWN | ERROR } ON ERROR</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines the return value if an error occurs. The default value is <literal>FALSE</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Check whether the provided <type>jsonb</type> data contains a
+ key/value pair with the <literal>key1</literal> key, and its value
+ contains an array with one or more elements bigger than 2:
+ </para>
+<screen>
+SELECT JSON_EXISTS(jsonb '{"key1": [1,2,3]}', 'strict $.key1[*] ? (@ > 2)');
+ json_exists
+-------------
+ t
+(1 row)
+</screen>
+
+ <para>
+ Note the difference between strict and lax modes
+ if the required item does not exist:
+ </para>
+<screen>
+-- Strict mode with ERROR on ERROR clause
+SELECT JSON_EXISTS(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR);
+ERROR: Invalid SQL/JSON subscript
+(1 row)
+</screen>
+
+<screen>
+-- Lax mode
+SELECT JSON_EXISTS(jsonb '{"a": [1,2,3]}', 'lax $.a[5]' ERROR ON ERROR);
+ json_exists
+-------------
+ f
+(1 row)
+</screen>
+
+<screen>
+-- Strict mode using the default value for the ON ERROR clause
+SELECT JSON_EXISTS(jsonb '{"a": [1,2,3]}', 'strict $.a[5]');
+ json_exists
+-------------
+ f
+(1 row)
+</screen>
+
+ </refsect1>
+ </refentry>
+
+ <refentry id="functions-jsonvalue">
+ <refnamediv>
+ <refname>JSON_VALUE</refname>
+ <refpurpose>extract a value from JSON data and convert
+ it to an <acronym>SQL</acronym> scalar</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>JSON_VALUE (
+ <replaceable class="parameter">json_api_common_syntax</replaceable>
+[ RETURNING <replaceable class="parameter">data_type</replaceable> ]
+[ { ERROR | NULL | DEFAULT <replaceable class="parameter">expression</replaceable> } ON EMPTY ]
+[ { ERROR | NULL | DEFAULT <replaceable class="parameter">expression</replaceable> } ON ERROR ]
+)
+ </synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>JSON_VALUE</function> function extracts a value from the provided
+ <acronym>JSON</acronym> data and converts it to an <acronym>SQL</acronym> scalar.
+ If the specified JSON path expression returns more than one
+ <acronym>SQL/JSON</acronym> item, an error occurs. To extract
+ an <acronym>SQL/JSON</acronym> array or object, use <xref linkend="functions-jsonquery"/>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+
+ <varlistentry>
+ <term>
+ <literal><replaceable class="parameter">json_api_common_syntax</replaceable></literal>
+ </term>
+ <listitem>
+
+ <para>
+ The input data to query, the JSON path expression defining the query, and an optional <literal>PASSING</literal> clause.
+ For details, see <xref linkend="functions-sqljson-path"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>RETURNING <replaceable class="parameter">data_type</replaceable></literal>
+ </term>
+ <listitem>
+ <para>
+ The output clause that specifies the data type of the returned value.
+ Out of the box, <productname>PostgreSQL</productname>
+ supports the following types: <literal>json</literal>, <literal>jsonb</literal>,
+ <literal>bytea</literal>, and character string types (<literal>text</literal>, <literal>char</literal>,
+ <literal>varchar</literal>, and <literal>nchar</literal>).
+ The extracted value must be a single <acronym>SQL/JSON</acronym> scalar item
+ and have a cast to the specified type. Otherwise, an error occurs.
+ By default, <function>JSON_VALUE</function> returns a string
+ of the <literal>text</literal> type.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ ERROR | NULL | DEFAULT <replaceable>expression</replaceable> } ON EMPTY</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines the return value if no JSON value is found. The default is <literal>NULL</literal>.
+ If you use <literal>DEFAULT <replaceable>expression</replaceable></literal>, the provided
+ <replaceable>expression</replaceable> is evaluated and cast to the type specified in the
+ <command>RETURNING</command> clause.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ ERROR | NULL | DEFAULT <replaceable>expression</replaceable> } ON ERROR</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines the return value if an unhandled error occurs. The default is <literal>NULL</literal>.
+ If you use <literal>DEFAULT <replaceable>expression</replaceable></literal>, the provided
+ <replaceable>expression</replaceable> is evaluated and cast to the type specified in the
+ <command>RETURNING</command> clause.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Extract an SQL/JSON value and return it as an SQL
+ scalar of the specified type. Note that
+ <command>JSON_VALUE</command> can only return a
+ single scalar, and the returned value must have a
+ cast to the specified return type:
+ </para>
+
+<screen>
+SELECT JSON_VALUE('"123.45"', '$' RETURNING float);
+ json_value
+------------
+ 123.45
+(1 row)
+
+SELECT JSON_VALUE('123.45', '$' RETURNING int ERROR ON ERROR);
+ json_value
+------------
+ 123
+(1 row)
+
+SELECT JSON_VALUE('"03:04 2015-02-01"', '$.datetime("HH24:MI YYYY-MM-DD")' RETURNING date);
+ json_value
+------------
+ 2015-02-01
+(1 row)
+
+SELECT JSON_VALUE('"123.45"', '$' RETURNING int ERROR ON ERROR);
+ERROR: invalid input syntax for integer: "123.45"
+
+SELECT JSON_VALUE(jsonb '[1]', 'strict $' ERROR ON ERROR);
+ERROR: SQL/JSON scalar required
+
+SELECT JSON_VALUE(jsonb '[1,2]', 'strict $[*]' ERROR ON ERROR);
+ERROR: more than one SQL/JSON item
+</screen>
+
+ <para>
+ If the path expression returns an array, an object, or
+ multiple SQL/JSON items, an error is returned, as specified
+ in the <command>ON ERROR</command> clause:
+ </para>
+<screen>
+SELECT JSON_VALUE(jsonb '[1]', 'strict $' ERROR ON ERROR);
+ERROR: SQL/JSON scalar required
+
+SELECT JSON_VALUE(jsonb '{"a": 1}', 'strict $' ERROR ON ERROR);
+ERROR: SQL/JSON scalar required
+
+SELECT JSON_VALUE(jsonb '[1,2]', 'strict $[*]' ERROR ON ERROR);
+ERROR: more than one SQL/JSON item
+
+SELECT JSON_VALUE(jsonb '[1,2]', 'strict $[*]' DEFAULT 1 ON ERROR);
+1
+</screen>
+
+ </refsect1>
+ </refentry>
+
+ <refentry id="functions-jsonquery">
+ <refnamediv>
+ <refname>JSON_QUERY</refname>
+ <refpurpose>extract an SQL/JSON array or object from JSON data
+ and return a JSON string</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>JSON_QUERY (
+ <replaceable class="parameter">json_api_common_syntax</replaceable>
+[ RETURNING <replaceable class="parameter">data_type</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]
+[ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER ]
+[ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
+[ { ERROR | NULL | EMPTY { ARRAY | OBJECT } } ON EMPTY ]
+[ { ERROR | NULL | EMPTY { ARRAY | OBJECT } } ON ERROR ]
+)
+ </synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>JSON_QUERY</function> function extracts an <acronym>SQL/JSON</acronym>
+ array or object from <acronym>JSON</acronym> data. This function must return
+ a JSON string, so if the path expression returns a scalar or multiple SQL/JSON
+ items, you must wrap the result using the <literal>WITH WRAPPER</literal> clause
+ or enclose the path expression into square brackets for automatic wrapping.
+ To extract a single <acronym>SQL/JSON</acronym> value, you can use <xref linkend="functions-jsonvalue"/>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+
+ <varlistentry>
+ <term>
+ <literal><replaceable class="parameter">json_api_common_syntax</replaceable></literal>
+ </term>
+ <listitem>
+
+ <para>
+ The input data to query, the JSON path expression defining the query, and an optional <literal>PASSING</literal> clause.
+ For details, see <xref linkend="functions-sqljson-path"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>RETURNING <replaceable class="parameter">data_type</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]</literal>
+ </term>
+ <listitem>
+ <para>
+ The output clause that specifies the data type of the returned value.
+ For details, see <xref linkend="sqljson-output-clause"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } [ ARRAY ] WRAPPER</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines whether to wrap a returned result as an array.
+ </para>
+ <variablelist>
+ <varlistentry>
+ <term><literal>WITH CONDITIONAL WRAPPER</literal></term>
+ <listitem>
+ <para>
+ Wrap the results if the path
+ expression returns anything other than a singleton SQL/JSON array or object.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>WITH UNCONDITIONAL WRAPPER</literal></term>
+ <listitem>
+ <para>
+ Always wrap the result.
+ This is the default behavior if <literal>WITH WRAPPER</literal> is
+ specified.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>WITHOUT WRAPPER</literal></term>
+ <listitem>
+ <para>
+ Do not wrap the result.
+ This is the default behavior if the <literal>WRAPPER</literal>
+ clause is omitted.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ <para>
+ Optionally, you can add the <literal>ARRAY</literal> keyword for semantic clarity.
+ </para>
+ <important>
+ <para>You cannot use this clause together with the <literal>ON EMPTY</literal> clause.
+ </para>
+ </important>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ KEEP | OMIT } QUOTES [ ON SCALAR STRING ]</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines whether to keep or omit quotes if a scalar string is returned.
+ By default, scalar strings are returned with quotes. Using this
+ clause together with the <command>WITH WRAPPER</command> clause is not allowed.
+ </para>
+ <para>
+ Optionally, you can add the <literal>ON SCALAR STRING</literal> keywords for semantic clarity.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ ERROR | NULL | EMPTY { ARRAY | OBJECT } } ON EMPTY</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines the return value if no JSON value is found. The default is <literal>NULL</literal>.
+ If you use <literal>EMPTY ARRAY</literal> or <literal>EMPTY OBJECT</literal>,
+ an empty JSON array [] or object {} is returned, respectively.
+ You cannot use this clause together with the <literal>WRAPPER</literal> clause.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ ERROR | NULL | EMPTY { ARRAY | OBJECT } } ON ERROR</literal>
+ </term>
+ <listitem>
+ <para>
+ Defines the return value if an unhandled error occurs. The default is <literal>NULL</literal>.
+ If you use <literal>EMPTY ARRAY</literal> or <literal>EMPTY OBJECT</literal>,
+ an empty JSON array [] or object {} are returned, respectively.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Extract all film genres listed in the <structname>my_films</structname> table:
+ </para>
+ <screen>
+SELECT
+ JSON_QUERY(js, '$.favorites[*].kind' WITH WRAPPER ERROR ON ERROR)
+FROM my_films;
+ json_query
+------------
+ ["comedy", "horror", "thriller", "drama"]
+(1 row)
+</screen>
+
+ <para>
+ Note that the same query will result in an error if you omit the
+ <command>WITH WRAPPER</command> clause, as it returns multiple SQL/JSON items:
+ </para>
+ <screen>
+SELECT
+ JSON_QUERY(js, '$.favorites[*].kind' ERROR ON ERROR)
+FROM my_films;
+ERROR: more than one SQL/JSON item
+</screen>
+
+ <para>
+ Compare the effect of different <literal>WRAPPER</literal> clauses:
+ </para>
+ <screen>
+SELECT
+ js,
+ JSON_QUERY(js, 'lax $[*]') AS "without",
+ JSON_QUERY(js, 'lax $[*]' WITH WRAPPER) AS "with uncond",
+ JSON_QUERY(js, 'lax $[*]' WITH CONDITIONAL WRAPPER) AS "with cond"
+FROM
+ (VALUES (jsonb '[]'), ('[1]'), ('[[1,2,3]]'), ('[{"a": 1}]'), ('[1, null, "2"]')) foo(js);
+ js | without | with uncond | with cond
+----------------+-----------+----------------+----------------
+ [] | (null) | (null) | (null)
+ [1] | 1 | [1] | [1]
+ [[1, 2, 3]] | [1, 2, 3] | [[1, 2, 3]] | [1, 2, 3]
+ [{"a": 1}] | {"a": 1} | [{"a": 1}] | {"a": 1}
+ [1, null, "2"] | (null) | [1, null, "2"] | [1, null, "2"]
+(5 rows)
+</screen>
+
+<para>Compare quote handling for scalar types with and without the <command>OMIT QUOTES</command> clause:
+</para>
+ <screen>
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text);
+ json_query
+------------
+ "aaa"
+(1 row)
+
+SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text OMIT QUOTES);
+ json_query
+------------
+ aaa
+(1 row)
+</screen>
+ </refsect1>
+ </refentry>
+
+ <refentry id="functions-jsontable">
+ <refnamediv>
+ <refname>JSON_TABLE</refname>
+ <refpurpose>display JSON data as an SQL relation</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+JSON_TABLE (
+ <replaceable class="parameter">json_api_common_syntax</replaceable> [ AS <replaceable>path_name</replaceable> ]
+ COLUMNS ( <replaceable class="parameter">json_table_column</replaceable> [, ...] )
+ [ PLAN ( <replaceable class="parameter">json_table_plan</replaceable> ) |
+ PLAN DEFAULT ( { INNER | OUTER } [ , { CROSS | UNION } ]
+ | { CROSS | UNION } [ , { INNER | OUTER } ] )
+ ]
+)
+<phrase>
+where <replaceable class="parameter">json_table_column</replaceable> is:
+</phrase>
+ <replaceable>name</replaceable> <replaceable>type</replaceable> [ PATH <replaceable>json_path_specification</replaceable> ]
+ [ { ERROR | NULL | DEFAULT expression } ON EMPTY ]
+ [ { ERROR | NULL | DEFAULT expression } ON ERROR ]
+ | <replaceable>name</replaceable> <replaceable>type</replaceable> FORMAT <replaceable>json_representation</replaceable>
+ [ PATH <replaceable>json_path_specification</replaceable> ]
+ [ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } }
+ [ ARRAY ] WRAPPER ]
+ [ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
+ [ { ERROR | NULL | EMPTY { ARRAY | OBJECT } } ON EMPTY ]
+ [ { ERROR | NULL | EMPTY { ARRAY | OBJECT } } ON ERROR ]
+ | NESTED PATH <replaceable>json_path_specification</replaceable> [ AS <replaceable>path_name</replaceable> ]
+ COLUMNS ( <replaceable>json_table_column</replaceable> [, ...] )
+ | <replaceable>name</replaceable> FOR ORDINALITY
+<phrase>
+<replaceable>json_table_plan</replaceable> is:
+</phrase>
+ <replaceable>path_name</replaceable> [ { OUTER | INNER } <replaceable>json_table_plan_primary</replaceable> ]
+ | <replaceable>json_table_plan_primary</replaceable> { UNION <replaceable>json_table_plan_primary</replaceable> } [...]
+ | <replaceable>json_table_plan_primary</replaceable> { CROSS <replaceable>json_table_plan_primary</replaceable> } [...]
+<phrase>
+<replaceable>json_table_plan_primary</replaceable> is:
+</phrase>
+ <replaceable>path_name</replaceable> | ( <replaceable>json_table_plan</replaceable> )
+
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>JSON_TABLE</function> function queries <acronym>JSON</acronym> data
+ and presents the results as a relational view, which can be accessed as a
+ regular SQL table. You can only use <function>JSON_TABLE</function> inside the
+ <literal>FROM</literal> clause of the <literal>SELECT</literal> statement
+ for an SQL table.
+ </para>
+
+ <para>
+ Taking JSON data as input, <function>JSON_TABLE</function> uses
+ a path expression to extract a part of the provided data that
+ will be used as a <firstterm>row pattern</firstterm> for the
+ constructed view. Each SQL/JSON item at the top level of the row pattern serves
+ as the source for a separate row in the constructed relational view.
+ </para>
+
+ <para>
+ To split the row pattern into columns, <function>JSON_TABLE</function>
+ provides the <literal>COLUMNS</literal> clause that defines the
+ schema of the created view. For each column to be constructed,
+ this clause provides a separate path expression that evaluates
+ the row pattern, extracts a JSON item, and returns it as a
+ separate SQL value for the specified column. If the required value
+ is stored in a nested level of the row pattern, it can be extracted
+ using the <literal>NESTED PATH</literal> subclause. Joining the
+ columns returned by <literal>NESTED PATH</literal> can add multiple
+ new rows to the constructed view. Such rows are called
+ <firstterm>child rows</firstterm>, as opposed to the <firstterm>parent row</firstterm>
+ that generates them.
+ </para>
+
+ <para>
+ The rows produced by <function>JSON_TABLE</function> are laterally
+ joined to the row that generated them, so you do not have to explicitly join
+ the constructed view with the original table holding <acronym>JSON</acronym>
+ data. Optionally, you can specify how to join the columns returned
+ by <literal>NESTED PATH</literal> using the <literal>PLAN</literal> clause.
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal><replaceable class="parameter">json_api_common_syntax</replaceable></literal>
+ </term>
+ <listitem>
+
+ <para>
+ The input data to query, the JSON path expression defining the query,
+ and an optional <literal>PASSING</literal> clause, as described in
+ <xref linkend="sqljson-input-clause"/>. The result of the input data
+ evaluation is called the <firstterm>row pattern</firstterm>. The row
+ pattern is used as the source for row values in the constructed view.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>COLUMNS( { <replaceable class="parameter">json_table_column</replaceable> } [, ...] )</literal>
+ </term>
+ <listitem>
+
+ <para>
+ The <literal>COLUMNS</literal> clause defining the schema of the
+ constructed view. In this clause, you must specify all the columns
+ to be filled with SQL/JSON items. Only scalar column types are supported.
+ The <replaceable class="parameter">json_table_column</replaceable>
+ expression has the following syntax variants:
+ </para>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal><replaceable>name</replaceable> <replaceable>type</replaceable>
+ [ PATH <replaceable>json_path_specification</replaceable> ]</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Inserts a single SQL/JSON item into each row of
+ the specified column.
+ </para>
+ <para>
+ The provided <literal>PATH</literal> expression parses the
+ row pattern defined by <replaceable>json_api_common_syntax</replaceable>
+ and fills the column with produced SQL/JSON items, one for each row.
+ If the <literal>PATH</literal> expression is omitted,
+ <function>JSON_TABLE</function> uses the
+ <literal>$.<replaceable>name</replaceable></literal> path expression,
+ where <replaceable>name</replaceable> is the provided column name.
+ In this case, the column name must correspond to one of the
+ keys within the SQL/JSON item produced by the row pattern.
+ </para>
+ <para>
+ Optionally, you can add <literal>ON EMPTY</literal> and
+ <literal>ON ERROR</literal> clauses to define how to handle
+ missing values or structural errors. These clauses have the same syntax
+ and semantics as in <xref linkend="functions-jsonvalue"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal><replaceable>name</replaceable> <replaceable>type</replaceable> FORMAT <replaceable>json_representation</replaceable>
+ [ PATH <replaceable>json_path_specification</replaceable> ]</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Gerenates a column and inserts a composite SQL/JSON
+ item into each row of this column.
+ </para>
+ <para>
+ The provided <literal>PATH</literal> expression parses the
+ row pattern defined by <replaceable>json_api_common_syntax</replaceable>
+ and fills the column with produced SQL/JSON items, one for each row.
+ If the <literal>PATH</literal> expression is omitted,
+ <function>JSON_TABLE</function> uses the
+ <literal>$.<replaceable>name</replaceable></literal> path expression,
+ where <replaceable>name</replaceable> is the provided column name.
+ In this case, the column name must correspond to one of the
+ keys within the SQL/JSON item produced by the row pattern.
+ </para>
+ <para>
+ Optionally, you can add <literal>WRAPPER</literal>, <literal>QUOTES</literal>,
+ <literal>ON EMPTY</literal> and <literal>ON ERROR</literal> clauses
+ to define additional settings for the returned SQL/JSON items.
+ These clauses have the same syntax and semantics as
+ in <xref linkend="functions-jsonquery"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>NESTED PATH <replaceable>json_path_specification</replaceable> [ AS <replaceable>json_path_name</replaceable> ]
+ COLUMNS ( <replaceable>json_table_column</replaceable> [, ...] )</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Extracts SQL/JSON items from nested levels of the row pattern,
+ gerenates one or more columns as defined by the <literal>COLUMNS</literal>
+ subclause, and inserts the extracted SQL/JSON items into each row of these columns.
+ The <replaceable>json_table_column</replaceable> expression in the
+ <literal>COLUMNS</literal> subclause uses the same syntax as in the
+ parent <literal>COLUMNS</literal> clause.
+ </para>
+
+ <para>
+ The <literal>NESTED PATH</literal> syntax is recursive,
+ so you can go down multiple nested levels by specifying several
+ <literal>NESTED PATH</literal> subclauses within each other.
+ It allows to unnest the hierarchy of JSON objects and arrays
+ in a single function invocation rather than chaining several
+ <function>JSON_TABLE</function> expressions in an SQL statement.
+ </para>
+
+ <para>
+ You can use the <literal>PLAN</literal> clause to define how
+ to join the columns returned by <replaceable>NESTED PATH</replaceable> clauses.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal><replaceable>name</replaceable> FOR ORDINALITY</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Adds an ordinality column that provides sequential row numbering.
+ You can have only one ordinality column per table. Row numbering
+ is 1-based. For child rows that result from the <literal>NESTED PATH</literal>
+ clauses, the parent row number is repeated.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal><replaceable>json_path_name</replaceable></literal>
+ </term>
+ <listitem>
+
+ <para>
+ The optional <replaceable>json_path_name</replaceable> serves as an
+ identifier of the provided <replaceable>json_path_specification</replaceable>.
+ The path name must be unique and cannot coincide with column names.
+ When using the <literal>PLAN</literal> clause, you must specify the names
+ for all the paths, including the row pattern. Each path name can appear in
+ the <literal>PLAN</literal> clause only once.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>PLAN ( <replaceable class="parameter">json_table_plan</replaceable> )</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Defines how to join the data returned by <replaceable>NESTED PATH</replaceable>
+ clauses to the constructed view.
+ </para>
+ <para>
+ Each <literal>NESTED PATH</literal> clause can generate one or more
+ columns, which are considered to be <firstterm>siblings</firstterm>
+ to each other. In relation to the columns returned directly from the row
+ expression or by the <literal>NESTED PATH</literal> clause of a
+ higher level, these columns are <firstterm>child</firstterm> columns.
+ Sibling columns are always joined first. Once they are processed,
+ the resulting rows are joined to the parent row.
+ </para>
+ <para>
+ To join columns with parent/child relationship, you can use:
+ </para>
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal>INNER</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Use <literal>INNER JOIN</literal>, so that the parent row
+ is omitted from the output if it does not have any child rows
+ after joining the data returned by <literal>NESTED PATH</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>OUTER</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Use <literal>LEFT OUTER JOIN</literal>, so that the parent row
+ is always included into the output even if it does not have any child rows
+ after joining the data returned by <literal>NESTED PATH</literal>, with NULL values
+ inserted into the child columns if the corresponding
+ values are missing.
+ </para>
+ <para>
+ This is the default option for joining columns with parent/child relationship.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <para>
+ To join sibling columns, you can use:
+ </para>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal>UNION</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Use <literal>FULL OUTER JOIN ON FALSE</literal>, so that both parent and child
+ rows are included into the output, with NULL values inserted
+ into both child and parrent columns for all missing values.
+ </para>
+ <para>
+ This is the default option for joining sibling columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>CROSS</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Use <literal>CROSS JOIN</literal>, so that the output includes
+ a row for every possible combination of rows from the left-hand
+ and the right-hand columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>PLAN DEFAULT ( <replaceable>option</replaceable> [, ... ] )</literal>
+ </term>
+ <listitem>
+ <para>
+ Overrides the default joining plans. The <literal>INNER</literal> and
+ <literal>OUTER</literal> options define the joining plan for parent/child
+ columns, while <literal>UNION</literal> and <literal>CROSS</literal>
+ affect the sibling columns. You can override the default plans for all columns at once.
+ Even though the path names are not incuded into the <literal>PLAN DEFAULT</literal>
+ clause, they must be provided for all the paths to conform to
+ the SQL/JSON standard.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Query the <structname>my_films</structname> table holding
+ some JSON data about the films and create a view that
+ distributes the film genre, title, and director between separate columns:
+<screen>
+SELECT jt.* FROM
+ my_films,
+ JSON_TABLE ( js, '$.favorites[*]' COLUMNS (
+ id FOR ORDINALITY,
+ kind text PATH '$.kind',
+ NESTED PATH '$.films[*]' COLUMNS (
+ title text PATH '$.title',
+ director text PATH '$.director'))) AS jt;
+----+----------+------------------+-------------------
+ id | kind | title | director
+----+----------+------------------+-------------------
+ 1 | comedy | Bananas | Woody Allen
+ 1 | comedy | The Dinner Game | Francis Veber
+ 2 | horror | Psycho | Alfred Hitchcock
+ 3 | thriller | Vertigo | Hitchcock
+ 4 | drama | Yojimbo | Akira Kurosawa
+ (5 rows)
+</screen>
+ </para>
+
+ <para>
+ Find a director that has done films in two different genres:
+<screen>
+SELECT
+ director1 AS director, title1, kind1, title2, kind2
+FROM
+ my_films,
+ JSON_TABLE ( js, '$.favorites' AS favs COLUMNS (
+ NESTED PATH '$[*]' AS films1 COLUMNS (
+ kind1 text PATH '$.kind',
+ NESTED PATH '$.films[*]' AS film1 COLUMNS (
+ title1 text PATH '$.title',
+ director1 text PATH '$.director')
+ ),
+ NESTED PATH '$[*]' AS films2 COLUMNS (
+ kind2 text PATH '$.kind',
+ NESTED PATH '$.films[*]' AS film2 COLUMNS (
+ title2 text PATH '$.title',
+ director2 text PATH '$.director'
+ )
+ )
+ )
+ PLAN (favs OUTER ((films1 INNER film1) CROSS (films2 INNER film2)))
+ ) AS jt
+ WHERE kind1 > kind2 AND director1 = director2;
+</screen>
+ </para>
+ </refsect1>
+ </refentry>
+
+ <refentry id="functions-isjson-predicate">
+ <refnamediv>
+ <refname>IS JSON</refname>
+ <refpurpose>test whether the provided value is valid JSON data</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+<replaceable class="parameter">expression</replaceable>
+ IS [ NOT ] JSON
+ [ { VALUE | SCALAR | ARRAY | OBJECT } ]
+ [ { WITH | WITHOUT } UNIQUE [ KEYS ] ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>IS JSON</command> predicate tests whether the provided value is valid
+ <acronym>JSON</acronym> data. If you provide a specific JSON data type as a parameter,
+ you can check whether the value belongs to this type.
+ You can also use this predicate in the <command>IS NOT JSON</command> form.
+ The return values are:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>t</literal> if the value satisfies the specified condition.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>f</literal> if the value does not satisfy the specified condition.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+<variablelist>
+
+ <varlistentry>
+ <term>
+ <literal><replaceable class="parameter">expression</replaceable></literal>
+ </term>
+ <listitem>
+
+ <para>
+ The input clause defining the value to test. You can provide the values of <literal>json</literal>,
+ <literal>jsonb</literal>, <literal>bytea</literal>, or character string types.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>VALUE | SCALAR | ARRAY | OBJECT</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Specifies the <acronym>JSON</acronym> data type to test for:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>VALUE</literal> (default) — any <acronym>JSON</acronym> type.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>SCALAR</literal> — <acronym>JSON</acronym> number, string, or boolean.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>ARRAY</literal> — <acronym>JSON</acronym> array.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>OBJECT</literal> — <acronym>JSON</acronym> object.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>{ WITH | WITHOUT } UNIQUE [ KEYS ]</literal>
+ </term>
+ <listitem>
+ <para>Defines whether duplicate keys are allowed:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>WITHOUT</literal> (default) — the
+ <acronym>JSON</acronym> object can contain duplicate keys.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>WITH</literal> — duplicate keys are not allowed.
+ If the input data contains duplicate keys, it is considered to be invalid JSON.
+ </para>
+ </listitem>
+ </itemizedlist>
+ Optionally, you can add the <literal>KEYS</literal> keyword for semantic clarity.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Compare the result returned by the <function>IS JSON</function>
+ predicate for different data types:
+ </para>
+ <screen>
+SELECT
+ js,
+ js IS JSON "is json",
+ js IS NOT JSON "is not json",
+ js IS JSON SCALAR "is scalar",
+ js IS JSON OBJECT "is object",
+ js IS JSON ARRAY "is array"
+FROM
+ (VALUES ('123'), ('"abc"'), ('{"a": "b"}'), ('[1,2]'), ('abc')) foo(js);
+
+ js | is json | is not json | is scalar | is object | is array
+------------+---------+-------------+-----------+-----------|-------------
+ 123 | t | f | t | f | f
+ "abc" | t | f | t | f | f
+ {"a": "b"} | t | f | f | t | f
+ [1,2] | t | f | f | f | t
+ abc | f | t | f | f | f
+(5 rows)
+</screen>
+ </refsect1>
+ </refentry>
+ </sect3>
+
+ <sect3 id="sqljson-common-clauses">
+ <title>SQL/JSON Common Clauses</title>
+
+ <sect4 id="sqljson-input-clause">
+ <title>SQL/JSON Input Clause</title>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal><replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable>
+[ PASSING { <replaceable>value</replaceable> AS <replaceable>varname</replaceable> } [, ...]]</literal>
+ </term>
+ <listitem>
+ <para>
+ The input clause specifies the JSON data to query and
+ the exact query path to be passed to SQL/JSON query functions:
+ </para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ The <replaceable>context_item</replaceable> is the JSON data to query.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The <replaceable>path_expression</replaceable> is an SQL/JSON path
+ expression that specifies the items to be retrieved from the JSON
+ data. For details on path expression syntax, see
+ <xref linkend="functions-sqljson-path"/>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The optional <command>PASSING</command> clause provides the values for
+ the named variables used in the SQL/JSON path expression.
+ </para>
+ </listitem>
+ </itemizedlist>
+ <para>
+ The input clause is common for all SQL/JSON query functions.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ </sect4>
+
+ <sect4 id="sqljson-output-clause">
+ <title>SQL/JSON Output Clause</title>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal>RETURNING <replaceable class="parameter">data_type</replaceable> [ FORMAT JSON [ ENCODING UTF8 ] ]</literal>
+ </term>
+ <listitem>
+ <para>
+ The output clause that specifies the return type of the generated
+ <acronym>JSON</acronym> object. Out of the box, <productname>PostgreSQL</productname>
+ supports the following types: <type>json</type>, <type>jsonb</type>,
+ <type>bytea</type>, and character string types (<type>text</type>, <type>char</type>,
+ <type>varchar</type>, and <type>nchar</type>).
+ To use other types, you must create the <literal>CAST</literal> from <type>json</type> for this type.
+ By default, the <type>json</type> type is returned.
+ </para>
+ <para>
+ The optional <literal>FORMAT</literal> clause is provided to conform to the SQL/JSON standard.
+ </para>
+ <para>
+ The output clause is common for both constructor and query SQL/JSON functions.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ </sect4>
+
+ </sect3>
+ </sect2>
+
+ <sect2 id="functions-sqljson-path">
+ <title>SQL/JSON Path Expressions</title>
+
+ <para>
+ SQL/JSON path expressions specify the items to be retrieved
+ from the JSON data, which are passed to SQL/JSON query functions
+ as one of the parameters. Path expressions belong to a special
+ <type>jsonpath</type> type described in <xref linkend="datatype-jsonpath"/>.
+ </para>
+
+ <para>
+ The SQL/JSON query functions pass the provided path expression to
+ the <firstterm>path engine</firstterm> for evaluation.
+ The path expression is evaluated from left to right.
+ You can use parentheses to change the order of operations.
+ If the evaluation is successful, an SQL/JSON sequence is produced.
+ The evaluation result is returned to the SQL/JSON query function
+ that completes the specified computation. If the query result
+ must be JSON text, you have to use the <command>WITH WRAPPER</command> clause
+ or enclose the path expression in square brackets to ensure
+ the evaluation result is an array.
+ </para>
+
+ <para>
+ A typical path expression has the following structure:
+ </para>
+
+ <programlisting>
+'[strict | lax] <replaceable>path_specification</replaceable> [? (<replaceable>filter_expression</replaceable>) ...]'
+</programlisting>
+
+ <para>
+ where:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ The optional <literal>strict</literal> or <literal>lax</literal> mode
+ defines how to handle structural errors, as explained in
+ <xref linkend="strict-and-lax-modes"/>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The <firstterm>path_specification</firstterm> defines the parts
+ of JSON data to be retrieved by the SQL/JSON query functions.
+ To learn the syntax of the path specification, see
+ <xref linkend="sqljson-path-specification"/>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The optional <firstterm>filter_expression</firstterm> can include
+ one or more filtering conditions to apply to the result of the
+ path evaluation. For details, see <xref linkend="sqljson-filter-clause"/>.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <sect3 id="strict-and-lax-modes">
+ <title>Strict and Lax Modes</title>
+ <para>
+ When you query JSON data, the path expression may not match the
+ actual JSON data structure. An attempt to access a non-existent
+ member of an object or element of an array results in a
+ structural error. SQL/JSON path expressions have two modes
+ of handling structural errors:
+ </para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ lax (default) — the path engine implicitly adapts
+ the queried data to the specified path.
+ Any remaining structural errors are suppressed and converted
+ to empty SQL/JSON sequences.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ strict — if a structural error occurs,
+ an error is raised.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <para>
+ The lax mode facilitates matching of a JSON document structure and path
+ expression if the JSON data does not conform to the expected schema.
+ If an operand does not match the requirements of a particular
+ operation, it can be automatically wrapped as an SQL/JSON array or unwrapped
+ by converting its elements into an SQL/JSON sequence before performing
+ this operation. Besides, comparison operators automatically unwrap their
+ operands in the lax mode, so you can compare SQL/JSON arrays out-of-the-box.
+ Arrays of size 1 are interchangeable with a singleton.
+ </para>
+
+ <note>
+ <para>
+ In the lax mode, implicit unwrapping only goes one level down.
+ If the arrays are nested, only the outermost array is unwrapped,
+ while all the inner arrays remain unchanged.
+ </para>
+ </note>
+
+ <para>
+ If you prefer using the strict mode, the specified path must exactly match
+ the structure of the queried JSON document. You can still get some
+ error-handling flexibility by using the <command>JSON_EXISTS</command>
+ predicate, which checks whether the element to be accessed is
+ available. It allows to convert structural errors to empty SQL/JSON
+ sequences on a selective basis, achieving lax semantics in the strict
+ mode as required.
+ </para>
+
+ <para>
+ In both strict and lax modes, the actual interpretation of the returned value
+ depends on the <literal>ON ERROR</literal> or <literal>ON EMPTY</literal>
+ clauses of the SQL/JSON query functions, as explained in <xref linkend="functions-jsonquery"/>.
+ </para>
+ </sect3>
+
+ <sect3 id="sqljson-path-specification">
+ <title>Path Specification</title>
+
+ <para>
+ A path specification defines the exact path to access one or more items
+ within JSON data using the SQL/JSON path language.
+ </para>
+
+ <para>
+ The general structure of a path specification is as follows:
+ <itemizedlist>
+ <listitem>
+ <para>
+ Each path specification starts with a <literal>$</literal> sign,
+ which denotes the JSON text to be queried (the <firstterm>context item</firstterm>).
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The context item can be followed
+ by one or more <link linkend="type-jsonpath-accessors">accessor operators</link>.
+ Going down the JSON structure level by level,
+ these operators return an SQL/JSON sequence if path evaluation is successful.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Path evaluation results can be further processed by one or more <type>jsonpath</type>
+ operators and methods listed in <xref linkend="functions-sqljson-path-operators"/>.
+ Each method must be preceded by a dot, while arithmetic and boolean
+ operators are separated from the operands by spaces.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ If the path specification is enclosed into square brackets <literal>[]</literal>,
+ path evaluation result is automatically wrapped into an array.
+ This is a <productname>PostgreSQL</productname> extension of the SQL/JSON standard.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+<!-- TBD: path expression examples -->
+
+ <para>
+ Consider the following path specification examples:
+ <programlisting>
+'$.floor'
+'($+1)'
+'$+1'
+'($.floor[*].apt[*].area > 10)'
+</programlisting>
+ </para>
+
+ <para>Writing the path as an expression is also a valid path specification:
+ <programlisting>
+'$' || '.' || 'a'
+</programlisting>
+ </para>
+
+ <para>
+ If you use any named variables in the path specification, you must define
+ their values in the <command>PASSING</command> clause of the SQL/JSON query functions.
+ </para>
+
+ </sect3>
+
+ <sect3 id="sqljson-filter-clause">
+ <title>Filter Clause</title>
+
+ <para>
+ The optional filter clause is similar to the <command>WHERE</command>
+ clause in SQL. The filter clause can provide one or more
+ <firstterm>filter expressions</firstterm>, each including one or more
+ filtering conditions to apply to the result of the path evaluation.
+ Filter expressions are applied from left to right and can be nested.
+ The <literal>@</literal> variable denotes the current item returned
+ by the path evaluation to which the filtering condition should be applied.
+ </para>
+
+ <para>
+ Filter expressions must be enclosed in parentheses and preceded by
+ a question mark <literal>?</literal>. Functions and operators that can be used in
+ filter expressions are listed in <xref linkend="functions-sqljson-filter-ex-table"/>.
+ The result of the filter expression may be true, false, or unknown.
+ </para>
+
+<!-- TBD: filter examples -->
+
+ </sect3>
+
+ <sect3 id="functions-sqljson-path-operators">
+ <title>SQL/JSON Path Operators and Methods</title>
+
+ <table id="functions-sqljson-op-table">
+ <title><type>jsonpath</type> Operators and Methods</title>
+ <tgroup cols="5">
+ <thead>
+ <row>
+ <entry>Operator/Method</entry>
+ <entry>Description</entry>
+ <entry>Example JSON</entry>
+ <entry>Example Query</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>+</literal> (unary)</entry>
+ <entry>Plus operator that iterates over the json sequence</entry>
+ <entry><literal>{"x": [2.85, -14.7, -9.4]}</literal></entry>
+ <entry><literal>+ $.x.floor()</literal></entry>
+ <entry><literal>2, -15, -10</literal></entry>
+ </row>
+ <row>
+ <entry><literal>-</literal> (unary)</entry>
+ <entry>Minus operator that iterates over the json sequence</entry>
+ <entry><literal>{"x": [2.85, -14.7, -9.4]}</literal></entry>
+ <entry><literal>- $.x.floor()</literal></entry>
+ <entry><literal>-2, 15, 10</literal></entry>
+ </row>
+ <row>
+ <entry><literal>+</literal> (binary)</entry>
+ <entry>Addition</entry>
+ <entry><literal>[2]</literal></entry>
+ <entry><literal>2 + $[0]</literal></entry>
+ <entry><literal>4</literal></entry>
+ </row>
+ <row>
+ <entry><literal>-</literal> (binary)</entry>
+ <entry>Subtraction</entry>
+ <entry><literal>[2]</literal></entry>
+ <entry><literal>4 - $[0]</literal></entry>
+ <entry><literal>2</literal></entry>
+ </row>
+ <row>
+ <entry><literal>*</literal></entry>
+ <entry>Multiplication</entry>
+ <entry><literal>[4]</literal></entry>
+ <entry><literal>2 * $[0]</literal></entry>
+ <entry><literal>8</literal></entry>
+ </row>
+ <row>
+ <entry><literal>/</literal></entry>
+ <entry>Division</entry>
+ <entry><literal>[8]</literal></entry>
+ <entry><literal>$[0] / 2</literal></entry>
+ <entry><literal>4</literal></entry>
+ </row>
+ <row>
+ <entry><literal>%</literal></entry>
+ <entry>Modulus</entry>
+ <entry><literal>[32]</literal></entry>
+ <entry><literal>$[0] % 10</literal></entry>
+ <entry><literal>2</literal></entry>
+ </row>
+ <row>
+ <entry><literal>type()</literal></entry>
+ <entry>Type of the SQL/JSON item</entry>
+ <entry><literal>[1, "2", {}]</literal></entry>
+ <entry><literal>$[*].type()</literal></entry>
+ <entry><literal>"number", "string", "object"</literal></entry>
+ </row>
+ <row>
+ <entry><literal>size()</literal></entry>
+ <entry>Size of the SQL/JSON item</entry>
+ <entry><literal>{"m": [11, 15]}</literal></entry>
+ <entry><literal>$.m.size()</literal></entry>
+ <entry><literal>2</literal></entry>
+ </row>
+ <row>
+ <entry><literal>double()</literal></entry>
+ <entry>Approximate numeric value converted from a string</entry>
+ <entry><literal>{"len": "1.9"}</literal></entry>
+ <entry><literal>$.len.double() * 2</literal></entry>
+ <entry><literal>3.8</literal></entry>
+ </row>
+ <row>
+ <entry><literal>ceiling()</literal></entry>
+ <entry>Nearest integer greater than or equal to the SQL/JSON number</entry>
+ <entry><literal>{"h": 1.3}</literal></entry>
+ <entry><literal>$.h.ceiling()</literal></entry>
+ <entry><literal>2</literal></entry>
+ </row>
+ <row>
+ <entry><literal>floor()</literal></entry>
+ <entry>Nearest integer less than or equal to the SQL/JSON number</entry>
+ <entry><literal>{"h": 1.3}</literal></entry>
+ <entry><literal>$.h.floor()</literal></entry>
+ <entry><literal>1</literal></entry>
+ </row>
+ <row>
+ <entry><literal>abs()</literal></entry>
+ <entry>Absolute value of the SQL/JSON number</entry>
+ <entry><literal>{"z": -0.3}</literal></entry>
+ <entry><literal>$.z.abs()</literal></entry>
+ <entry><literal>0.3</literal></entry>
+ </row>
+ <row>
+ <entry><literal>datetime()</literal></entry>
+ <entry>Datetime value converted from a string</entry>
+ <entry><literal>["2015-8-1", "2015-08-12"]</literal></entry>
+ <entry><literal>$[*] ? (@.datetime() < "2015-08-2". datetime())</literal></entry>
+ <entry><literal>2015-8-1</literal></entry>
+ </row>
+ <row>
+ <entry><literal>datetime(<replaceable>template</replaceable>)</literal></entry>
+ <entry>Datetime value converted from a string with a specified template</entry>
+ <entry><literal>["12:30", "18:40"]</literal></entry>
+ <entry><literal>$[*].datetime("HH24:MI")</literal></entry>
+ <entry><literal>"12:30:00", "18:40:00"</literal></entry>
+ </row>
+ <row>
+ <entry><literal>keyvalue()</literal></entry>
+ <entry>Array of objects containing two members ("key" and "value" of the SQL/JSON item)</entry>
+ <entry><literal>{"x": "20", "y": 32}</literal></entry>
+ <entry><literal>$.keyvalue()</literal></entry>
+ <entry><literal>{"key": "x", "value": "20"}, {"key": "y", "value": 32}</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ <table id="functions-sqljson-filter-ex-table">
+ <title><type>jsonpath</type> Filter Expression Elements</title>
+ <tgroup cols="5">
+ <thead>
+ <row>
+ <entry>Value/Predicate</entry>
+ <entry>Description</entry>
+ <entry>Example JSON</entry>
+ <entry>Example Query</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>==</literal></entry>
+ <entry>Equality operator</entry>
+ <entry><literal>[1, 2, 1, 3]</literal></entry>
+ <entry><literal>$[*] ? (@ == 1)</literal></entry>
+ <entry><literal>1, 1</literal></entry>
+ </row>
+ <row>
+ <entry><literal>!=</literal></entry>
+ <entry>Non-equality operator</entry>
+ <entry><literal>[1, 2, 1, 3]</literal></entry>
+ <entry><literal>$[*] ? (@ != 1)</literal></entry>
+ <entry><literal>2, 3</literal></entry>
+ </row>
+ <row>
+ <entry><literal><></literal></entry>
+ <entry>Non-equality operator (same as <literal>!=</literal>)</entry>
+ <entry><literal>[1, 2, 1, 3]</literal></entry>
+ <entry><literal>$[*] ? (@ <> 1)</literal></entry>
+ <entry><literal>2, 3</literal></entry>
+ </row>
+ <row>
+ <entry><literal><</literal></entry>
+ <entry>Less-than operator</entry>
+ <entry><literal>[1, 2, 3]</literal></entry>
+ <entry><literal>$[*] ? (@ < 2)</literal></entry>
+ <entry><literal>1, 2</literal></entry>
+ </row>
+ <row>
+ <entry><literal><=</literal></entry>
+ <entry>Less-than-or-equal-to operator</entry>
+ <entry><literal>[1, 2, 3]</literal></entry>
+ <entry><literal>$[*] ? (@ < 2)</literal></entry>
+ <entry><literal>1</literal></entry>
+ </row>
+ <row>
+ <entry><literal>></literal></entry>
+ <entry>Greater-than operator</entry>
+ <entry><literal>[1, 2, 3]</literal></entry>
+ <entry><literal>$[*] ? (@ > 2)</literal></entry>
+ <entry><literal>3</literal></entry>
+ </row>
+ <row>
+ <entry><literal>></literal></entry>
+ <entry>Greater-than-or-equal-to operator</entry>
+ <entry><literal>[1, 2, 3]</literal></entry>
+ <entry><literal>$[*] ? (@ >= 2)</literal></entry>
+ <entry><literal>2, 3</literal></entry>
+ </row>
+ <row>
+ <entry><literal>true</literal></entry>
+ <entry>Value used to perform comparison with JSON <literal>true</literal> literal</entry>
+ <entry><literal>[{"name": "John", "parent": false},
+ {"name": "Chris", "parent": true}]</literal></entry>
+ <entry><literal>$[*] ? (@.parent == true)</literal></entry>
+ <entry><literal>{"name": "Chris", "parent": true}</literal></entry>
+ </row>
+ <row>
+ <entry><literal>false</literal></entry>
+ <entry>Value used to perform comparison with JSON <literal>false</literal> literal</entry>
+ <entry><literal>[{"name": "John", "parent": false},
+ {"name": "Chris", "parent": true}]</literal></entry>
+ <entry><literal>$[*] ? (@.parent == false)</literal></entry>
+ <entry><literal>{"name": "John", "parent": false}</literal></entry>
+ </row>
+ <row>
+ <entry><literal>null</literal></entry>
+ <entry>Value used to perform comparison with JSON <literal>null</literal> value</entry>
+ <entry><literal>[{"name": "Mary", "job": null},
+ {"name": "Michael", "job": "driver"}]</literal></entry>
+ <entry><literal>$[*] ? (@.job == null) .name</literal></entry>
+ <entry><literal>"Mary"</literal></entry>
+ </row>
+ <row>
+ <entry><literal>&&</literal></entry>
+ <entry>Boolean AND</entry>
+ <entry><literal>[1, 3, 7]</literal></entry>
+ <entry><literal>$[*] ? (@ > 1 && @ < 5)</literal></entry>
+ <entry><literal>3</literal></entry>
+ </row>
+ <row>
+ <entry><literal>||</literal></entry>
+ <entry>Boolean OR</entry>
+ <entry><literal>[1, 3, 7]</literal></entry>
+ <entry><literal>$[*] ? (@ < 1 || @ > 5)</literal></entry>
+ <entry><literal>7</literal></entry>
+ </row>
+ <row>
+ <entry><literal>!</literal></entry>
+ <entry>Boolean NOT</entry>
+ <entry><literal>[1, 3, 7]</literal></entry>
+ <entry><literal>$[*] ? (!(@ < 5))</literal></entry>
+ <entry><literal>7</literal></entry>
+ </row>
+ <row>
+ <entry><literal>like_regex</literal></entry>
+ <entry>Tests pattern matching with regular expressions</entry>
+ <entry><literal>["abc", "abd", "aBdC", "abdacb", "babc"]</literal></entry>
+ <entry><literal>$[*] ? (@ like_regex "^ab.*c" flag "i")</literal></entry>
+ <entry><literal>"abc", "aBdC", "abdacb"</literal></entry>
+ </row>
+ <row>
+ <entry><literal>starts with</literal></entry>
+ <entry>Tests whether the second operand is an initial substring of the first operand</entry>
+ <entry><literal>["John Smith", "Mary Stone", "Bob Johnson"]</literal></entry>
+ <entry><literal>$[*] ? (@ starts with "John")</literal></entry>
+ <entry><literal>"John Smith"</literal></entry>
+ </row>
+ <row>
+ <entry><literal>exists</literal></entry>
+ <entry>Tests whether a path expression has at least one SQL/JSON item</entry>
+ <entry><literal>{"x": [1, 2], "y": [2, 4]}</literal></entry>
+ <entry><literal>strict $.* ? (exists (@ ? (@[*] > 2)))</literal></entry>
+ <entry><literal>2, 4</literal></entry>
+ </row>
+ <row>
+ <entry><literal>is unknown</literal></entry>
+ <entry>Tests whether a boolean condition is <literal>unknown</literal></entry>
+ <entry><literal>[-1, 2, 7, "infinity"]</literal></entry>
+ <entry><literal>$[*] ? ((@ > 0) is unknown)</literal></entry>
+ <entry><literal>"infinity"</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <table id="functions-sqljson-extra-op-table">
+ <title>Extended <type>jsonpath</type> Methods</title>
+ <tgroup cols="5">
+ <thead>
+ <row>
+ <entry>Method</entry>
+ <entry>Description</entry>
+ <entry>Example JSON</entry>
+ <entry>Example Query</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>min()</literal></entry>
+ <entry>Minimum value in the json array</entry>
+ <entry><literal>[1, 2, 0, 3, 1]</literal></entry>
+ <entry><literal>$.min()</literal></entry>
+ <entry><literal>0</literal></entry>
+ </row>
+ <row>
+ <entry><literal>max()</literal></entry>
+ <entry>Maximum value in the json array</entry>
+ <entry><literal>[1, 2, 0, 3, 1]</literal></entry>
+ <entry><literal>$.max()</literal></entry>
+ <entry><literal>3</literal></entry>
+ </row>
+ <row>
+ <entry><literal>map()</literal></entry>
+ <entry>Calculate an expression by applying a given function
+ to each element of the json array
+ </entry>
+ <entry><literal>[1, 2, 0]</literal></entry>
+ <entry><literal>$.map(@ * 2)</literal></entry>
+ <entry><literal>[2, 4, 0]</literal></entry>
+ </row>
+ <row>
+ <entry><literal>reduce()</literal></entry>
+ <entry>Calculate an aggregate expression by combining elements
+ of the json array using a given function
+ ($1 references the current result, $2 references the current element)
+ </entry>
+ <entry><literal>[3, 5, 9]</literal></entry>
+ <entry><literal>$.reduce($1 + $2)</literal></entry>
+ <entry><literal>17</literal></entry>
+ </row>
+ <row>
+ <entry><literal>fold()</literal></entry>
+ <entry>Calculate an aggregate expression by combining elements
+ of the json array using a given function
+ with the specified initial value
+ ($1 references the current result, $2 references the current element)
+ </entry>
+ <entry><literal>[2, 3, 4]</literal></entry>
+ <entry><literal>$.fold($1 * $2, 1)</literal></entry>
+ <entry><literal>24</literal></entry>
+ </row>
+ <row>
+ <entry><literal>foldl()</literal></entry>
+ <entry>Calculate an aggregate expression by combining elements
+ of the json array using a given function from left to right
+ with the specified initial value
+ ($1 references the current result, $2 references the current element)
+ </entry>
+ <entry><literal>[1, 2, 3]</literal></entry>
+ <entry><literal>$.foldl([$1, $2], [])</literal></entry>
+ <entry><literal>[[[[], 1], 2], 3]</literal></entry>
+ </row>
+ <row>
+ <entry><literal>foldr()</literal></entry>
+ <entry>Calculate an aggregate expression by combining elements
+ of the json array using a given function from right to left
+ with the specified initial value
+ ($1 references the current result, $2 references the current element)
+ </entry>
+ <entry><literal>[1, 2, 3]</literal></entry>
+ <entry><literal>$.foldr([$2, $1], [])</literal></entry>
+ <entry><literal>[[[[], 3], 2], 1]</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect3>
+
+ </sect2>
+
+ <sect2 id="functions-pgjson">
+ <title>PostgreSQL-specific JSON Functions and Operators</title>
+<!-- TBD Check references to functions-json -->
+ <indexterm zone="functions-pgjson">
+ <primary>JSON</primary>
+ <secondary>functions and operators</secondary>
+ </indexterm>
+
+ <para>
+ <xref linkend="functions-json-op-table"/> shows the operators that
+ are available for use with JSON data types (see <xref
+ linkend="datatype-json"/>).
+ </para>
+
+ <table id="functions-json-op-table">
+ <title><type>json</type> and <type>jsonb</type> Operators</title>
+ <tgroup cols="6">
+ <thead>
+ <row>
+ <entry>Operator</entry>
+ <entry>Right Operand Type</entry>
+ <entry>Return type</entry>
+ <entry>Description</entry>
+ <entry>Example</entry>
+ <entry>Example Result</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>-></literal></entry>
+ <entry><type>int</type></entry>
+ <entry><type>json</type> or <type>jsonb</type></entry>
+ <entry>Get JSON array element (indexed from zero, negative
+ integers count from the end)</entry>
+ <entry><literal>'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2</literal></entry>
+ <entry><literal>{"c":"baz"}</literal></entry>
+ </row>
+ <row>
+ <entry><literal>-></literal></entry>
+ <entry><type>text</type></entry>
+ <entry><type>json</type> or <type>jsonb</type></entry>
+ <entry>Get JSON object field by key</entry>
+ <entry><literal>'{"a": {"b":"foo"}}'::json->'a'</literal></entry>
+ <entry><literal>{"b":"foo"}</literal></entry>
+ </row>
+ <row>
+ <entry><literal>->></literal></entry>
+ <entry><type>int</type></entry>
+ <entry><type>text</type></entry>
+ <entry>Get JSON array element as <type>text</type></entry>
+ <entry><literal>'[1,2,3]'::json->>2</literal></entry>
+ <entry><literal>3</literal></entry>
+ </row>
+ <row>
+ <entry><literal>->></literal></entry>
+ <entry><type>text</type></entry>
+ <entry><type>text</type></entry>
+ <entry>Get JSON object field as <type>text</type></entry>
+ <entry><literal>'{"a":1,"b":2}'::json->>'b'</literal></entry>
+ <entry><literal>2</literal></entry>
+ </row>
+ <row>
+ <entry><literal>#></literal></entry>
+ <entry><type>text[]</type></entry>
+ <entry><type>json</type> or <type>jsonb</type></entry>
+ <entry>Get JSON object at the specified path</entry>
+ <entry><literal>'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'</literal></entry>
+ <entry><literal>{"c": "foo"}</literal></entry>
+ </row>
+ <row>
+ <entry><literal>#>></literal></entry>
+ <entry><type>text[]</type></entry>
+ <entry><type>text</type></entry>
+ <entry>Get JSON object at the specified path as <type>text</type></entry>
+ <entry><literal>'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'</literal></entry>
+ <entry><literal>3</literal></entry>
+ </row>
+ <row>
+ <entry><literal>@*</literal></entry>
+ <entry><type>jsonpath</type></entry>
+ <entry><type>setof json</type> or <type>setof jsonb</type></entry>
+ <entry>Get all JSON items returned by JSON path for the specified JSON value</entry>
+ <entry><literal>'{"a":[1,2,3,4,5]}'::json @* '$.a[*] ? (@ > 2)'</literal></entry>
+ <entry><programlisting>
+3
+4
+5
+</programlisting></entry>
+ </row>
+ <row>
+ <entry><literal>@#</literal></entry>
+ <entry><type>jsonpath</type></entry>
+ <entry><type>json</type> or <type>jsonb</type></entry>
+ <entry>Get all JSON items returned by JSON path for the specified JSON value. If there is more than one item, they will be wrapped into an array.</entry>
+ <entry><literal>'{"a":[1,2,3,4,5]}'::json @# '$.a[*] ? (@ > 2)'</literal></entry>
+ <entry><literal>[3, 4, 5]</literal></entry>
+ </row>
+ <row>
+ <entry><literal>@?</literal></entry>
+ <entry><type>jsonpath</type></entry>
+ <entry><type>boolean</type></entry>
+ <entry>Check whether JSON path returns any item for the specified JSON value</entry>
+ <entry><literal>'{"a":[1,2,3,4,5]}'::json @? '$.a[*] ? (@ > 2)'</literal></entry>
+ <entry><literal>true</literal></entry>
+ </row>
+ <row>
+ <entry><literal>@~</literal></entry>
+ <entry><type>jsonpath</type></entry>
+ <entry><type>boolean</type></entry>
+ <entry>Get JSON path predicate result for the specified JSON value</entry>
+ <entry><literal>'{"a":[1,2,3,4,5]}'::json @~ '$.a[*] > 2'</literal></entry>
+ <entry><literal>true</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <note>
+ <para>
+ There are parallel variants of these operators for both the
+ <type>json</type> and <type>jsonb</type> types.
+ The field/element/path extraction operators
+ return the same type as their left-hand input (either <type>json</type>
+ or <type>jsonb</type>), except for those specified as
+ returning <type>text</type>, which coerce the value to text.
+ The field/element/path extraction operators return NULL, rather than
+ failing, if the JSON input does not have the right structure to match
+ the request; for example if no such element exists. The
+ field/element/path extraction operators that accept integer JSON
+ array subscripts all support negative subscripting from the end of
+ arrays.
+ </para>
+ </note>
+ <para>
+ The standard comparison operators shown in <xref
+ linkend="functions-comparison-op-table"/> are available for
+ <type>jsonb</type>, but not for <type>json</type>. They follow the
+ ordering rules for B-tree operations outlined at <xref
+ linkend="json-indexing"/>.
+ </para>
+ <para>
+ Some further operators also exist only for <type>jsonb</type>, as shown
+ in <xref linkend="functions-jsonb-op-table"/>.
+ Many of these operators can be indexed by
+ <type>jsonb</type> operator classes. For a full description of
+ <type>jsonb</type> containment and existence semantics, see <xref
+ linkend="json-containment"/>. <xref linkend="json-indexing"/>
+ describes how these operators can be used to effectively index
+ <type>jsonb</type> data.
+ </para>
+ <table id="functions-jsonb-op-table">
+ <title>Additional <type>jsonb</type> Operators</title>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Operator</entry>
+ <entry>Right Operand Type</entry>
+ <entry>Description</entry>
+ <entry>Example</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>@></literal></entry>
+ <entry><type>jsonb</type></entry>
+ <entry>Does the left JSON value contain the right JSON
+ path/value entries at the top level?</entry>
+ <entry><literal>'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb</literal></entry>
+ </row>
+ <row>
+ <entry><literal><@</literal></entry>
+ <entry><type>jsonb</type></entry>
+ <entry>Are the left JSON path/value entries contained at the top level within
+ the right JSON value?</entry>
+ <entry><literal>'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb</literal></entry>
+ </row>
+ <row>
+ <entry><literal>?</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>Does the <emphasis>string</emphasis> exist as a top-level
+ key within the JSON value?</entry>
+ <entry><literal>'{"a":1, "b":2}'::jsonb ? 'b'</literal></entry>
+ </row>
+ <row>
+ <entry><literal>?|</literal></entry>
+ <entry><type>text[]</type></entry>
+ <entry>Do any of these array <emphasis>strings</emphasis>
+ exist as top-level keys?</entry>
+ <entry><literal>'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']</literal></entry>
+ </row>
+ <row>
+ <entry><literal>?&</literal></entry>
+ <entry><type>text[]</type></entry>
+ <entry>Do all of these array <emphasis>strings</emphasis> exist
+ as top-level keys?</entry>
+ <entry><literal>'["a", "b"]'::jsonb ?& array['a', 'b']</literal></entry>
+ </row>
+ <row>
+ <entry><literal>||</literal></entry>
+ <entry><type>jsonb</type></entry>
+ <entry>Concatenate two <type>jsonb</type> values into a new <type>jsonb</type> value</entry>
+ <entry><literal>'["a", "b"]'::jsonb || '["c", "d"]'::jsonb</literal></entry>
+ </row>
+ <row>
+ <entry><literal>-</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>Delete key/value pair or <emphasis>string</emphasis>
+ element from left operand. Key/value pairs are matched based
+ on their key value.</entry>
+ <entry><literal>'{"a": "b"}'::jsonb - 'a' </literal></entry>
+ </row>
+ <row>
+ <entry><literal>-</literal></entry>
+ <entry><type>text[]</type></entry>
+ <entry>Delete multiple key/value pairs or <emphasis>string</emphasis>
+ elements from left operand. Key/value pairs are matched based
+ on their key value.</entry>
+ <entry><literal>'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] </literal></entry>
+ </row>
+ <row>
+ <entry><literal>-</literal></entry>
+ <entry><type>integer</type></entry>
+ <entry>Delete the array element with specified index (Negative
+ integers count from the end). Throws an error if top level
+ container is not an array.</entry>
+ <entry><literal>'["a", "b"]'::jsonb - 1 </literal></entry>
+ </row>
+ <row>
+ <entry><literal>#-</literal></entry>
+ <entry><type>text[]</type></entry>
+ <entry>Delete the field or element with specified path (for
+ JSON arrays, negative integers count from the end)</entry>
+ <entry><literal>'["a", {"b":1}]'::jsonb #- '{1,b}'</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <note>
+ <para>
+ The <literal>||</literal> operator concatenates the elements at the top level of
+ each of its operands. It does not operate recursively. For example, if
+ both operands are objects with a common key field name, the value of the
+ field in the result will just be the value from the right hand operand.
+ </para>
+ </note>
+
+ <para>
+ <xref linkend="functions-json-creation-table"/> shows the functions that are
+ available for creating <type>json</type> and <type>jsonb</type> values.
+ (There are no equivalent functions for <type>jsonb</type>, of the <literal>row_to_json</literal>
+ and <literal>array_to_json</literal> functions. However, the <literal>to_jsonb</literal>
+ function supplies much the same functionality as these functions would.)
+ </para>
+
+ <indexterm>
+ <primary>to_json</primary>
+ </indexterm>
+ <indexterm>
+ <primary>array_to_json</primary>
+ </indexterm>
+ <indexterm>
+ <primary>row_to_json</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_build_array</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_build_object</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_object</primary>
+ </indexterm>
+ <indexterm>
+ <primary>to_jsonb</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_build_array</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_build_object</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_object</primary>
+ </indexterm>
+
+ <table id="functions-json-creation-table">
+ <title>JSON Creation Functions</title>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Function</entry>
+ <entry>Description</entry>
+ <entry>Example</entry>
+ <entry>Example Result</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><para><literal>to_json(anyelement)</literal>
+ </para><para><literal>to_jsonb(anyelement)</literal>
+ </para></entry>
+ <entry>
+ Returns the value as <type>json</type> or <type>jsonb</type>.
+ Arrays and composites are converted
+ (recursively) to arrays and objects; otherwise, if there is a cast
+ from the type to <type>json</type>, the cast function will be used to
+ perform the conversion; otherwise, a scalar value is produced.
+ For any scalar type other than a number, a Boolean, or a null value,
+ the text representation will be used, in such a fashion that it is a
+ valid <type>json</type> or <type>jsonb</type> value.
+ </entry>
+ <entry><literal>to_json('Fred said "Hi."'::text)</literal></entry>
+ <entry><literal>"Fred said \"Hi.\""</literal></entry>
+ </row>
+ <row>
+ <entry>
+ <literal>array_to_json(anyarray [, pretty_bool])</literal>
+ </entry>
+ <entry>
+ Returns the array as a JSON array. A PostgreSQL multidimensional array
+ becomes a JSON array of arrays. Line feeds will be added between
+ dimension-1 elements if <parameter>pretty_bool</parameter> is true.
+ </entry>
+ <entry><literal>array_to_json('{{1,5},{99,100}}'::int[])</literal></entry>
+ <entry><literal>[[1,5],[99,100]]</literal></entry>
+ </row>
+ <row>
+ <entry>
+ <literal>row_to_json(record [, pretty_bool])</literal>
+ </entry>
+ <entry>
+ Returns the row as a JSON object. Line feeds will be added between
+ level-1 elements if <parameter>pretty_bool</parameter> is true.
+ </entry>
+ <entry><literal>row_to_json(row(1,'foo'))</literal></entry>
+ <entry><literal>{"f1":1,"f2":"foo"}</literal></entry>
+ </row>
+ <row>
+ <entry><para><literal>json_build_array(VARIADIC "any")</literal>
+ </para><para><literal>jsonb_build_array(VARIADIC "any")</literal>
+ </para></entry>
+ <entry>
+ Builds a possibly-heterogeneously-typed JSON array out of a variadic
+ argument list.
+ </entry>
+ <entry><literal>json_build_array(1,2,'3',4,5)</literal></entry>
+ <entry><literal>[1, 2, "3", 4, 5]</literal></entry>
+ </row>
+ <row>
+ <entry><para><literal>json_build_object(VARIADIC "any")</literal>
+ </para><para><literal>jsonb_build_object(VARIADIC "any")</literal>
+ </para></entry>
+ <entry>
+ Builds a JSON object out of a variadic argument list. By
+ convention, the argument list consists of alternating
+ keys and values.
+ </entry>
+ <entry><literal>json_build_object('foo',1,'bar',2)</literal></entry>
+ <entry><literal>{"foo": 1, "bar": 2}</literal></entry>
+ </row>
+ <row>
+ <entry><para><literal>json_object(text[])</literal>
+ </para><para><literal>jsonb_object(text[])</literal>
+ </para></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 key/value pairs, or two dimensions
+ such that each inner array has exactly two elements, which
+ are taken as a key/value pair.
+ </entry>
+ <entry><para><literal>json_object('{a, 1, b, "def", c, 3.5}')</literal></para>
+ <para><literal>json_object('{{a, 1},{b, "def"},{c, 3.5}}')</literal></para></entry>
+ <entry><literal>{"a": "1", "b": "def", "c": "3.5"}</literal></entry>
+ </row>
+ <row>
+ <entry><para><literal>json_object(keys text[], values text[])</literal>
+ </para><para><literal>jsonb_object(keys text[], values text[])</literal>
+ </para></entry>
+ <entry>
+ This form of <function>json_object</function> takes keys and values pairwise from two separate
+ arrays. In all other respects it is identical to the one-argument form.
+ </entry>
+ <entry><literal>json_object('{a, b}', '{1,2}')</literal></entry>
+ <entry><literal>{"a": "1", "b": "2"}</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <note>
+ <para>
+ <function>array_to_json</function> and <function>row_to_json</function> have the same
+ behavior as <function>to_json</function> except for offering a pretty-printing
+ option. The behavior described for <function>to_json</function> likewise applies
+ to each individual value converted by the other JSON creation functions.
+ </para>
+ </note>
+
+ <note>
+ <para>
+ The <xref linkend="hstore"/> extension has a cast
+ from <type>hstore</type> to <type>json</type>, so that
+ <type>hstore</type> values converted via the JSON creation functions
+ will be represented as JSON objects, not as primitive string values.
+ </para>
+ </note>
+
+ <para>
+ <xref linkend="functions-json-processing-table"/> shows the functions that
+ are available for processing <type>json</type> and <type>jsonb</type> values.
+ </para>
+
+ <indexterm>
+ <primary>json_array_length</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_array_length</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_each</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_each</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_each_text</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_each_text</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_extract_path</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_extract_path</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_extract_path_text</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_extract_path_text</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_object_keys</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_object_keys</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_populate_record</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_populate_record</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_populate_recordset</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_populate_recordset</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_array_elements</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_array_elements</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_array_elements_text</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_array_elements_text</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_typeof</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_typeof</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_to_record</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_to_record</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_to_recordset</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_to_recordset</primary>
+ </indexterm>
+ <indexterm>
+ <primary>json_strip_nulls</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_strip_nulls</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_set</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_insert</primary>
+ </indexterm>
+ <indexterm>
+ <primary>jsonb_pretty</primary>
+ </indexterm>
+
+ <table id="functions-json-processing-table">
+ <title>JSON Processing Functions</title>
+ <tgroup cols="5">
+ <thead>
+ <row>
+ <entry>Function</entry>
+ <entry>Return Type</entry>
+ <entry>Description</entry>
+ <entry>Example</entry>
+ <entry>Example Result</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><para><literal>json_array_length(json)</literal>
+ </para><para><literal>jsonb_array_length(jsonb)</literal>
+ </para></entry>
+ <entry><type>int</type></entry>
+ <entry>
+ Returns the number of elements in the outermost JSON array.
+ </entry>
+ <entry><literal>json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')</literal></entry>
+ <entry><literal>5</literal></entry>
+ </row>
+ <row>
+ <entry><para><literal>json_each(json)</literal>
+ </para><para><literal>jsonb_each(jsonb)</literal>
+ </para></entry>
+ <entry><para><literal>setof key text, value json</literal>
+ </para><para><literal>setof key text, value jsonb</literal>
+ </para></entry>
+ <entry>
+ Expands the outermost JSON object into a set of key/value pairs.
+ </entry>
+ <entry><literal>select * from json_each('{"a":"foo", "b":"bar"}')</literal></entry>
+ <entry>
+<programlisting>
+ key | value
+-----+-------
+ a | "foo"
+ b | "bar"
+</programlisting>
+ </entry>
+ </row>
+ <row>
+ <entry><para><literal>json_each_text(json)</literal>
+ </para><para><literal>jsonb_each_text(jsonb)</literal>
+ </para></entry>
+ <entry><type>setof key text, value text</type></entry>
+ <entry>
+ Expands the outermost JSON object into a set of key/value pairs. The
+ returned values will be of type <type>text</type>.
+ </entry>
+ <entry><literal>select * from json_each_text('{"a":"foo", "b":"bar"}')</literal></entry>
+ <entry>
+<programlisting>
+ key | value
+-----+-------
+ a | foo
+ b | bar
+</programlisting>
+ </entry>
+ </row>
+ <row>
+ <entry><para><literal>json_extract_path(from_json json, VARIADIC path_elems text[])</literal>
+ </para><para><literal>jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])</literal>
+ </para></entry>
+ <entry><para><type>json</type></para><para><type>jsonb</type>
+ </para></entry>
+ <entry>
+ Returns JSON value pointed to by <replaceable>path_elems</replaceable>
+ (equivalent to <literal>#></literal> operator).
+ </entry>
+ <entry><literal>json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')</literal></entry>
+ <entry><literal>{"f5":99,"f6":"foo"}</literal></entry>
+ </row>
+ <row>
+ <entry><para><literal>json_extract_path_text(from_json json, VARIADIC path_elems text[])</literal>
+ </para><para><literal>jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])</literal>
+ </para></entry>
+ <entry><type>text</type></entry>
+ <entry>
+ Returns JSON value pointed to by <replaceable>path_elems</replaceable>
+ as <type>text</type>
+ (equivalent to <literal>#>></literal> operator).
+ </entry>
+ <entry><literal>json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')</literal></entry>
+ <entry><literal>foo</literal></entry>
+ </row>
+ <row>
+ <entry><para><literal>json_object_keys(json)</literal>
+ </para><para><literal>jsonb_object_keys(jsonb)</literal>
+ </para></entry>
+ <entry><type>setof text</type></entry>
+ <entry>
+ Returns set of keys in the outermost JSON object.
+ </entry>
+ <entry><literal>json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')</literal></entry>
+ <entry>
+<programlisting>
+ json_object_keys
+------------------
+ f1
+ f2
+</programlisting>
+ </entry>
+ </row>
+ <row>
+ <entry><para><literal>json_populate_record(base anyelement, from_json json)</literal>
+ </para><para><literal>jsonb_populate_record(base anyelement, from_json jsonb)</literal>
+ </para></entry>
+ <entry><type>anyelement</type></entry>
+ <entry>
+ Expands the object in <replaceable>from_json</replaceable> to a row
+ whose columns match the record type defined by <replaceable>base</replaceable>
+ (see note below).
+ </entry>
+ <entry><literal>select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}')</literal></entry>
+ <entry>
+<programlisting>
+ a | b | c
+---+-----------+-------------
+ 1 | {2,"a b"} | (4,"a b c")
+</programlisting>
+ </entry>
+ </row>
+ <row>
+ <entry><para><literal>json_populate_recordset(base anyelement, from_json json)</literal>
+ </para><para><literal>jsonb_populate_recordset(base anyelement, from_json jsonb)</literal>
+ </para></entry>
+ <entry><type>setof anyelement</type></entry>
+ <entry>
+ Expands the outermost array of objects
+ in <replaceable>from_json</replaceable> to a set of rows whose
+ columns match the record type defined by <replaceable>base</replaceable> (see
+ note below).
+ </entry>
+ <entry><literal>select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')</literal></entry>
+ <entry>
+<programlisting>
+ a | b
+---+---
+ 1 | 2
+ 3 | 4
+</programlisting>
+ </entry>
+ </row>
+ <row>
+ <entry><para><literal>json_array_elements(json)</literal>
+ </para><para><literal>jsonb_array_elements(jsonb)</literal>
+ </para></entry>
+ <entry><para><type>setof json</type>
+ </para><para><type>setof jsonb</type>
+ </para></entry>
+ <entry>
+ Expands a JSON array to a set of JSON values.
+ </entry>
+ <entry><literal>select * from json_array_elements('[1,true, [2,false]]')</literal></entry>
+ <entry>
+<programlisting>
+ value
+-----------
+ 1
+ true
+ [2,false]
+</programlisting>
+ </entry>
+ </row>
+ <row>
+ <entry><para><literal>json_array_elements_text(json)</literal>
+ </para><para><literal>jsonb_array_elements_text(jsonb)</literal>
+ </para></entry>
+ <entry><type>setof text</type></entry>
+ <entry>
+ Expands a JSON array to a set of <type>text</type> values.
+ </entry>
+ <entry><literal>select * from json_array_elements_text('["foo", "bar"]')</literal></entry>
+ <entry>
+<programlisting>
+ value
+-----------
+ foo
+ bar
+</programlisting>
+ </entry>
+ </row>
+ <row>
+ <entry><para><literal>json_typeof(json)</literal>
+ </para><para><literal>jsonb_typeof(jsonb)</literal>
+ </para></entry>
+ <entry><type>text</type></entry>
+ <entry>
+ Returns the type of the outermost JSON value as a text string.
+ Possible types are
+ <literal>object</literal>, <literal>array</literal>, <literal>string</literal>, <literal>number</literal>,
+ <literal>boolean</literal>, and <literal>null</literal>.
+ </entry>
+ <entry><literal>json_typeof('-123.4')</literal></entry>
+ <entry><literal>number</literal></entry>
+ </row>
+ <row>
+ <entry><para><literal>json_to_record(json)</literal>
+ </para><para><literal>jsonb_to_record(jsonb)</literal>
+ </para></entry>
+ <entry><type>record</type></entry>
+ <entry>
+ Builds an arbitrary record from a JSON object (see note below). As
+ with all functions returning <type>record</type>, the caller must
+ explicitly define the structure of the record with an <literal>AS</literal>
+ clause.
+ </entry>
+ <entry><literal>select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype) </literal></entry>
+ <entry>
+<programlisting>
+ a | b | c | d | r
+---+---------+---------+---+---------------
+ 1 | [1,2,3] | {1,2,3} | | (123,"a b c")
+</programlisting>
+ </entry>
+ </row>
+ <row>
+ <entry><para><literal>json_to_recordset(json)</literal>
+ </para><para><literal>jsonb_to_recordset(jsonb)</literal>
+ </para></entry>
+ <entry><type>setof record</type></entry>
+ <entry>
+ Builds an arbitrary set of records from a JSON array of objects (see
+ note below). As with all functions returning <type>record</type>, the
+ caller must explicitly define the structure of the record with
+ an <literal>AS</literal> clause.
+ </entry>
+ <entry><literal>select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);</literal></entry>
+ <entry>
+<programlisting>
+ a | b
+---+-----
+ 1 | foo
+ 2 |
+</programlisting>
+ </entry>
+ </row>
+ <row>
+ <entry><para><literal>json_strip_nulls(from_json json)</literal>
+ </para><para><literal>jsonb_strip_nulls(from_json jsonb)</literal>
+ </para></entry>
+ <entry><para><type>json</type></para><para><type>jsonb</type></para></entry>
+ <entry>
+ Returns <replaceable>from_json</replaceable>
+ with all object fields that have null values omitted. Other null values
+ are untouched.
+ </entry>
+ <entry><literal>json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')</literal></entry>
+ <entry><literal>[{"f1":1},2,null,3]</literal></entry>
+ </row>
+ <row>
+ <entry><para><literal>jsonb_set(target jsonb, path text[], new_value jsonb<optional>, <parameter>create_missing</parameter> <type>boolean</type></optional>)</literal>
+ </para></entry>
+ <entry><para><type>jsonb</type></para></entry>
+ <entry>
+ Returns <replaceable>target</replaceable>
+ with the section designated by <replaceable>path</replaceable>
+ replaced by <replaceable>new_value</replaceable>, or with
+ <replaceable>new_value</replaceable> added if
+ <replaceable>create_missing</replaceable> is true ( default is
+ <literal>true</literal>) and the item
+ designated by <replaceable>path</replaceable> does not exist.
+ As with the path orientated operators, negative integers that
+ appear in <replaceable>path</replaceable> count from the end
+ of JSON arrays.
+ </entry>
+ <entry><para><literal>jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)</literal>
+ </para><para><literal>jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]')</literal>
+ </para></entry>
+ <entry><para><literal>[{"f1":[2,3,4],"f2":null},2,null,3]</literal>
+ </para><para><literal>[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]</literal>
+ </para></entry>
+ </row>
+ <row>
+ <entry>
+ <para><literal>
+ jsonb_insert(target jsonb, path text[], new_value jsonb, <optional><parameter>insert_after</parameter> <type>boolean</type></optional>)
+ </literal></para>
+ </entry>
+ <entry><para><type>jsonb</type></para></entry>
+ <entry>
+ Returns <replaceable>target</replaceable> with
+ <replaceable>new_value</replaceable> inserted. If
+ <replaceable>target</replaceable> section designated by
+ <replaceable>path</replaceable> is in a JSONB array,
+ <replaceable>new_value</replaceable> will be inserted before target or
+ after if <replaceable>insert_after</replaceable> is true (default is
+ <literal>false</literal>). If <replaceable>target</replaceable> section
+ designated by <replaceable>path</replaceable> is in JSONB object,
+ <replaceable>new_value</replaceable> will be inserted only if
+ <replaceable>target</replaceable> does not exist. As with the path
+ orientated operators, negative integers that appear in
+ <replaceable>path</replaceable> count from the end of JSON arrays.
+ </entry>
+ <entry>
+ <para><literal>
+ jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"')
+ </literal></para>
+ <para><literal>
+ jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true)
+ </literal></para>
+ </entry>
+ <entry><para><literal>{"a": [0, "new_value", 1, 2]}</literal>
+ </para><para><literal>{"a": [0, 1, "new_value", 2]}</literal>
+ </para></entry>
+ </row>
+ <row>
+ <entry><para><literal>jsonb_pretty(from_json jsonb)</literal>
+ </para></entry>
+ <entry><para><type>text</type></para></entry>
+ <entry>
+ Returns <replaceable>from_json</replaceable>
+ as indented JSON text.
+ </entry>
+ <entry><literal>jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')</literal></entry>
+ <entry>
+<programlisting>
+[
+ {
+ "f1": 1,
+ "f2": null
+ },
+ 2,
+ null,
+ 3
+]
+</programlisting>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <note>
+ <para>
+ Many of these functions and operators will convert Unicode escapes in
+ JSON strings to the appropriate single character. This is a non-issue
+ if the input is type <type>jsonb</type>, because the conversion was already
+ done; but for <type>json</type> input, this may result in throwing an error,
+ as noted in <xref linkend="datatype-json"/>.
+ </para>
+ </note>
+
+ <note>
+ <para>
+ While the examples for the functions
+ <function>json_populate_record</function>,
+ <function>json_populate_recordset</function>,
+ <function>json_to_record</function> and
+ <function>json_to_recordset</function> use constants, the typical use
+ would be to reference a table in the <literal>FROM</literal> clause
+ and use one of its <type>json</type> or <type>jsonb</type> columns
+ as an argument to the function. Extracted key values can then be
+ referenced in other parts of the query, like <literal>WHERE</literal>
+ clauses and target lists. Extracting multiple values in this
+ way can improve performance over extracting them separately with
+ per-key operators.
+ </para>
+
+ <para>
+ JSON keys are matched to identical column names in the target
+ row type. JSON type coercion for these functions is <quote>best
+ effort</quote> and may not result in desired values for some types.
+ JSON fields that do not appear in the target row type will be
+ omitted from the output, and target columns that do not match any
+ JSON field will simply be NULL.
+ </para>
+ </note>
+
+ <note>
+ <para>
+ All the items of the <literal>path</literal> parameter of <literal>jsonb_set</literal>
+ as well as <literal>jsonb_insert</literal> except the last item must be present
+ in the <literal>target</literal>. If <literal>create_missing</literal> is false, all
+ items of the <literal>path</literal> parameter of <literal>jsonb_set</literal> must be
+ present. If these conditions are not met the <literal>target</literal> is
+ returned unchanged.
+ </para>
+ <para>
+ If the last path item is an object key, it will be created if it
+ is absent and given the new value. If the last path item is an array
+ index, if it is positive the item to set is found by counting from
+ the left, and if negative by counting from the right - <literal>-1</literal>
+ designates the rightmost element, and so on.
+ If the item is out of the range -array_length .. array_length -1,
+ and create_missing is true, the new value is added at the beginning
+ of the array if the item is negative, and at the end of the array if
+ it is positive.
+ </para>
+ </note>
+
+ <note>
+ <para>
+ The <literal>json_typeof</literal> function's <literal>null</literal> return value
+ should not be confused with a SQL NULL. While
+ calling <literal>json_typeof('null'::json)</literal> will
+ return <literal>null</literal>, calling <literal>json_typeof(NULL::json)</literal>
+ will return a SQL NULL.
+ </para>
+ </note>
+
+ <note>
+ <para>
+ If the argument to <literal>json_strip_nulls</literal> contains duplicate
+ field names in any object, the result could be semantically somewhat
+ different, depending on the order in which they occur. This is not an
+ issue for <literal>jsonb_strip_nulls</literal> since <type>jsonb</type> values never have
+ duplicate object field names.
+ </para>
+ </note>
+
+ <para>
+ See also <xref linkend="functions-aggregate"/> for the aggregate
+ function <function>json_agg</function> which aggregates record
+ values as JSON, and the aggregate function
+ <function>json_object_agg</function> which aggregates pairs of values
+ into a JSON object, and their <type>jsonb</type> equivalents,
+ <function>jsonb_agg</function> and <function>jsonb_object_agg</function>.
+ </para>
+
+ </sect2>
+
+</sect1>
+
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5dce8ef..414dc22 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -11183,896 +11183,7 @@ table2-mapping
</sect2>
</sect1>
- <sect1 id="functions-json">
- <title>JSON Functions and Operators</title>
-
- <indexterm zone="functions-json">
- <primary>JSON</primary>
- <secondary>functions and operators</secondary>
- </indexterm>
-
- <para>
- <xref linkend="functions-json-op-table"/> shows the operators that
- are available for use with the two JSON data types (see <xref
- linkend="datatype-json"/>).
- </para>
-
- <table id="functions-json-op-table">
- <title><type>json</type> and <type>jsonb</type> Operators</title>
- <tgroup cols="5">
- <thead>
- <row>
- <entry>Operator</entry>
- <entry>Right Operand Type</entry>
- <entry>Description</entry>
- <entry>Example</entry>
- <entry>Example Result</entry>
- </row>
- </thead>
- <tbody>
- <row>
- <entry><literal>-></literal></entry>
- <entry><type>int</type></entry>
- <entry>Get JSON array element (indexed from zero, negative
- integers count from the end)</entry>
- <entry><literal>'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2</literal></entry>
- <entry><literal>{"c":"baz"}</literal></entry>
- </row>
- <row>
- <entry><literal>-></literal></entry>
- <entry><type>text</type></entry>
- <entry>Get JSON object field by key</entry>
- <entry><literal>'{"a": {"b":"foo"}}'::json->'a'</literal></entry>
- <entry><literal>{"b":"foo"}</literal></entry>
- </row>
- <row>
- <entry><literal>->></literal></entry>
- <entry><type>int</type></entry>
- <entry>Get JSON array element as <type>text</type></entry>
- <entry><literal>'[1,2,3]'::json->>2</literal></entry>
- <entry><literal>3</literal></entry>
- </row>
- <row>
- <entry><literal>->></literal></entry>
- <entry><type>text</type></entry>
- <entry>Get JSON object field as <type>text</type></entry>
- <entry><literal>'{"a":1,"b":2}'::json->>'b'</literal></entry>
- <entry><literal>2</literal></entry>
- </row>
- <row>
- <entry><literal>#></literal></entry>
- <entry><type>text[]</type></entry>
- <entry>Get JSON object at specified path</entry>
- <entry><literal>'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'</literal></entry>
- <entry><literal>{"c": "foo"}</literal></entry>
- </row>
- <row>
- <entry><literal>#>></literal></entry>
- <entry><type>text[]</type></entry>
- <entry>Get JSON object at specified path as <type>text</type></entry>
- <entry><literal>'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'</literal></entry>
- <entry><literal>3</literal></entry>
- </row>
- </tbody>
- </tgroup>
- </table>
-
- <note>
- <para>
- There are parallel variants of these operators for both the
- <type>json</type> and <type>jsonb</type> types.
- The field/element/path extraction operators
- return the same type as their left-hand input (either <type>json</type>
- or <type>jsonb</type>), except for those specified as
- returning <type>text</type>, which coerce the value to text.
- The field/element/path extraction operators return NULL, rather than
- failing, if the JSON input does not have the right structure to match
- the request; for example if no such element exists. The
- field/element/path extraction operators that accept integer JSON
- array subscripts all support negative subscripting from the end of
- arrays.
- </para>
- </note>
- <para>
- The standard comparison operators shown in <xref
- linkend="functions-comparison-op-table"/> are available for
- <type>jsonb</type>, but not for <type>json</type>. They follow the
- ordering rules for B-tree operations outlined at <xref
- linkend="json-indexing"/>.
- </para>
- <para>
- Some further operators also exist only for <type>jsonb</type>, as shown
- in <xref linkend="functions-jsonb-op-table"/>.
- Many of these operators can be indexed by
- <type>jsonb</type> operator classes. For a full description of
- <type>jsonb</type> containment and existence semantics, see <xref
- linkend="json-containment"/>. <xref linkend="json-indexing"/>
- describes how these operators can be used to effectively index
- <type>jsonb</type> data.
- </para>
- <table id="functions-jsonb-op-table">
- <title>Additional <type>jsonb</type> Operators</title>
- <tgroup cols="4">
- <thead>
- <row>
- <entry>Operator</entry>
- <entry>Right Operand Type</entry>
- <entry>Description</entry>
- <entry>Example</entry>
- </row>
- </thead>
- <tbody>
- <row>
- <entry><literal>@></literal></entry>
- <entry><type>jsonb</type></entry>
- <entry>Does the left JSON value contain the right JSON
- path/value entries at the top level?</entry>
- <entry><literal>'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb</literal></entry>
- </row>
- <row>
- <entry><literal><@</literal></entry>
- <entry><type>jsonb</type></entry>
- <entry>Are the left JSON path/value entries contained at the top level within
- the right JSON value?</entry>
- <entry><literal>'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb</literal></entry>
- </row>
- <row>
- <entry><literal>?</literal></entry>
- <entry><type>text</type></entry>
- <entry>Does the <emphasis>string</emphasis> exist as a top-level
- key within the JSON value?</entry>
- <entry><literal>'{"a":1, "b":2}'::jsonb ? 'b'</literal></entry>
- </row>
- <row>
- <entry><literal>?|</literal></entry>
- <entry><type>text[]</type></entry>
- <entry>Do any of these array <emphasis>strings</emphasis>
- exist as top-level keys?</entry>
- <entry><literal>'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']</literal></entry>
- </row>
- <row>
- <entry><literal>?&</literal></entry>
- <entry><type>text[]</type></entry>
- <entry>Do all of these array <emphasis>strings</emphasis> exist
- as top-level keys?</entry>
- <entry><literal>'["a", "b"]'::jsonb ?& array['a', 'b']</literal></entry>
- </row>
- <row>
- <entry><literal>||</literal></entry>
- <entry><type>jsonb</type></entry>
- <entry>Concatenate two <type>jsonb</type> values into a new <type>jsonb</type> value</entry>
- <entry><literal>'["a", "b"]'::jsonb || '["c", "d"]'::jsonb</literal></entry>
- </row>
- <row>
- <entry><literal>-</literal></entry>
- <entry><type>text</type></entry>
- <entry>Delete key/value pair or <emphasis>string</emphasis>
- element from left operand. Key/value pairs are matched based
- on their key value.</entry>
- <entry><literal>'{"a": "b"}'::jsonb - 'a' </literal></entry>
- </row>
- <row>
- <entry><literal>-</literal></entry>
- <entry><type>text[]</type></entry>
- <entry>Delete multiple key/value pairs or <emphasis>string</emphasis>
- elements from left operand. Key/value pairs are matched based
- on their key value.</entry>
- <entry><literal>'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] </literal></entry>
- </row>
- <row>
- <entry><literal>-</literal></entry>
- <entry><type>integer</type></entry>
- <entry>Delete the array element with specified index (Negative
- integers count from the end). Throws an error if top level
- container is not an array.</entry>
- <entry><literal>'["a", "b"]'::jsonb - 1 </literal></entry>
- </row>
- <row>
- <entry><literal>#-</literal></entry>
- <entry><type>text[]</type></entry>
- <entry>Delete the field or element with specified path (for
- JSON arrays, negative integers count from the end)</entry>
- <entry><literal>'["a", {"b":1}]'::jsonb #- '{1,b}'</literal></entry>
- </row>
- </tbody>
- </tgroup>
- </table>
-
- <note>
- <para>
- The <literal>||</literal> operator concatenates the elements at the top level of
- each of its operands. It does not operate recursively. For example, if
- both operands are objects with a common key field name, the value of the
- field in the result will just be the value from the right hand operand.
- </para>
- </note>
-
- <para>
- <xref linkend="functions-json-creation-table"/> shows the functions that are
- available for creating <type>json</type> and <type>jsonb</type> values.
- (There are no equivalent functions for <type>jsonb</type>, of the <literal>row_to_json</literal>
- and <literal>array_to_json</literal> functions. However, the <literal>to_jsonb</literal>
- function supplies much the same functionality as these functions would.)
- </para>
-
- <indexterm>
- <primary>to_json</primary>
- </indexterm>
- <indexterm>
- <primary>array_to_json</primary>
- </indexterm>
- <indexterm>
- <primary>row_to_json</primary>
- </indexterm>
- <indexterm>
- <primary>json_build_array</primary>
- </indexterm>
- <indexterm>
- <primary>json_build_object</primary>
- </indexterm>
- <indexterm>
- <primary>json_object</primary>
- </indexterm>
- <indexterm>
- <primary>to_jsonb</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_build_array</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_build_object</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_object</primary>
- </indexterm>
-
- <table id="functions-json-creation-table">
- <title>JSON Creation Functions</title>
- <tgroup cols="4">
- <thead>
- <row>
- <entry>Function</entry>
- <entry>Description</entry>
- <entry>Example</entry>
- <entry>Example Result</entry>
- </row>
- </thead>
- <tbody>
- <row>
- <entry><para><literal>to_json(anyelement)</literal>
- </para><para><literal>to_jsonb(anyelement)</literal>
- </para></entry>
- <entry>
- Returns the value as <type>json</type> or <type>jsonb</type>.
- Arrays and composites are converted
- (recursively) to arrays and objects; otherwise, if there is a cast
- from the type to <type>json</type>, the cast function will be used to
- perform the conversion; otherwise, a scalar value is produced.
- For any scalar type other than a number, a Boolean, or a null value,
- the text representation will be used, in such a fashion that it is a
- valid <type>json</type> or <type>jsonb</type> value.
- </entry>
- <entry><literal>to_json('Fred said "Hi."'::text)</literal></entry>
- <entry><literal>"Fred said \"Hi.\""</literal></entry>
- </row>
- <row>
- <entry>
- <literal>array_to_json(anyarray [, pretty_bool])</literal>
- </entry>
- <entry>
- Returns the array as a JSON array. A PostgreSQL multidimensional array
- becomes a JSON array of arrays. Line feeds will be added between
- dimension-1 elements if <parameter>pretty_bool</parameter> is true.
- </entry>
- <entry><literal>array_to_json('{{1,5},{99,100}}'::int[])</literal></entry>
- <entry><literal>[[1,5],[99,100]]</literal></entry>
- </row>
- <row>
- <entry>
- <literal>row_to_json(record [, pretty_bool])</literal>
- </entry>
- <entry>
- Returns the row as a JSON object. Line feeds will be added between
- level-1 elements if <parameter>pretty_bool</parameter> is true.
- </entry>
- <entry><literal>row_to_json(row(1,'foo'))</literal></entry>
- <entry><literal>{"f1":1,"f2":"foo"}</literal></entry>
- </row>
- <row>
- <entry><para><literal>json_build_array(VARIADIC "any")</literal>
- </para><para><literal>jsonb_build_array(VARIADIC "any")</literal>
- </para></entry>
- <entry>
- Builds a possibly-heterogeneously-typed JSON array out of a variadic
- argument list.
- </entry>
- <entry><literal>json_build_array(1,2,'3',4,5)</literal></entry>
- <entry><literal>[1, 2, "3", 4, 5]</literal></entry>
- </row>
- <row>
- <entry><para><literal>json_build_object(VARIADIC "any")</literal>
- </para><para><literal>jsonb_build_object(VARIADIC "any")</literal>
- </para></entry>
- <entry>
- Builds a JSON object out of a variadic argument list. By
- convention, the argument list consists of alternating
- keys and values.
- </entry>
- <entry><literal>json_build_object('foo',1,'bar',2)</literal></entry>
- <entry><literal>{"foo": 1, "bar": 2}</literal></entry>
- </row>
- <row>
- <entry><para><literal>json_object(text[])</literal>
- </para><para><literal>jsonb_object(text[])</literal>
- </para></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 key/value pairs, or two dimensions
- such that each inner array has exactly two elements, which
- are taken as a key/value pair.
- </entry>
- <entry><para><literal>json_object('{a, 1, b, "def", c, 3.5}')</literal></para>
- <para><literal>json_object('{{a, 1},{b, "def"},{c, 3.5}}')</literal></para></entry>
- <entry><literal>{"a": "1", "b": "def", "c": "3.5"}</literal></entry>
- </row>
- <row>
- <entry><para><literal>json_object(keys text[], values text[])</literal>
- </para><para><literal>jsonb_object(keys text[], values text[])</literal>
- </para></entry>
- <entry>
- This form of <function>json_object</function> takes keys and values pairwise from two separate
- arrays. In all other respects it is identical to the one-argument form.
- </entry>
- <entry><literal>json_object('{a, b}', '{1,2}')</literal></entry>
- <entry><literal>{"a": "1", "b": "2"}</literal></entry>
- </row>
- </tbody>
- </tgroup>
- </table>
-
- <note>
- <para>
- <function>array_to_json</function> and <function>row_to_json</function> have the same
- behavior as <function>to_json</function> except for offering a pretty-printing
- option. The behavior described for <function>to_json</function> likewise applies
- to each individual value converted by the other JSON creation functions.
- </para>
- </note>
-
- <note>
- <para>
- The <xref linkend="hstore"/> extension has a cast
- from <type>hstore</type> to <type>json</type>, so that
- <type>hstore</type> values converted via the JSON creation functions
- will be represented as JSON objects, not as primitive string values.
- </para>
- </note>
-
- <para>
- <xref linkend="functions-json-processing-table"/> shows the functions that
- are available for processing <type>json</type> and <type>jsonb</type> values.
- </para>
-
- <indexterm>
- <primary>json_array_length</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_array_length</primary>
- </indexterm>
- <indexterm>
- <primary>json_each</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_each</primary>
- </indexterm>
- <indexterm>
- <primary>json_each_text</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_each_text</primary>
- </indexterm>
- <indexterm>
- <primary>json_extract_path</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_extract_path</primary>
- </indexterm>
- <indexterm>
- <primary>json_extract_path_text</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_extract_path_text</primary>
- </indexterm>
- <indexterm>
- <primary>json_object_keys</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_object_keys</primary>
- </indexterm>
- <indexterm>
- <primary>json_populate_record</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_populate_record</primary>
- </indexterm>
- <indexterm>
- <primary>json_populate_recordset</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_populate_recordset</primary>
- </indexterm>
- <indexterm>
- <primary>json_array_elements</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_array_elements</primary>
- </indexterm>
- <indexterm>
- <primary>json_array_elements_text</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_array_elements_text</primary>
- </indexterm>
- <indexterm>
- <primary>json_typeof</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_typeof</primary>
- </indexterm>
- <indexterm>
- <primary>json_to_record</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_to_record</primary>
- </indexterm>
- <indexterm>
- <primary>json_to_recordset</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_to_recordset</primary>
- </indexterm>
- <indexterm>
- <primary>json_strip_nulls</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_strip_nulls</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_set</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_insert</primary>
- </indexterm>
- <indexterm>
- <primary>jsonb_pretty</primary>
- </indexterm>
-
- <table id="functions-json-processing-table">
- <title>JSON Processing Functions</title>
- <tgroup cols="5">
- <thead>
- <row>
- <entry>Function</entry>
- <entry>Return Type</entry>
- <entry>Description</entry>
- <entry>Example</entry>
- <entry>Example Result</entry>
- </row>
- </thead>
- <tbody>
- <row>
- <entry><para><literal>json_array_length(json)</literal>
- </para><para><literal>jsonb_array_length(jsonb)</literal>
- </para></entry>
- <entry><type>int</type></entry>
- <entry>
- Returns the number of elements in the outermost JSON array.
- </entry>
- <entry><literal>json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')</literal></entry>
- <entry><literal>5</literal></entry>
- </row>
- <row>
- <entry><para><literal>json_each(json)</literal>
- </para><para><literal>jsonb_each(jsonb)</literal>
- </para></entry>
- <entry><para><literal>setof key text, value json</literal>
- </para><para><literal>setof key text, value jsonb</literal>
- </para></entry>
- <entry>
- Expands the outermost JSON object into a set of key/value pairs.
- </entry>
- <entry><literal>select * from json_each('{"a":"foo", "b":"bar"}')</literal></entry>
- <entry>
-<programlisting>
- key | value
------+-------
- a | "foo"
- b | "bar"
-</programlisting>
- </entry>
- </row>
- <row>
- <entry><para><literal>json_each_text(json)</literal>
- </para><para><literal>jsonb_each_text(jsonb)</literal>
- </para></entry>
- <entry><type>setof key text, value text</type></entry>
- <entry>
- Expands the outermost JSON object into a set of key/value pairs. The
- returned values will be of type <type>text</type>.
- </entry>
- <entry><literal>select * from json_each_text('{"a":"foo", "b":"bar"}')</literal></entry>
- <entry>
-<programlisting>
- key | value
------+-------
- a | foo
- b | bar
-</programlisting>
- </entry>
- </row>
- <row>
- <entry><para><literal>json_extract_path(from_json json, VARIADIC path_elems text[])</literal>
- </para><para><literal>jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])</literal>
- </para></entry>
- <entry><para><type>json</type></para><para><type>jsonb</type>
- </para></entry>
- <entry>
- Returns JSON value pointed to by <replaceable>path_elems</replaceable>
- (equivalent to <literal>#></literal> operator).
- </entry>
- <entry><literal>json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')</literal></entry>
- <entry><literal>{"f5":99,"f6":"foo"}</literal></entry>
- </row>
- <row>
- <entry><para><literal>json_extract_path_text(from_json json, VARIADIC path_elems text[])</literal>
- </para><para><literal>jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])</literal>
- </para></entry>
- <entry><type>text</type></entry>
- <entry>
- Returns JSON value pointed to by <replaceable>path_elems</replaceable>
- as <type>text</type>
- (equivalent to <literal>#>></literal> operator).
- </entry>
- <entry><literal>json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')</literal></entry>
- <entry><literal>foo</literal></entry>
- </row>
- <row>
- <entry><para><literal>json_object_keys(json)</literal>
- </para><para><literal>jsonb_object_keys(jsonb)</literal>
- </para></entry>
- <entry><type>setof text</type></entry>
- <entry>
- Returns set of keys in the outermost JSON object.
- </entry>
- <entry><literal>json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')</literal></entry>
- <entry>
-<programlisting>
- json_object_keys
-------------------
- f1
- f2
-</programlisting>
- </entry>
- </row>
- <row>
- <entry><para><literal>json_populate_record(base anyelement, from_json json)</literal>
- </para><para><literal>jsonb_populate_record(base anyelement, from_json jsonb)</literal>
- </para></entry>
- <entry><type>anyelement</type></entry>
- <entry>
- Expands the object in <replaceable>from_json</replaceable> to a row
- whose columns match the record type defined by <replaceable>base</replaceable>
- (see note below).
- </entry>
- <entry><literal>select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}')</literal></entry>
- <entry>
-<programlisting>
- a | b | c
----+-----------+-------------
- 1 | {2,"a b"} | (4,"a b c")
-</programlisting>
- </entry>
- </row>
- <row>
- <entry><para><literal>json_populate_recordset(base anyelement, from_json json)</literal>
- </para><para><literal>jsonb_populate_recordset(base anyelement, from_json jsonb)</literal>
- </para></entry>
- <entry><type>setof anyelement</type></entry>
- <entry>
- Expands the outermost array of objects
- in <replaceable>from_json</replaceable> to a set of rows whose
- columns match the record type defined by <replaceable>base</replaceable> (see
- note below).
- </entry>
- <entry><literal>select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')</literal></entry>
- <entry>
-<programlisting>
- a | b
----+---
- 1 | 2
- 3 | 4
-</programlisting>
- </entry>
- </row>
- <row>
- <entry><para><literal>json_array_elements(json)</literal>
- </para><para><literal>jsonb_array_elements(jsonb)</literal>
- </para></entry>
- <entry><para><type>setof json</type>
- </para><para><type>setof jsonb</type>
- </para></entry>
- <entry>
- Expands a JSON array to a set of JSON values.
- </entry>
- <entry><literal>select * from json_array_elements('[1,true, [2,false]]')</literal></entry>
- <entry>
-<programlisting>
- value
------------
- 1
- true
- [2,false]
-</programlisting>
- </entry>
- </row>
- <row>
- <entry><para><literal>json_array_elements_text(json)</literal>
- </para><para><literal>jsonb_array_elements_text(jsonb)</literal>
- </para></entry>
- <entry><type>setof text</type></entry>
- <entry>
- Expands a JSON array to a set of <type>text</type> values.
- </entry>
- <entry><literal>select * from json_array_elements_text('["foo", "bar"]')</literal></entry>
- <entry>
-<programlisting>
- value
------------
- foo
- bar
-</programlisting>
- </entry>
- </row>
- <row>
- <entry><para><literal>json_typeof(json)</literal>
- </para><para><literal>jsonb_typeof(jsonb)</literal>
- </para></entry>
- <entry><type>text</type></entry>
- <entry>
- Returns the type of the outermost JSON value as a text string.
- Possible types are
- <literal>object</literal>, <literal>array</literal>, <literal>string</literal>, <literal>number</literal>,
- <literal>boolean</literal>, and <literal>null</literal>.
- </entry>
- <entry><literal>json_typeof('-123.4')</literal></entry>
- <entry><literal>number</literal></entry>
- </row>
- <row>
- <entry><para><literal>json_to_record(json)</literal>
- </para><para><literal>jsonb_to_record(jsonb)</literal>
- </para></entry>
- <entry><type>record</type></entry>
- <entry>
- Builds an arbitrary record from a JSON object (see note below). As
- with all functions returning <type>record</type>, the caller must
- explicitly define the structure of the record with an <literal>AS</literal>
- clause.
- </entry>
- <entry><literal>select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype) </literal></entry>
- <entry>
-<programlisting>
- a | b | c | d | r
----+---------+---------+---+---------------
- 1 | [1,2,3] | {1,2,3} | | (123,"a b c")
-</programlisting>
- </entry>
- </row>
- <row>
- <entry><para><literal>json_to_recordset(json)</literal>
- </para><para><literal>jsonb_to_recordset(jsonb)</literal>
- </para></entry>
- <entry><type>setof record</type></entry>
- <entry>
- Builds an arbitrary set of records from a JSON array of objects (see
- note below). As with all functions returning <type>record</type>, the
- caller must explicitly define the structure of the record with
- an <literal>AS</literal> clause.
- </entry>
- <entry><literal>select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);</literal></entry>
- <entry>
-<programlisting>
- a | b
----+-----
- 1 | foo
- 2 |
-</programlisting>
- </entry>
- </row>
- <row>
- <entry><para><literal>json_strip_nulls(from_json json)</literal>
- </para><para><literal>jsonb_strip_nulls(from_json jsonb)</literal>
- </para></entry>
- <entry><para><type>json</type></para><para><type>jsonb</type></para></entry>
- <entry>
- Returns <replaceable>from_json</replaceable>
- with all object fields that have null values omitted. Other null values
- are untouched.
- </entry>
- <entry><literal>json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')</literal></entry>
- <entry><literal>[{"f1":1},2,null,3]</literal></entry>
- </row>
- <row>
- <entry><para><literal>jsonb_set(target jsonb, path text[], new_value jsonb<optional>, <parameter>create_missing</parameter> <type>boolean</type></optional>)</literal>
- </para></entry>
- <entry><para><type>jsonb</type></para></entry>
- <entry>
- Returns <replaceable>target</replaceable>
- with the section designated by <replaceable>path</replaceable>
- replaced by <replaceable>new_value</replaceable>, or with
- <replaceable>new_value</replaceable> added if
- <replaceable>create_missing</replaceable> is true ( default is
- <literal>true</literal>) and the item
- designated by <replaceable>path</replaceable> does not exist.
- As with the path orientated operators, negative integers that
- appear in <replaceable>path</replaceable> count from the end
- of JSON arrays.
- </entry>
- <entry><para><literal>jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)</literal>
- </para><para><literal>jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]')</literal>
- </para></entry>
- <entry><para><literal>[{"f1":[2,3,4],"f2":null},2,null,3]</literal>
- </para><para><literal>[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]</literal>
- </para></entry>
- </row>
- <row>
- <entry>
- <para><literal>
- jsonb_insert(target jsonb, path text[], new_value jsonb, <optional><parameter>insert_after</parameter> <type>boolean</type></optional>)
- </literal></para>
- </entry>
- <entry><para><type>jsonb</type></para></entry>
- <entry>
- Returns <replaceable>target</replaceable> with
- <replaceable>new_value</replaceable> inserted. If
- <replaceable>target</replaceable> section designated by
- <replaceable>path</replaceable> is in a JSONB array,
- <replaceable>new_value</replaceable> will be inserted before target or
- after if <replaceable>insert_after</replaceable> is true (default is
- <literal>false</literal>). If <replaceable>target</replaceable> section
- designated by <replaceable>path</replaceable> is in JSONB object,
- <replaceable>new_value</replaceable> will be inserted only if
- <replaceable>target</replaceable> does not exist. As with the path
- orientated operators, negative integers that appear in
- <replaceable>path</replaceable> count from the end of JSON arrays.
- </entry>
- <entry>
- <para><literal>
- jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"')
- </literal></para>
- <para><literal>
- jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true)
- </literal></para>
- </entry>
- <entry><para><literal>{"a": [0, "new_value", 1, 2]}</literal>
- </para><para><literal>{"a": [0, 1, "new_value", 2]}</literal>
- </para></entry>
- </row>
- <row>
- <entry><para><literal>jsonb_pretty(from_json jsonb)</literal>
- </para></entry>
- <entry><para><type>text</type></para></entry>
- <entry>
- Returns <replaceable>from_json</replaceable>
- as indented JSON text.
- </entry>
- <entry><literal>jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')</literal></entry>
- <entry>
-<programlisting>
-[
- {
- "f1": 1,
- "f2": null
- },
- 2,
- null,
- 3
-]
-</programlisting>
- </entry>
- </row>
- </tbody>
- </tgroup>
- </table>
-
- <note>
- <para>
- Many of these functions and operators will convert Unicode escapes in
- JSON strings to the appropriate single character. This is a non-issue
- if the input is type <type>jsonb</type>, because the conversion was already
- done; but for <type>json</type> input, this may result in throwing an error,
- as noted in <xref linkend="datatype-json"/>.
- </para>
- </note>
-
- <note>
- <para>
- While the examples for the functions
- <function>json_populate_record</function>,
- <function>json_populate_recordset</function>,
- <function>json_to_record</function> and
- <function>json_to_recordset</function> use constants, the typical use
- would be to reference a table in the <literal>FROM</literal> clause
- and use one of its <type>json</type> or <type>jsonb</type> columns
- as an argument to the function. Extracted key values can then be
- referenced in other parts of the query, like <literal>WHERE</literal>
- clauses and target lists. Extracting multiple values in this
- way can improve performance over extracting them separately with
- per-key operators.
- </para>
-
- <para>
- JSON keys are matched to identical column names in the target
- row type. JSON type coercion for these functions is <quote>best
- effort</quote> and may not result in desired values for some types.
- JSON fields that do not appear in the target row type will be
- omitted from the output, and target columns that do not match any
- JSON field will simply be NULL.
- </para>
- </note>
-
- <note>
- <para>
- All the items of the <literal>path</literal> parameter of <literal>jsonb_set</literal>
- as well as <literal>jsonb_insert</literal> except the last item must be present
- in the <literal>target</literal>. If <literal>create_missing</literal> is false, all
- items of the <literal>path</literal> parameter of <literal>jsonb_set</literal> must be
- present. If these conditions are not met the <literal>target</literal> is
- returned unchanged.
- </para>
- <para>
- If the last path item is an object key, it will be created if it
- is absent and given the new value. If the last path item is an array
- index, if it is positive the item to set is found by counting from
- the left, and if negative by counting from the right - <literal>-1</literal>
- designates the rightmost element, and so on.
- If the item is out of the range -array_length .. array_length -1,
- and create_missing is true, the new value is added at the beginning
- of the array if the item is negative, and at the end of the array if
- it is positive.
- </para>
- </note>
-
- <note>
- <para>
- The <literal>json_typeof</literal> function's <literal>null</literal> return value
- should not be confused with a SQL NULL. While
- calling <literal>json_typeof('null'::json)</literal> will
- return <literal>null</literal>, calling <literal>json_typeof(NULL::json)</literal>
- will return a SQL NULL.
- </para>
- </note>
-
- <note>
- <para>
- If the argument to <literal>json_strip_nulls</literal> contains duplicate
- field names in any object, the result could be semantically somewhat
- different, depending on the order in which they occur. This is not an
- issue for <literal>jsonb_strip_nulls</literal> since <type>jsonb</type> values never have
- duplicate object field names.
- </para>
- </note>
-
- <para>
- See also <xref linkend="functions-aggregate"/> for the aggregate
- function <function>json_agg</function> which aggregates record
- values as JSON, and the aggregate function
- <function>json_object_agg</function> which aggregates pairs of values
- into a JSON object, and their <type>jsonb</type> equivalents,
- <function>jsonb_agg</function> and <function>jsonb_object_agg</function>.
- </para>
-
- </sect1>
+ &func-sqljson;
<sect1 id="functions-sequence">
<title>Sequence Manipulation Functions</title>
diff --git a/doc/src/sgml/gin.sgml b/doc/src/sgml/gin.sgml
index cc7cd1e..8c51e4e 100644
--- a/doc/src/sgml/gin.sgml
+++ b/doc/src/sgml/gin.sgml
@@ -102,6 +102,8 @@
<literal>?&</literal>
<literal>?|</literal>
<literal>@></literal>
+ <literal>@?</literal>
+ <literal>@~</literal>
</entry>
</row>
<row>
@@ -109,6 +111,8 @@
<entry><type>jsonb</type></entry>
<entry>
<literal>@></literal>
+ <literal>@?</literal>
+ <literal>@~</literal>
</entry>
</row>
<row>
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index e7b68fa..9c896c5 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -22,8 +22,16 @@
</para>
<para>
- There are two JSON data types: <type>json</type> and <type>jsonb</type>.
- They accept <emphasis>almost</emphasis> identical sets of values as
+ <productname>PostgreSQL</productname> offers two types for storing JSON
+ data: <type>json</type> and <type>jsonb</type>. To implement effective query
+ mechanisms for these data types, <productname>PostgreSQL</productname>
+ also provides the <type>jsonpath</type> data type described in
+ <xref linkend="datatype-jsonpath"/>.
+ </para>
+
+ <para>
+ The <type>json</type> and <type>jsonb</type> data types
+ accept <emphasis>almost</emphasis> identical sets of values as
input. The major practical difference is one of efficiency. The
<type>json</type> data type stores an exact copy of the input text,
which processing functions must reparse on each execution; while
@@ -217,6 +225,11 @@ SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
in this example, even though those are semantically insignificant for
purposes such as equality checks.
</para>
+
+ <para>
+ For the list of built-in functions and operators available for
+ constructing and processing JSON values, see <xref linkend="functions-json"/>.
+ </para>
</sect2>
<sect2 id="json-doc-design">
@@ -536,6 +549,19 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu
</para>
<para>
+ <literal>jsonb_ops</literal> and <literal>jsonb_path_ops</literal> also
+ support queries with <type>jsonpath</type> operators <literal>@?</literal>
+ and <literal>@~</literal>. The previous example for <literal>@></literal>
+ operator can be rewritten as follows:
+ <programlisting>
+-- Find documents in which the key "tags" contains array element "qui"
+SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';
+SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @~ '$.tags[*] == "qui"';
+</programlisting>
+
+ </para>
+
+ <para>
<type>jsonb</type> also supports <literal>btree</literal> and <literal>hash</literal>
indexes. These are usually useful only if it's important to check
equality of complete JSON documents.
@@ -593,4 +619,185 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu
lists, and scalars, as appropriate.
</para>
</sect2>
+
+ <sect2 id="datatype-jsonpath">
+ <title>jsonpath Type</title>
+
+ <indexterm zone="datatype-jsonpath">
+ <primary>jsonpath</primary>
+ </indexterm>
+
+ <para>
+ The <type>jsonpath</type> type implements support for the SQL/JSON path language
+ in <productname>PostgreSQL</productname> to effectively query JSON data.
+ It provides a binary representation of the parsed SQL/JSON path
+ expression that specifies the items to be retrieved by the path
+ engine from the JSON data for further processing with the
+ SQL/JSON query functions.
+ </para>
+
+ <para>
+ The SQL/JSON path language is fully integrated into the SQL engine:
+ the semantics of its predicates and operators generally follow SQL.
+ At the same time, to provide a most natural way of working with JSON data,
+ SQL/JSON path syntax uses some of the JavaScript conventions:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ Dot <literal>.</literal> is used for member access.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Square brackets <literal>[]</literal> are used for array access.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ SQL/JSON arrays are 0-relative, unlike regular SQL arrays that start from 1.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <para>
+ An SQL/JSON path expression is an SQL character string literal,
+ so it must be enclosed in single quotes. Following the JavaScript
+ conventions, character string literals within the path expression
+ must be enclosed in double quotes. Any single quotes within this
+ character string literal must be escaped with a single quote
+ by the SQL convention.
+ </para>
+
+ <para>
+ A path expression consists of a sequence of path elements,
+ which can be the following:
+ <itemizedlist>
+ <listitem>
+ <para>
+ Path literals of JSON primitive types:
+ Unicode text, numeric, true, false, or null.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Path variables listed in <xref linkend="type-jsonpath-variables"/>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Accessor operators listed in <xref linkend="type-jsonpath-accessors"/>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <type>jsonpath</type> operators
+ and methods listed in <xref linkend="functions-sqljson-path-operators"/>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Parentheses, which can be used to provide filter expressions
+ or define the order of path evaluation.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ For details on using <type>jsonpath</type> expressions with SQL/JSON
+ query functions, see <xref linkend="functions-sqljson-path"/>.
+ </para>
+
+ <table id="type-jsonpath-variables">
+ <title><type>jsonpath</type> Variables</title>
+ <tgroup cols="2">
+ <thead>
+ <row>
+ <entry>Variable</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>$</literal></entry>
+ <entry>A variable representing the JSON text to be queried
+ (the <firstterm>context item</firstterm>).
+ </entry>
+ </row>
+ <row>
+ <entry><literal>$varname</literal></entry>
+ <entry>A named variable. Its value must be set in the
+ <command>PASSING</command> clause. See <xref linkend="sqljson-input-clause"/>
+ for details.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>@</literal></entry>
+ <entry>A variable representing the result of path evaluation
+ in <link linkend="sqljson-filter-clause">filter expressions</link>.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <table id="type-jsonpath-accessors">
+ <title><type>jsonpath</type> Accessors</title>
+ <tgroup cols="2">
+ <thead>
+ <row>
+ <entry>Accessor Operator</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><para><literal>.<replaceable>key</replaceable></literal></para>
+ <para><literal>.$"<replaceable>varname</replaceable>"</literal></para>
+ </entry>
+ <entry>Member accessor that returns an object
+ member with the specified key. If the key name is a named
+ variable starting with <literal>$</literal> or does not
+ meet the JavaScript rules of an identifier, it must be enclosed in
+ double quotes as a character string literal.</entry>
+ </row>
+ <row>
+ <entry><literal>.*</literal></entry>
+ <entry>Wildcard member accessor that returns the values of all
+ members located at the top level of the current object.</entry>
+ </row>
+ <row>
+ <entry><literal>.**</literal></entry>
+ <entry>Recursive wildcard member accessor that processes
+ all levels of the JSON hierarchy of the current object and
+ returns all the member values, regardless of their nesting
+ level. This is a <productname>PostgreSQL</productname>
+ extension of the SQL/JSON standard.</entry>
+ </row>
+ <row>
+ <entry>
+ <para><literal>[<replaceable>subscript</replaceable>, ...]</literal></para>
+ <para><literal>[<replaceable>subscript</replaceable> to last]</literal></para>
+ </entry>
+ <entry>Array element accessor. The provided numeric subscripts return
+ the corresponding array elements. The first element in an array is
+ accessed with [0]. The <literal>last</literal> keyword denotes the last subscript
+ in an array and can be used to handle arrays of unknown length.</entry>
+ </row>
+ <row>
+ <entry><literal>[*]</literal></entry>
+ <entry>Wildcard array element accessor that returns all array elements.</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ For details on using <type>jsonpath</type> expressions with SQL/JSON query
+ functions, see <xref linkend="functions-sqljson-path"/>.
+ </para>
+
+ </sect2>
</sect1>
Re: SQL/JSON: documentation
On 06/27/2018 07:36 PM, Nikita Glukhov wrote:
Also it can be found in our sqljson repository on sqljson_doc branch:
https://github.com/postgrespro/sqljson/tree/sqljson_doc
Perhaps it is my unfamiliarity, but it seems that on lines 1067–1071,
the output clause for JSON_VALUE is given support for return types
json, jsonb, bytea, text, char, varchar, nchar "out of the box".
There are then examples on lines 1123–1135 of returning float, int,
and date.
Does that mean that the list in 1067–1071 is incomplete, and should
include additional data types?
Or does it mean that there is more cleverness buried in the
"must ... have a cast to the specified type" language than I
first understood?
Does the function support returning some wanted type w, not in the
out-of-the-box list, such as float, by searching for an intermediate
type t ∈ {json, jsonb, bytea, text, char, varchar, nchar} such that
∃ cast(t as w), then representing the JSON value as t, then casting
that to w ?
If so, what does it do if more than one t is a candidate?
Line 2081: "A typical path expression has the following structure"
It seems like a "weasel word" to have "typical" in the statement
of an expression grammar. Is there more to the grammar than is
given here?
Lines 2323 and 2330 ( / and % operators ). Do these behave differently
for integer than for float operands? If they provide integer operations,
which results do they produce for negative operands? (A recent minor
trauma reminded me that C before C99 left that unspecified, but as this
is a special-purpose language, perhaps there is a chance to avoid
leaving such details vague. :) For a similar-language example,
XPath/XQuery specifies that its idiv and mod operators have the
truncate-quotient-toward-zero semantics, regardless of the signs of
the operands.
Line 2519, like_regex: What regex dialect is accepted here? The same
as the PostgreSQL "POSIX regex"? Or some other?
This looks like very interesting functionality!
-Chap
Re: SQL/JSON: documentation
On 28.06.2018 05:23, Chapman Flack wrote:
On 06/27/2018 07:36 PM, Nikita Glukhov wrote:
Also it can be found in our sqljson repository on sqljson_doc branch:
https://github.com/postgrespro/sqljson/tree/sqljson_docPerhaps it is my unfamiliarity, but it seems that on lines 1067–1071,
the output clause for JSON_VALUE is given support for return types
json, jsonb, bytea, text, char, varchar, nchar "out of the box".There are then examples on lines 1123–1135 of returning float, int,
and date.Does that mean that the list in 1067–1071 is incomplete, and should
include additional data types?Or does it mean that there is more cleverness buried in the
"must ... have a cast to the specified type" language than I
first understood?Does the function support returning some wanted type w, not in the
out-of-the-box list, such as float, by searching for an intermediate
type t ∈ {json, jsonb, bytea, text, char, varchar, nchar} such that
∃ cast(t as w), then representing the JSON value as t, then casting
that to w ?If so, what does it do if more than one t is a candidate?
First, thank you for your interest in SQL/JSON docs.
Standard says only about returning of string (both binary and character),
numeric, boolean and datetime types in JSON_VALUE and only about string
types in JSON_QUERY.
In JSON_VALUE first searched cast from the SQL type corresponding to the
SQL/JSON type of a resulting scalar item to the target RETURNING type.
SQL/JSON type PG SQL type
string => text
number => numeric
boolean => boolean
date => date
time => time
time with tz => timetz
timestamp => timestamp
timestamp with tz => timestamptz
If this cast does not exist then conversion via input/output is tried (this
is our extension). But json and jsonb RETURNING types are exceptional here,
because SQL/JSON items can be converted directly to json[b] without casting.
But we also support returning of arbitrary PG types including arrays, domains
and records in both JSON_VALUE and JSON_QUERY. In JSON_VALUE values of this
types should be represented as serialized JSON strings, because JSON_VALUE
supports only returning of scalar items. The behavior of JSON_QUERY is similar
to the behavior json[b]_populate_record().
Examples:
-- CAST(numeric AS int) is used here
=# SELECT JSON_VALUE('1.8', '$' RETURNING int);
json_value
------------
2
(1 row)
-- CAST(text AS int) is used here
=# SELECT JSON_VALUE('"1"', '$' RETURNING int);
json_value
------------
1
(1 row)
-- CAST(text AS int) is used here
=# SELECT JSON_VALUE('"1.8"', '$' RETURNING int ERROR ON ERROR);
ERROR: invalid input syntax for integer: "1.8"
-- CAST(numeric AS int) is used here
# SELECT JSON_VALUE('"1.8"', '$.double().floor()' RETURNING int);
json_value
------------
1
(1 row)
-- array of points serialized into single JSON string
-- CAST(text AS point[]) is used
=# SELECT JSON_VALUE('"{\"(1,2)\",\"3,4\",NULL}"', '$' RETURNING point[]);
json_value
------------------------
{"(1,2)","(3,4)",NULL}
(1 row)
-- point[] is represented by JSON array of point strings
-- ARRAY[CAST(text AS point)] is used
=# SELECT JSON_QUERY('["(1, 2)", " 3 , 4 ", null]', '$' RETURNING point[]);
json_query
------------------------
{"(1,2)","(3,4)",NULL}
(1 row)
-- JSON object converted into SQL record type
=# SELECT JSON_QUERY('{"relname": "foo", "relnatts" : 5}', '$' RETURNING pg_class);
json_query
----------------------------------------
(foo,,,,,,,,,,,,,,,,5,,,,,,,,,,,,,,,,)
(1 row)
Line 2081: "A typical path expression has the following structure"
It seems like a "weasel word" to have "typical" in the statement
of an expression grammar. Is there more to the grammar than is
given here?
Yes, that expression grammar is incomplete because arithmetic operations
are supported on the top of jsonpath accessor expressions.
Here is nearly complete expression grammar (predicates are not included):
jsonpath ::=
[STRICT | LAX] jsonpath_expression
jsonpath_expression ::=
jsonpath_additive_expression
jsonpath_additive_expression ::=
[ jsonpath_additive_expression { + | - } ]
jsonpath_multiplicative_expression
jsonpath_multiplicative_expression ::=
[ jsonpath_multiplicative_expression { * | / | % } ]
jsonpath_unary_expression
jsonpath_unary_expression ::=
jsonpath_accessor_expression
| { + | - } jsonpath_unary_expression
jsonpath_accessor_expression ::=
jsonpath_primary { jsonpath_accessor }[...]
jsonpath_accessor ::=
. *
| . key_name
| . method_name ( jsonpath_expression [, ...] )
| '[' * ']'
| '[' jsonpath_expression [, ...] ']'
| ? ( predicate )
jsonpath_primary ::=
$
| @
| variable
| literal
| ( jsonpath_expression )
Lines 2323 and 2330 ( / and % operators ). Do these behave differently
for integer than for float operands? If they provide integer operations,
which results do they produce for negative operands? (A recent minor
trauma reminded me that C before C99 left that unspecified, but as this
is a special-purpose language, perhaps there is a chance to avoid
leaving such details vague. :) For a similar-language example,
XPath/XQuery specifies that its idiv and mod operators have the
truncate-quotient-toward-zero semantics, regardless of the signs of
the operands.
Arithmetic operations in jsonpath are implemented using PG numeric datatype,
which also is used in jsonb for representation of JSON numbers:
=# SELECT jsonb '3' @* '$ / 2';
?column?
--------------------
1.5000000000000000
(1 row)
=# SELECT jsonb '3.4' @* '$ % 2.3';
?column?
----------
1.1
(1 row)
=# SELECT jsonb '-3.4' @* '$ % 2.3';
?column?
----------
-1.1
(1 row)
The same behavior exists in JavaScript, but it seems that ordinary double
type is used there.
Line 2519, like_regex: What regex dialect is accepted here? The same
as the PostgreSQL "POSIX regex"? Or some other?
Standard requires XQuery regexes, but we have only POSIX regexes in PostgreSQL
now, so we decided to use the latter.
We will fix all these issues soon.
This looks like very interesting functionality!
-Chap
You can try this SQL/JSON examples in our web interface:
http://sqlfiddle.postgrespro.ru/#!21/
(please first select "PostgreSQL 11dev+SQL/JSON" in the version
selection field on the top toolbar).
--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Re: SQL/JSON: documentation
On 06/28/2018 06:45 PM, Nikita Glukhov wrote:
Standard says only about returning of string (both binary and character),
numeric, boolean and datetime types in JSON_VALUE and only about string
types in JSON_QUERY.
What I think I noticed was that right now, in func-sqljson.sgml,
the same list of seven types (not including numeric, boolean, or
datetime) is repeated for both JSON_QUERY and JSON_VALUE. Should
the list for JSON_VALUE also mention that numeric, boolean, and
datetime are supported there? That's the description that is near
line 1067.
Arithmetic operations in jsonpath are implemented using PG numeric
datatype,
which also is used in jsonb for representation of JSON numbers:
...
=# SELECT jsonb '-3.4' @* '$ % 2.3';
?column?
----------
-1.1
In a recent message[1]/messages/by-id/23660.1530070402@sss.pgh.pa.us it seemed that PG itself relies on the
underlying C compiler behavior, at least for int and float, which
could mean that on some platforms the answer is -1.1 and on others
+1.2. But I don't know whether that is true for PG numeric, since
that is implemented much more within PG itself, so perhaps it has
a platform-independent behavior. The XQuery result would be -1.1 on
all platforms, because the standard is explicit there.
-Chap
Re: SQL/JSON: documentation
On 28/06/2018 01:36, Nikita Glukhov wrote:
Attached patch with draft of SQL/JSON documentation written by
Liudmila Mantrova, Oleg Bartunov and me.Also it can be found in our sqljson repository on sqljson_doc branch:
https://github.com/postgrespro/sqljson/tree/sqljson_docWe continue to work on it.
Some structural comments:
- I don't think this should be moved to a separate file. Yes, func.sgml
is pretty big, but if we're going to split it up, we should do it in a
systematic way, not just one section.
- The refentries are not a bad idea, but again, if we just used them for
this one section, the navigation will behave weirdly. So I'd do it
without them, just using normal subsections.
- Stick to one-space indentation in XML.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: SQL/JSON: documentation
On 09/28/2018 01:29 PM, Peter Eisentraut wrote:
On 28/06/2018 01:36, Nikita Glukhov wrote:
Attached patch with draft of SQL/JSON documentation written by
Liudmila Mantrova, Oleg Bartunov and me.Also it can be found in our sqljson repository on sqljson_doc branch:
https://github.com/postgrespro/sqljson/tree/sqljson_docWe continue to work on it.
Some structural comments:
- I don't think this should be moved to a separate file. Yes, func.sgml
is pretty big, but if we're going to split it up, we should do it in a
systematic way, not just one section.
I'm in favor of doing that. It's rather a monster.
I agree it should not be done piecemeal.
cheers
andrew
--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: SQL/JSON: documentation
Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
On 09/28/2018 01:29 PM, Peter Eisentraut wrote:
- I don't think this should be moved to a separate file. Yes, func.sgml
is pretty big, but if we're going to split it up, we should do it in a
systematic way, not just one section.
I'm in favor of doing that. It's rather a monster.
I agree it should not be done piecemeal.
Maybe split it into one file per existing section?
Although TBH, I am not convinced that the benefits of doing that
will exceed the back-patching pain we'll incur.
regards, tom lane
Re: SQL/JSON: documentation
On 09/28/2018 08:29 PM, Peter Eisentraut wrote:
On 28/06/2018 01:36, Nikita Glukhov wrote:
Attached patch with draft of SQL/JSON documentation written by
Liudmila Mantrova, Oleg Bartunov and me.Also it can be found in our sqljson repository on sqljson_doc branch:
https://github.com/postgrespro/sqljson/tree/sqljson_docWe continue to work on it.
Some structural comments:
- I don't think this should be moved to a separate file. Yes, func.sgml
is pretty big, but if we're going to split it up, we should do it in a
systematic way, not just one section.- The refentries are not a bad idea, but again, if we just used them for
this one section, the navigation will behave weirdly. So I'd do it
without them, just using normal subsections.- Stick to one-space indentation in XML.
Hi Peter,
Thanks for your comments! I'm OK with keeping all reference information
in func.sgml and will rework it as you suggest. While refentries are
dear to my heart, let's use subsections for now for the sake of
consistency. We'll continue working with Nikita and Oleg to improve the
content before we resend an updated patch; I believe we might still need
a separate source file if we end up having a separate chapter with usage
examples and implementation details.
--
Liudmila Mantrova
Technical writer at Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Re: SQL/JSON: documentation
On Mon, Oct 1, 2018 at 2:24 PM Liudmila Mantrova <l.mantrova@postgrespro.ru> wrote:
We'll continue working with Nikita and Oleg to improve the
content before we resend an updated patch; I believe we might still need
a separate source file if we end up having a separate chapter with usage
examples and implementation details.
Hi,
Any progress on that? It would be nice to have a new version of the
documentation, and I would even advocate to put it into the json path patch [1]/messages/by-id/fcc6fc6a-b497-f39a-923d-aa34d0c588e8@2ndQuadrant.com
(especially, since there were already requests for that, and I personally don't
see any reason to keep them separately). For now I'll move the item to
the next CF.
[1]: /messages/by-id/fcc6fc6a-b497-f39a-923d-aa34d0c588e8@2ndQuadrant.com
Re: SQL/JSON: documentation
On 11/29/18 7:34 PM, Dmitry Dolgov wrote:
Hi,
Any progress on that? It would be nice to have a new version of the
documentation, and I would even advocate to put it into the json path patch [1]
(especially, since there were already requests for that, and I personally don't
see any reason to keep them separately). For now I'll move the item to
the next CF.[1]:/messages/by-id/fcc6fc6a-b497-f39a-923d-aa34d0c588e8@2ndQuadrant.com
Hi Dmitry,
Unfortunately, I couldn't find much time for this activity, but as far
as I understand, thread [1] only requires jsonpath documentation right
now. So I extracted the relevant parts from this patch, reworked path
expression description, and moved it to func.sgml as Peter suggested
(attached). Nikita is going to add this patch to the jsonpath thread
together with the updated code once it's ready.
Next, I'm going to address Peter's feedback on the rest of this
documentation patch (which probably also needs to be split for threads
[2]: /messages/by-id/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru
[2]: /messages/by-id/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru
/messages/by-id/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru
[3]: /messages/by-id/132f26c4-dfc6-f8fd-4764-2cbf455a3aec@postgrespro.ru
/messages/by-id/132f26c4-dfc6-f8fd-4764-2cbf455a3aec@postgrespro.ru
--
Liudmila Mantrova
Technical writer at Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:
jsonpath-docs.patchtext/x-patch; name=jsonpath-docs.patchDownload
diff --git a/doc/src/sgml/biblio.sgml b/doc/src/sgml/biblio.sgml
index 4953024..f06305d 100644
--- a/doc/src/sgml/biblio.sgml
+++ b/doc/src/sgml/biblio.sgml
@@ -136,6 +136,17 @@
<pubdate>1988</pubdate>
</biblioentry>
+ <biblioentry id="sqltr-19075-6">
+ <title>SQL Technical Report</title>
+ <subtitle>Part 6: SQL support for JavaScript Object
+ Notation (JSON)</subtitle>
+ <edition>First Edition.</edition>
+ <biblioid>
+ <ulink url="http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip"></ulink>.
+ </biblioid>
+ <pubdate>2017.</pubdate>
+ </biblioentry>
+
</bibliodiv>
<bibliodiv>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 112d962..20ef7df 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -11285,26 +11285,661 @@ table2-mapping
</sect1>
<sect1 id="functions-json">
- <title>JSON Functions and Operators</title>
+ <title>JSON Functions, Operators, and Expressions</title>
- <indexterm zone="functions-json">
+ <para>
+ The functions, operators, and expressions described in this section
+ operate on JSON data:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ SQL/JSON path expressions
+ (see <xref linkend="functions-sqljson-path"/>).
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ PostgreSQL-specific functions and operators for JSON
+ data types (see <xref linkend="functions-pgjson"/>).
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <para>
+ To learn more about the SQL/JSON standard, see
+ <xref linkend="sqltr-19075-6"/>. For details on JSON types
+ supported in <productname>PostgreSQL</productname>,
+ see <xref linkend="datatype-json"/>.
+ </para>
+
+ <sect2 id="functions-sqljson-path">
+ <title>SQL/JSON Path Expressions</title>
+
+ <para>
+ SQL/JSON path expressions specify the items to be retrieved
+ from the JSON data, similar to XPath expressions used
+ for SQL access to XML. In <productname>PostgreSQL</productname>,
+ path expressions are implemented as the <type>jsonpath</type>
+ data type, described in <xref linkend="datatype-jsonpath"/>.
+ </para>
+
+ <para>JSON query functions and operators
+ pass the provided path expression to the <firstterm>path engine</firstterm>
+ for evaluation. If the expression matches the JSON data to be queried,
+ the corresponding SQL/JSON item is returned.
+ Path expressions are written in the SQL/JSON path language
+ and can also include arithmetic expressions and functions.
+ Query functions treat the provided expression as a
+ text string, so it must be enclosed in single quotes.
+ </para>
+
+ <para>
+ A path expression consists of a sequence of elements allowed
+ by the <type>jsonpath</type> data type.
+ The path expression is evaluated from left to right, but
+ you can use parentheses to change the order of operations.
+ If the evaluation is successful, an SQL/JSON sequence is produced,
+ and the evaluation result is returned to the JSON query function
+ that completes the specified computation.
+ </para>
+
+ <para>
+ To refer to the JSON data to be queried (the
+ <firstterm>context item</firstterm>), use the <literal>$</literal> sign
+ in the path expression. It can be followed by one or more
+ <link linkend="type-jsonpath-accessors">accessor operators</link>,
+ which go down the JSON structure level by level to retrieve the
+ content of context item. Each operator that follows deals with the
+ result of the previous evaluation step.
+ </para>
+
+ <para>
+ For example, suppose you have some JSON data from a GPS tracker that you
+ would like to parse, such as:
+<programlisting>
+{ "track" :
+ {
+ "segments" : [
+ { "location": [ 47.763, 13.4034 ],
+ "start time": "2018-10-14 10:05:14",
+ "HR": 73
+ },
+ { "location": [ 47.706, 13.2635 ],
+ "start time": "2018-10-14 10:39:21",
+ "HR": 130
+ } ]
+ }
+}
+</programlisting>
+ </para>
+
+ <para>
+ To retrieve the available track segments, you need to use the
+ <literal>.<replaceable>key</replaceable></literal> accessor
+ operator for all the preceding JSON objects:
+<programlisting>
+'$.track.segments'
+</programlisting>
+ </para>
+
+ <para>
+ If the item to retrieve is an element of an array, you have
+ to unnest this array using the [*] operator. For example,
+ the following path will return location coordinates for all
+ the available track segments:
+<programlisting>
+'$.track.segments[*].location'
+</programlisting>
+ </para>
+
+ <para>
+ To return the coordinates of the first segment only, you can
+ specify the corresponding subscript in the <literal>[]</literal>
+ accessor operator. Note that the SQL/JSON arrays are 0-relative:
+<programlisting>
+'$.track.segments[0].location'
+</programlisting>
+ </para>
+
+ <para>
+ The result of each path evaluation step can be processed
+ by one or more <type>jsonpath</type> operators and methods
+ listed in <xref linkend="functions-sqljson-path-operators"/>.
+ Each method must be preceded by a dot, while arithmetic and boolean
+ operators are separated from the operands by spaces. For example,
+ you can convert a text string into a datetime value:
+<programlisting>
+'$.track.segments[*]."start time".datetime()'
+</programlisting>
+ For more examples of using <type>jsonpath</type> operators
+ and methods within path expressions, see
+ <xref linkend="functions-sqljson-path-operators"/>.
+ </para>
+
+ <para>
+ When defining the path, you can also use one or more
+ <firstterm>filter expressions</firstterm>, which work similar to
+ the <command>WHERE</command> clause in SQL. Each filter expression
+ can provide one or more filtering conditions that are applied
+ to the result of the path evaluation. Each filter expression must
+ be enclosed in parentheses and preceded by a question mark.
+ Filter expressions are applied from left to right and can be nested.
+ The <literal>@</literal> variable denotes the current path evaluation
+ result to be filtered, and can be followed by one or more accessor
+ operators to define the JSON element by which to filter the result.
+ Functions and operators that can be used in the filtering condition
+ are listed in <xref linkend="functions-sqljson-filter-ex-table"/>.
+ The result of the filter expression may be true, false, or unknown.
+ </para>
+
+ <para>
+ For example, the following path expression returns the heart
+ rate value only if it is higher than 130:
+<programlisting>
+'$.track.segments[*].HR ? (@ > 130)'
+</programlisting>
+ </para>
+
+ <para>
+ But suppose you would like to retrieve the start time of this segment
+ instead. In this case, you have to filter out irrelevant
+ segments before getting the start time, so the path in the
+ filter condition looks differently:
+<programlisting>
+'$.track.segments[*] ? (@.HR > 130)."start time"'
+</programlisting>
+ </para>
+
+ <para>
+ <productname>PostgreSQL</productname> also implements the following
+ extensions of the SQL/JSON standard:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ Enclosing the path specification into square brackets
+ <literal>[]</literal> automatically wraps the path evaluation
+ result into an array.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ A path expression can be a boolean predicate. For example:
+<programlisting>
+'$.track.segments[*].HR < 70'
+</programlisting>
+ </para>
+ </listitem>
+ <listitem>
+ <para>Writing the path as an expression is also valid:
+<programlisting>
+'$' || '.' || 'a'
+</programlisting>
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <sect3 id="strict-and-lax-modes">
+ <title>Strict and Lax Modes</title>
+ <para>
+ When you query JSON data, the path expression may not match the
+ actual JSON data structure. An attempt to access a non-existent
+ member of an object or element of an array results in a
+ structural error. SQL/JSON path expressions have two modes
+ of handling structural errors:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ lax (default) — the path engine implicitly adapts
+ the queried data to the specified path.
+ Any remaining structural errors are suppressed and converted
+ to empty SQL/JSON sequences.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ strict — if a structural error occurs, an error is raised.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <para>
+ The lax mode facilitates matching of a JSON document structure and path
+ expression if the JSON data does not conform to the expected schema.
+ If an operand does not match the requirements of a particular operation,
+ it can be automatically wrapped as an SQL/JSON array or unwrapped by
+ converting its elements into an SQL/JSON sequence before performing
+ this operation. Besides, comparison operators automatically unwrap their
+ operands in the lax mode, so you can compare SQL/JSON arrays
+ out-of-the-box. Arrays of size 1 are interchangeable with a singleton.
+ </para>
+
+ <para>
+ For example, when querying the GPS data listed above, you can
+ abstract from the fact that it stores an array of segments
+ when using the lax mode:
+<programlisting>
+'lax $.track.segments.location'
+</programlisting>
+ </para>
+
+ <para>
+ In the strict mode, the specified path must exactly match the structure of
+ the queried JSON document to return an SQL/JSON item, so using this
+ path expression will cause an error. To get the same result as in
+ the lax mode, you have to explicitly unwrap the
+ <literal>segments</literal> array:
+<programlisting>
+'strict $.track.segments[*].location'
+</programlisting>
+ </para>
+
+ <para>
+ Implicit unwrapping in the lax mode is not performed in the following cases:
+ <itemizedlist>
+ <listitem>
+ <para>
+ The path expression contains <literal>type()</literal> or
+ <literal>size()</literal> methods that return the type
+ and the number of elements in the array, respectively.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The queried JSON data contain nested arrays. In this case, only
+ the outermost array is unwrapped, while all the inner arrays
+ remain unchanged. Thus, implicit unwrapping can only go one
+ level down within each path evaluation step.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ </sect3>
+
+ <sect3 id="functions-sqljson-path-operators">
+ <title>SQL/JSON Path Operators and Methods</title>
+
+ <table id="functions-sqljson-op-table">
+ <title><type>jsonpath</type> Operators and Methods</title>
+ <tgroup cols="5">
+ <thead>
+ <row>
+ <entry>Operator/Method</entry>
+ <entry>Description</entry>
+ <entry>Example JSON</entry>
+ <entry>Example Query</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>+</literal> (unary)</entry>
+ <entry>Plus operator that iterates over the json sequence</entry>
+ <entry><literal>{"x": [2.85, -14.7, -9.4]}</literal></entry>
+ <entry><literal>+ $.x.floor()</literal></entry>
+ <entry><literal>2, -15, -10</literal></entry>
+ </row>
+ <row>
+ <entry><literal>-</literal> (unary)</entry>
+ <entry>Minus operator that iterates over the json sequence</entry>
+ <entry><literal>{"x": [2.85, -14.7, -9.4]}</literal></entry>
+ <entry><literal>- $.x.floor()</literal></entry>
+ <entry><literal>-2, 15, 10</literal></entry>
+ </row>
+ <row>
+ <entry><literal>+</literal> (binary)</entry>
+ <entry>Addition</entry>
+ <entry><literal>[2]</literal></entry>
+ <entry><literal>2 + $[0]</literal></entry>
+ <entry><literal>4</literal></entry>
+ </row>
+ <row>
+ <entry><literal>-</literal> (binary)</entry>
+ <entry>Subtraction</entry>
+ <entry><literal>[2]</literal></entry>
+ <entry><literal>4 - $[0]</literal></entry>
+ <entry><literal>2</literal></entry>
+ </row>
+ <row>
+ <entry><literal>*</literal></entry>
+ <entry>Multiplication</entry>
+ <entry><literal>[4]</literal></entry>
+ <entry><literal>2 * $[0]</literal></entry>
+ <entry><literal>8</literal></entry>
+ </row>
+ <row>
+ <entry><literal>/</literal></entry>
+ <entry>Division</entry>
+ <entry><literal>[8]</literal></entry>
+ <entry><literal>$[0] / 2</literal></entry>
+ <entry><literal>4</literal></entry>
+ </row>
+ <row>
+ <entry><literal>%</literal></entry>
+ <entry>Modulus</entry>
+ <entry><literal>[32]</literal></entry>
+ <entry><literal>$[0] % 10</literal></entry>
+ <entry><literal>2</literal></entry>
+ </row>
+ <row>
+ <entry><literal>type()</literal></entry>
+ <entry>Type of the SQL/JSON item</entry>
+ <entry><literal>[1, "2", {}]</literal></entry>
+ <entry><literal>$[*].type()</literal></entry>
+ <entry><literal>"number", "string", "object"</literal></entry>
+ </row>
+ <row>
+ <entry><literal>size()</literal></entry>
+ <entry>Size of the SQL/JSON item</entry>
+ <entry><literal>{"m": [11, 15]}</literal></entry>
+ <entry><literal>$.m.size()</literal></entry>
+ <entry><literal>2</literal></entry>
+ </row>
+ <row>
+ <entry><literal>double()</literal></entry>
+ <entry>Approximate numeric value converted from a string</entry>
+ <entry><literal>{"len": "1.9"}</literal></entry>
+ <entry><literal>$.len.double() * 2</literal></entry>
+ <entry><literal>3.8</literal></entry>
+ </row>
+ <row>
+ <entry><literal>ceiling()</literal></entry>
+ <entry>Nearest integer greater than or equal to the SQL/JSON number</entry>
+ <entry><literal>{"h": 1.3}</literal></entry>
+ <entry><literal>$.h.ceiling()</literal></entry>
+ <entry><literal>2</literal></entry>
+ </row>
+ <row>
+ <entry><literal>floor()</literal></entry>
+ <entry>Nearest integer less than or equal to the SQL/JSON number</entry>
+ <entry><literal>{"h": 1.3}</literal></entry>
+ <entry><literal>$.h.floor()</literal></entry>
+ <entry><literal>1</literal></entry>
+ </row>
+ <row>
+ <entry><literal>abs()</literal></entry>
+ <entry>Absolute value of the SQL/JSON number</entry>
+ <entry><literal>{"z": -0.3}</literal></entry>
+ <entry><literal>$.z.abs()</literal></entry>
+ <entry><literal>0.3</literal></entry>
+ </row>
+ <row>
+ <entry><literal>datetime()</literal></entry>
+ <entry>Datetime value converted from a string</entry>
+ <entry><literal>["2015-8-1", "2015-08-12"]</literal></entry>
+ <entry><literal>$[*] ? (@.datetime() < "2015-08-2". datetime())</literal></entry>
+ <entry><literal>2015-8-1</literal></entry>
+ </row>
+ <row>
+ <entry><literal>datetime(<replaceable>template</replaceable>)</literal></entry>
+ <entry>Datetime value converted from a string with a specified template</entry>
+ <entry><literal>["12:30", "18:40"]</literal></entry>
+ <entry><literal>$[*].datetime("HH24:MI")</literal></entry>
+ <entry><literal>"12:30:00", "18:40:00"</literal></entry>
+ </row>
+ <row>
+ <entry><literal>keyvalue()</literal></entry>
+ <entry>Array of objects containing two members ("key" and "value" of the SQL/JSON item)</entry>
+ <entry><literal>{"x": "20", "y": 32}</literal></entry>
+ <entry><literal>$.keyvalue()</literal></entry>
+ <entry><literal>{"key": "x", "value": "20"}, {"key": "y", "value": 32}</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <table id="functions-sqljson-filter-ex-table">
+ <title><type>jsonpath</type> Filter Expression Elements</title>
+ <tgroup cols="5">
+ <thead>
+ <row>
+ <entry>Value/Predicate</entry>
+ <entry>Description</entry>
+ <entry>Example JSON</entry>
+ <entry>Example Query</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>==</literal></entry>
+ <entry>Equality operator</entry>
+ <entry><literal>[1, 2, 1, 3]</literal></entry>
+ <entry><literal>$[*] ? (@ == 1)</literal></entry>
+ <entry><literal>1, 1</literal></entry>
+ </row>
+ <row>
+ <entry><literal>!=</literal></entry>
+ <entry>Non-equality operator</entry>
+ <entry><literal>[1, 2, 1, 3]</literal></entry>
+ <entry><literal>$[*] ? (@ != 1)</literal></entry>
+ <entry><literal>2, 3</literal></entry>
+ </row>
+ <row>
+ <entry><literal><></literal></entry>
+ <entry>Non-equality operator (same as <literal>!=</literal>)</entry>
+ <entry><literal>[1, 2, 1, 3]</literal></entry>
+ <entry><literal>$[*] ? (@ <> 1)</literal></entry>
+ <entry><literal>2, 3</literal></entry>
+ </row>
+ <row>
+ <entry><literal><</literal></entry>
+ <entry>Less-than operator</entry>
+ <entry><literal>[1, 2, 3]</literal></entry>
+ <entry><literal>$[*] ? (@ < 2)</literal></entry>
+ <entry><literal>1, 2</literal></entry>
+ </row>
+ <row>
+ <entry><literal><=</literal></entry>
+ <entry>Less-than-or-equal-to operator</entry>
+ <entry><literal>[1, 2, 3]</literal></entry>
+ <entry><literal>$[*] ? (@ < 2)</literal></entry>
+ <entry><literal>1</literal></entry>
+ </row>
+ <row>
+ <entry><literal>></literal></entry>
+ <entry>Greater-than operator</entry>
+ <entry><literal>[1, 2, 3]</literal></entry>
+ <entry><literal>$[*] ? (@ > 2)</literal></entry>
+ <entry><literal>3</literal></entry>
+ </row>
+ <row>
+ <entry><literal>></literal></entry>
+ <entry>Greater-than-or-equal-to operator</entry>
+ <entry><literal>[1, 2, 3]</literal></entry>
+ <entry><literal>$[*] ? (@ >= 2)</literal></entry>
+ <entry><literal>2, 3</literal></entry>
+ </row>
+ <row>
+ <entry><literal>true</literal></entry>
+ <entry>Value used to perform comparison with JSON <literal>true</literal> literal</entry>
+ <entry><literal>[{"name": "John", "parent": false},
+ {"name": "Chris", "parent": true}]</literal></entry>
+ <entry><literal>$[*] ? (@.parent == true)</literal></entry>
+ <entry><literal>{"name": "Chris", "parent": true}</literal></entry>
+ </row>
+ <row>
+ <entry><literal>false</literal></entry>
+ <entry>Value used to perform comparison with JSON <literal>false</literal> literal</entry>
+ <entry><literal>[{"name": "John", "parent": false},
+ {"name": "Chris", "parent": true}]</literal></entry>
+ <entry><literal>$[*] ? (@.parent == false)</literal></entry>
+ <entry><literal>{"name": "John", "parent": false}</literal></entry>
+ </row>
+ <row>
+ <entry><literal>null</literal></entry>
+ <entry>Value used to perform comparison with JSON <literal>null</literal> value</entry>
+ <entry><literal>[{"name": "Mary", "job": null},
+ {"name": "Michael", "job": "driver"}]</literal></entry>
+ <entry><literal>$[*] ? (@.job == null) .name</literal></entry>
+ <entry><literal>"Mary"</literal></entry>
+ </row>
+ <row>
+ <entry><literal>&&</literal></entry>
+ <entry>Boolean AND</entry>
+ <entry><literal>[1, 3, 7]</literal></entry>
+ <entry><literal>$[*] ? (@ > 1 && @ < 5)</literal></entry>
+ <entry><literal>3</literal></entry>
+ </row>
+ <row>
+ <entry><literal>||</literal></entry>
+ <entry>Boolean OR</entry>
+ <entry><literal>[1, 3, 7]</literal></entry>
+ <entry><literal>$[*] ? (@ < 1 || @ > 5)</literal></entry>
+ <entry><literal>7</literal></entry>
+ </row>
+ <row>
+ <entry><literal>!</literal></entry>
+ <entry>Boolean NOT</entry>
+ <entry><literal>[1, 3, 7]</literal></entry>
+ <entry><literal>$[*] ? (!(@ < 5))</literal></entry>
+ <entry><literal>7</literal></entry>
+ </row>
+ <row>
+ <entry><literal>like_regex</literal></entry>
+ <entry>Tests pattern matching with POSIX regular expressions</entry>
+ <entry><literal>["abc", "abd", "aBdC", "abdacb", "babc"]</literal></entry>
+ <entry><literal>$[*] ? (@ like_regex "^ab.*c" flag "i")</literal></entry>
+ <entry><literal>"abc", "aBdC", "abdacb"</literal></entry>
+ </row>
+ <row>
+ <entry><literal>starts with</literal></entry>
+ <entry>Tests whether the second operand is an initial substring of the first operand</entry>
+ <entry><literal>["John Smith", "Mary Stone", "Bob Johnson"]</literal></entry>
+ <entry><literal>$[*] ? (@ starts with "John")</literal></entry>
+ <entry><literal>"John Smith"</literal></entry>
+ </row>
+ <row>
+ <entry><literal>exists</literal></entry>
+ <entry>Tests whether a path expression has at least one SQL/JSON item</entry>
+ <entry><literal>{"x": [1, 2], "y": [2, 4]}</literal></entry>
+ <entry><literal>strict $.* ? (exists (@ ? (@[*] > 2)))</literal></entry>
+ <entry><literal>2, 4</literal></entry>
+ </row>
+ <row>
+ <entry><literal>is unknown</literal></entry>
+ <entry>Tests whether a boolean condition is <literal>unknown</literal></entry>
+ <entry><literal>[-1, 2, 7, "infinity"]</literal></entry>
+ <entry><literal>$[*] ? ((@ > 0) is unknown)</literal></entry>
+ <entry><literal>"infinity"</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <table id="functions-sqljson-extra-op-table">
+ <title>Extended <type>jsonpath</type> Methods</title>
+ <tgroup cols="5">
+ <thead>
+ <row>
+ <entry>Method</entry>
+ <entry>Description</entry>
+ <entry>Example JSON</entry>
+ <entry>Example Query</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>min()</literal></entry>
+ <entry>Minimum value in the json array</entry>
+ <entry><literal>[1, 2, 0, 3, 1]</literal></entry>
+ <entry><literal>$.min()</literal></entry>
+ <entry><literal>0</literal></entry>
+ </row>
+ <row>
+ <entry><literal>max()</literal></entry>
+ <entry>Maximum value in the json array</entry>
+ <entry><literal>[1, 2, 0, 3, 1]</literal></entry>
+ <entry><literal>$.max()</literal></entry>
+ <entry><literal>3</literal></entry>
+ </row>
+ <row>
+ <entry><literal>map()</literal></entry>
+ <entry>Calculate an expression by applying a given function
+ to each element of the json array
+ </entry>
+ <entry><literal>[1, 2, 0]</literal></entry>
+ <entry><literal>$.map(@ * 2)</literal></entry>
+ <entry><literal>[2, 4, 0]</literal></entry>
+ </row>
+ <row>
+ <entry><literal>reduce()</literal></entry>
+ <entry>Calculate an aggregate expression by combining elements
+ of the json array using a given function
+ ($1 references the current result, $2 references the current element)
+ </entry>
+ <entry><literal>[3, 5, 9]</literal></entry>
+ <entry><literal>$.reduce($1 + $2)</literal></entry>
+ <entry><literal>17</literal></entry>
+ </row>
+ <row>
+ <entry><literal>fold()</literal></entry>
+ <entry>Calculate an aggregate expression by combining elements
+ of the json array using a given function
+ with the specified initial value
+ ($1 references the current result, $2 references the current element)
+ </entry>
+ <entry><literal>[2, 3, 4]</literal></entry>
+ <entry><literal>$.fold($1 * $2, 1)</literal></entry>
+ <entry><literal>24</literal></entry>
+ </row>
+ <row>
+ <entry><literal>foldl()</literal></entry>
+ <entry>Calculate an aggregate expression by combining elements
+ of the json array using a given function from left to right
+ with the specified initial value
+ ($1 references the current result, $2 references the current element)
+ </entry>
+ <entry><literal>[1, 2, 3]</literal></entry>
+ <entry><literal>$.foldl([$1, $2], [])</literal></entry>
+ <entry><literal>[[[[], 1], 2], 3]</literal></entry>
+ </row>
+ <row>
+ <entry><literal>foldr()</literal></entry>
+ <entry>Calculate an aggregate expression by combining elements
+ of the json array using a given function from right to left
+ with the specified initial value
+ ($1 references the current result, $2 references the current element)
+ </entry>
+ <entry><literal>[1, 2, 3]</literal></entry>
+ <entry><literal>$.foldr([$2, $1], [])</literal></entry>
+ <entry><literal>[[[[], 3], 2], 1]</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect3>
+
+ </sect2>
+
+ <sect2 id="functions-pgjson">
+ <title>PostgreSQL-specific JSON Functions and Operators</title>
+ <indexterm zone="functions-pgjson">
<primary>JSON</primary>
<secondary>functions and operators</secondary>
</indexterm>
- <para>
+ <para>
<xref linkend="functions-json-op-table"/> shows the operators that
- are available for use with the two JSON data types (see <xref
+ are available for use with JSON data types (see <xref
linkend="datatype-json"/>).
</para>
<table id="functions-json-op-table">
<title><type>json</type> and <type>jsonb</type> Operators</title>
- <tgroup cols="5">
+ <tgroup cols="6">
<thead>
<row>
<entry>Operator</entry>
<entry>Right Operand Type</entry>
+ <entry>Return type</entry>
<entry>Description</entry>
<entry>Example</entry>
<entry>Example Result</entry>
@@ -11314,6 +11949,7 @@ table2-mapping
<row>
<entry><literal>-></literal></entry>
<entry><type>int</type></entry>
+ <entry><type>json</type> or <type>jsonb</type></entry>
<entry>Get JSON array element (indexed from zero, negative
integers count from the end)</entry>
<entry><literal>'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2</literal></entry>
@@ -11322,6 +11958,7 @@ table2-mapping
<row>
<entry><literal>-></literal></entry>
<entry><type>text</type></entry>
+ <entry><type>json</type> or <type>jsonb</type></entry>
<entry>Get JSON object field by key</entry>
<entry><literal>'{"a": {"b":"foo"}}'::json->'a'</literal></entry>
<entry><literal>{"b":"foo"}</literal></entry>
@@ -11329,6 +11966,7 @@ table2-mapping
<row>
<entry><literal>->></literal></entry>
<entry><type>int</type></entry>
+ <entry><type>text</type></entry>
<entry>Get JSON array element as <type>text</type></entry>
<entry><literal>'[1,2,3]'::json->>2</literal></entry>
<entry><literal>3</literal></entry>
@@ -11336,6 +11974,7 @@ table2-mapping
<row>
<entry><literal>->></literal></entry>
<entry><type>text</type></entry>
+ <entry><type>text</type></entry>
<entry>Get JSON object field as <type>text</type></entry>
<entry><literal>'{"a":1,"b":2}'::json->>'b'</literal></entry>
<entry><literal>2</literal></entry>
@@ -11343,17 +11982,55 @@ table2-mapping
<row>
<entry><literal>#></literal></entry>
<entry><type>text[]</type></entry>
- <entry>Get JSON object at specified path</entry>
+ <entry><type>json</type> or <type>jsonb</type></entry>
+ <entry>Get JSON object at the specified path</entry>
<entry><literal>'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'</literal></entry>
<entry><literal>{"c": "foo"}</literal></entry>
</row>
<row>
<entry><literal>#>></literal></entry>
<entry><type>text[]</type></entry>
- <entry>Get JSON object at specified path as <type>text</type></entry>
+ <entry><type>text</type></entry>
+ <entry>Get JSON object at the specified path as <type>text</type></entry>
<entry><literal>'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'</literal></entry>
<entry><literal>3</literal></entry>
</row>
+ <row>
+ <entry><literal>@*</literal></entry>
+ <entry><type>jsonpath</type></entry>
+ <entry><type>setof json</type> or <type>setof jsonb</type></entry>
+ <entry>Get all JSON items returned by JSON path for the specified JSON value</entry>
+ <entry><literal>'{"a":[1,2,3,4,5]}'::json @* '$.a[*] ? (@ > 2)'</literal></entry>
+ <entry><programlisting>
+3
+4
+5
+</programlisting></entry>
+ </row>
+ <row>
+ <entry><literal>@#</literal></entry>
+ <entry><type>jsonpath</type></entry>
+ <entry><type>json</type> or <type>jsonb</type></entry>
+ <entry>Get all JSON items returned by JSON path for the specified JSON value. If there is more than one item, they will be wrapped into an array.</entry>
+ <entry><literal>'{"a":[1,2,3,4,5]}'::json @# '$.a[*] ? (@ > 2)'</literal></entry>
+ <entry><literal>[3, 4, 5]</literal></entry>
+ </row>
+ <row>
+ <entry><literal>@?</literal></entry>
+ <entry><type>jsonpath</type></entry>
+ <entry><type>boolean</type></entry>
+ <entry>Check whether JSON path returns any item for the specified JSON value</entry>
+ <entry><literal>'{"a":[1,2,3,4,5]}'::json @? '$.a[*] ? (@ > 2)'</literal></entry>
+ <entry><literal>true</literal></entry>
+ </row>
+ <row>
+ <entry><literal>@~</literal></entry>
+ <entry><type>jsonpath</type></entry>
+ <entry><type>boolean</type></entry>
+ <entry>Get JSON path predicate result for the specified JSON value</entry>
+ <entry><literal>'{"a":[1,2,3,4,5]}'::json @~ '$.a[*] > 2'</literal></entry>
+ <entry><literal>true</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -12119,6 +12796,7 @@ table2-mapping
JSON fields that do not appear in the target row type will be
omitted from the output, and target columns that do not match any
JSON field will simply be NULL.
+
</para>
</note>
@@ -12173,6 +12851,7 @@ table2-mapping
<function>jsonb_agg</function> and <function>jsonb_object_agg</function>.
</para>
+ </sect2>
</sect1>
<sect1 id="functions-sequence">
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index e7b68fa..2ba7520 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -22,8 +22,16 @@
</para>
<para>
- There are two JSON data types: <type>json</type> and <type>jsonb</type>.
- They accept <emphasis>almost</emphasis> identical sets of values as
+ <productname>PostgreSQL</productname> offers two types for storing JSON
+ data: <type>json</type> and <type>jsonb</type>. To implement effective query
+ mechanisms for these data types, <productname>PostgreSQL</productname>
+ also provides the <type>jsonpath</type> data type described in
+ <xref linkend="datatype-jsonpath"/>.
+ </para>
+
+ <para>
+ The <type>json</type> and <type>jsonb</type> data types
+ accept <emphasis>almost</emphasis> identical sets of values as
input. The major practical difference is one of efficiency. The
<type>json</type> data type stores an exact copy of the input text,
which processing functions must reparse on each execution; while
@@ -217,6 +225,11 @@ SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
in this example, even though those are semantically insignificant for
purposes such as equality checks.
</para>
+
+ <para>
+ For the list of built-in functions and operators available for
+ constructing and processing JSON values, see <xref linkend="functions-json"/>.
+ </para>
</sect2>
<sect2 id="json-doc-design">
@@ -536,6 +549,19 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu
</para>
<para>
+ <literal>jsonb_ops</literal> and <literal>jsonb_path_ops</literal> also
+ support queries with <type>jsonpath</type> operators <literal>@?</literal>
+ and <literal>@~</literal>. The previous example for <literal>@></literal>
+ operator can be rewritten as follows:
+ <programlisting>
+-- Find documents in which the key "tags" contains array element "qui"
+SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';
+SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @~ '$.tags[*] == "qui"';
+</programlisting>
+
+ </para>
+
+ <para>
<type>jsonb</type> also supports <literal>btree</literal> and <literal>hash</literal>
indexes. These are usually useful only if it's important to check
equality of complete JSON documents.
@@ -593,4 +619,224 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu
lists, and scalars, as appropriate.
</para>
</sect2>
+
+ <sect2 id="datatype-jsonpath">
+ <title>jsonpath Type</title>
+
+ <indexterm zone="datatype-jsonpath">
+ <primary>jsonpath</primary>
+ </indexterm>
+
+ <para>
+ The <type>jsonpath</type> type implements support for the SQL/JSON path language
+ in <productname>PostgreSQL</productname> to effectively query JSON data.
+ It provides a binary representation of the parsed SQL/JSON path
+ expression that specifies the items to be retrieved by the path
+ engine from the JSON data for further processing with the
+ SQL/JSON query functions.
+ </para>
+
+ <para>
+ The SQL/JSON path language is fully integrated into the SQL engine:
+ the semantics of its predicates and operators generally follow SQL.
+ At the same time, to provide a most natural way of working with JSON data,
+ SQL/JSON path syntax uses some of the JavaScript conventions:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ Dot <literal>.</literal> is used for member access.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Square brackets <literal>[]</literal> are used for array access.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ SQL/JSON arrays are 0-relative, unlike regular SQL arrays that start from 1.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <para>
+ An SQL/JSON path expression is an SQL character string literal,
+ so it must be enclosed in single quotes when passed to an SQL/JSON
+ query function. Following the JavaScript
+ conventions, character string literals within the path expression
+ must be enclosed in double quotes. Any single quotes within this
+ character string literal must be escaped with a single quote
+ by the SQL convention.
+ </para>
+
+ <para>
+ A path expression consists of a sequence of path elements,
+ which can be the following:
+ <itemizedlist>
+ <listitem>
+ <para>
+ Path literals of JSON primitive types:
+ Unicode text, numeric, true, false, or null.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Path variables listed in <xref linkend="type-jsonpath-variables"/>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Accessor operators listed in <xref linkend="type-jsonpath-accessors"/>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <type>jsonpath</type> operators and methods listed
+ in <xref linkend="functions-sqljson-path-operators"/>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Parentheses, which can be used to provide filter expressions
+ or define the order of path evaluation.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ For details on using <type>jsonpath</type> expressions with SQL/JSON
+ query functions, see <xref linkend="functions-sqljson-path"/>.
+ </para>
+
+ <table id="type-jsonpath-variables">
+ <title><type>jsonpath</type> Variables</title>
+ <tgroup cols="2">
+ <thead>
+ <row>
+ <entry>Variable</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>$</literal></entry>
+ <entry>A variable representing the JSON text to be queried
+ (the <firstterm>context item</firstterm>).
+ </entry>
+ </row>
+ <row>
+ <entry><literal>$varname</literal></entry>
+ <entry>A named variable. Its value must be set in the
+ <command>PASSING</command> clause of an SQL/JSON query function.
+ <!-- TBD: See <xref linkend="sqljson-input-clause"/> -->
+ for details.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>@</literal></entry>
+ <entry>A variable representing the result of path evaluation
+ in filter expressions.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <table id="type-jsonpath-accessors">
+ <title><type>jsonpath</type> Accessors</title>
+ <tgroup cols="2">
+ <thead>
+ <row>
+ <entry>Accessor Operator</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>
+ <para>
+ <literal>.<replaceable>key</replaceable></literal>
+ </para>
+ <para>
+ <literal>."$<replaceable>varname</replaceable>"</literal>
+ </para>
+ </entry>
+ <entry>
+ <para>
+ Member accessor that returns an object member with
+ the specified key. If the key name is a named variable
+ starting with <literal>$</literal> or does not meet the
+ JavaScript rules of an identifier, it must be enclosed in
+ double quotes as a character string literal.
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry>
+ <para>
+ <literal>.*</literal>
+ </para>
+ </entry>
+ <entry>
+ <para>
+ Wildcard member accessor that returns the values of all
+ members located at the top level of the current object.
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry>
+ <para>
+ <literal>.**</literal>
+ </para>
+ </entry>
+ <entry>
+ <para>
+ Recursive wildcard member accessor that processes all levels
+ of the JSON hierarchy of the current object and returns all
+ the member values, regardless of their nesting level. This
+ is a <productname>PostgreSQL</productname> extension of
+ the SQL/JSON standard.
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry>
+ <para>
+ <literal>[<replaceable>subscript</replaceable>, ...]</literal>
+ </para>
+ <para>
+ <literal>[<replaceable>subscript</replaceable> to last]</literal>
+ </para>
+ </entry>
+ <entry>
+ <para>
+ Array element accessor. The provided numeric subscripts return the
+ corresponding array elements. The first element in an array is
+ accessed with [0]. The <literal>last</literal> keyword denotes
+ the last subscript in an array and can be used to handle arrays
+ of unknown length.
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry>
+ <para>
+ <literal>[*]</literal>
+ </para>
+ </entry>
+ <entry>
+ <para>
+ Wildcard array element accessor that returns all array elements.
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect2>
</sect1>
Re: SQL/JSON: documentation
On Mon, Dec 03, 2018 at 07:23:09PM +0300, Liudmila Mantrova wrote:
Unfortunately, I couldn't find much time for this activity, but as far as I
understand, thread [1] only requires jsonpath documentation right now. So I
extracted the relevant parts from this patch, reworked path expression
description, and moved it to func.sgml as Peter suggested (attached). Nikita
is going to add this patch to the jsonpath thread together with the updated
code once it's ready.
For now the entry is marked as returned with feedback.
--
Michael