v7-0008-Documentation-for-SQL-JSON-features.patch
application/octet-stream
Filename: v7-0008-Documentation-for-SQL-JSON-features.patch
Type: application/octet-stream
Part: 4
Message:
Re: SQL/JSON revisited
Patch
Format: format-patch
Series: patch v7-0008
Subject: Documentation for SQL/JSON features
| File | + | − |
|---|---|---|
| doc/src/sgml/func.sgml | 1059 | 4 |
From 3536079cd7e700ebc243ca427fe92ec1376beaf2 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <andrew@dunslane.net>
Date: Thu, 7 Apr 2022 23:36:50 -0400
Subject: [PATCH v7 8/9] Documentation for SQL/JSON features
Nikita Glukhov
Reviewers have included (in no particular order) Andres Freund, Alexander
Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zhihong Yu,
Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby.
Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru
Discussion: https://postgr.es/m/7e2cb85d-24cf-4abb-30a5-1a33715959bd@postgrespro.ru
---
doc/src/sgml/func.sgml | 1063 +++++++++++++++++++++++++++++++++++++++-
1 file changed, 1059 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0cbdf63632..7acb321f7c 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17596,7 +17596,939 @@ $.* ? (@ like_regex "^\\d+$")
</programlisting>
</para>
</sect3>
- </sect2>
+ </sect2>
+
+ <sect2 id="functions-sqljson">
+ <title>SQL/JSON Functions and Expressions</title>
+ <indexterm zone="functions-json">
+ <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. The model is a formalization of the implied
+ data model in the JSON specification
+ <ulink url="https://tools.ietf.org/html/rfc7159">RFC 7159</ulink>.
+ </para>
+
+ <para>
+ SQL/JSON allows you to handle JSON data alongside regular SQL data,
+ with transaction support, including:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ Uploading JSON data into the database and storing it in
+ regular SQL columns as character or binary strings.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Generating JSON objects and arrays from relational data.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Querying JSON data using SQL/JSON query functions and
+ SQL/JSON path language expressions.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <para>
+ There are two groups of SQL/JSON functions.
+ <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>
+
+ <para>
+ Many SQL/JSON functions have an optional <literal>FORMAT</literal>
+ clause. This is provided to conform with the SQL standard, but has no
+ effect except where noted otherwise.
+ </para>
+
+ <para>
+ <xref linkend="functions-sqljson-producing" /> lists the SQL/JSON
+ Constructor functions. Each function has a <literal>RETURNING</literal>
+ clause specifying the data type returned. For the <function>json</function> and
+ <function>json_scalar</function> functions, this needs to be either <type>json</type> or
+ <type>jsonb</type>. For the other constructor functions it must be one of <type>json</type>,
+ <type>jsonb</type>, <type>bytea</type>, a character string type (<type>text</type>, <type>char</type>,
+ <type>varchar</type>, or <type>nchar</type>), or a type for which there is a cast
+ from <type>json</type> to that type.
+ By default, the <type>json</type> type is returned.
+ </para>
+
+ <note>
+ <para>
+ Many of the results that can be obtained from the SQL/JSON Constructor
+ functions can also be obtained by calling
+ <productname>PostgreSQL</productname>-specific functions detailed in
+ <xref linkend="functions-json-creation-table" /> and
+ <xref linkend="functions-aggregate-table"/>.
+ </para>
+ </note>
+
+ <table id="functions-sqljson-producing">
+ <title>SQL/JSON Constructor Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function signature
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para></entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>json constructor</primary></indexterm>
+ <function>json</function> (
+ <replaceable>expression</replaceable>
+ <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional></optional>
+ <optional> { <literal>WITH</literal> | <literal>WITHOUT</literal> } <literal>UNIQUE</literal> <optional> <literal>KEYS</literal> </optional></optional>
+ <optional> <literal>RETURNING</literal> <replaceable>json_data_type</replaceable> </optional>)
+ </para>
+ <para>
+ The <replaceable>expression</replaceable> can be any text type or a
+ <type>bytea</type> in UTF8 encoding. If the
+ <replaceable>expression</replaceable> is NULL, an
+ <acronym>SQL</acronym> null value is returned.
+ If <literal>WITH UNIQUE</literal> is specified, the
+ <replaceable>expression</replaceable> must not contain any duplicate
+ object keys.
+ </para>
+ <para>
+ <literal>json('{"a":123, "b":[true,"foo"], "a":"bar"}')</literal>
+ <returnvalue>{"a":123, "b":[true,"foo"], "a":"bar"}</returnvalue>
+ </para>
+ <para>
+ <literal>json('{"a":123,"b":[true,"foo"],"a":"bar"}' returning jsonb)</literal>
+ <returnvalue>{"a": "bar", "b": [true, "foo"]}</returnvalue>
+ </para></entry>
+ </row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>json_scalar</primary></indexterm>
+ <function>json_scalar</function> (<replaceable>expression</replaceable>
+ <optional> <literal>RETURNING</literal> <replaceable>json_data_type</replaceable> </optional>)
+ </para>
+ <para>
+ Returns a JSON scalar value representing
+ <replaceable>expression</replaceable>.
+ If the input is NULL, an SQL NULL is returned. If the input is a number
+ or a boolean value, a corresponding JSON number or boolean value is
+ returned. For any other value a JSON string is returned.
+ </para>
+ <para>
+ <literal>json_scalar(123.45)</literal>
+ <returnvalue>123.45</returnvalue>
+ </para>
+ <para>
+ <literal>json_scalar(CURRENT_TIMESTAMP)</literal>
+ <returnvalue>"2022-05-10T10:51:04.62128-04:00"</returnvalue>
+ </para></entry>
+ </row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>json_object</primary></indexterm>
+ <function>json_object</function> (
+ <optional> { <replaceable>key_expression</replaceable> { <literal>VALUE</literal> | ':' }
+ <replaceable>value_expression</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> }<optional>, ...</optional> </optional>
+ <optional> { <literal>NULL</literal> | <literal>ABSENT</literal> } <literal>ON NULL</literal> </optional>
+ <optional> { <literal>WITH</literal> | <literal>WITHOUT</literal> } <literal>UNIQUE</literal> <optional> <literal>KEYS</literal> </optional> </optional>
+ <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>)
+ </para>
+ <para>
+ Constructs a JSON object of all the key value pairs given,
+ or an empty object if none are given.
+ <replaceable>key_expression</replaceable> is a scalar expression
+ defining the <acronym>JSON</acronym> key, which is
+ converted to the <type>text</type> type.
+ It cannot be <literal>NULL</literal> nor can it
+ belong to a type that has a cast to the <type>json</type>.
+ If <literal>WITH UNIQUE</literal> is specified, there must not
+ be any duplicate <replaceable>key_expression</replaceable>.
+ If <literal>ABSENT ON NULL</literal> is specified, the entire
+ pair is omitted if the <replaceable>value_expression</replaceable>
+ is <literal>NULL</literal>.
+ </para>
+ <para>
+ <literal>json_object('code' VALUE 'P123', 'title': 'Jaws')</literal>
+ <returnvalue>{"code" : "P123", "title" : "Jaws"}</returnvalue>
+ </para></entry>
+ </row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>json_objectagg</primary></indexterm>
+ <function>json_objectagg</function> (
+ <optional> { <replaceable>key_expression</replaceable> { <literal>VALUE</literal> | ':' } <replaceable>value_expression</replaceable> } </optional>
+ <optional> { <literal>NULL</literal> | <literal>ABSENT</literal> } <literal>ON NULL</literal> </optional>
+ <optional> { <literal>WITH</literal> | <literal>WITHOUT</literal> } <literal>UNIQUE</literal> <optional> <literal>KEYS</literal> </optional> </optional>
+ <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>)
+ </para>
+ <para>
+ Behaves like <function>json_object</function> above, but as an
+ aggregate function, so it only takes one
+ <replaceable>key_expression</replaceable> and one
+ <replaceable>value_expression</replaceable> parameter.
+ </para>
+ <para>
+ <literal>SELECT json_objectagg(k:v) FROM (VALUES ('a'::text,current_date),('b',current_date + 1)) AS t(k,v)</literal>
+ <returnvalue>{ "a" : "2022-05-10", "b" : "2022-05-11" }</returnvalue>
+ </para></entry>
+ </row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>json_array</primary></indexterm>
+ <function>json_array</function> (
+ <optional> { <replaceable>value_expression</replaceable> <optional> <literal>FORMAT JSON</literal> </optional> } <optional>, ...</optional> </optional>
+ <optional> { <literal>NULL</literal> | <literal>ABSENT</literal> } <literal>ON NULL</literal> </optional>
+ <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>)
+ </para>
+ <para role="func_signature">
+ <function>json_array</function> (
+ <optional> <replaceable>query_expression</replaceable> </optional>
+ <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>)
+ </para>
+ <para>
+ Constructs a JSON array from either a series of
+ <replaceable>value_expression</replaceable> parameters or from the results
+ of <replaceable>query_expression</replaceable>,
+ which must be a SELECT query returning a single column. If
+ <literal>ABSENT ON NULL</literal> is specified, NULL values are ignored.
+ This is always the case if a
+ <replaceable>query_expression</replaceable> is used.
+ </para>
+ <para>
+ <literal>json_array(1,true,json '{"a":null}')</literal>
+ <returnvalue>[1, true, {"a":null}]</returnvalue>
+ </para>
+ <para>
+ <literal>json_array(SELECT * FROM (VALUES(1),(2)) t)</literal>
+ <returnvalue>[1, 2]</returnvalue>
+ </para></entry>
+ </row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>json_arrayagg</primary></indexterm>
+ <function>json_arrayagg</function> (
+ <optional> <replaceable>value_expression</replaceable> </optional>
+ <optional> <literal>ORDER BY</literal> <replaceable>sort_expression</replaceable> </optional>
+ <optional> { <literal>NULL</literal> | <literal>ABSENT</literal> } <literal>ON NULL</literal> </optional>
+ <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>)
+ </para>
+ <para>
+ Behaves in the same way as <function>json_array</function>
+ but as an aggregate function so it only takes one
+ <replaceable>value_expression</replaceable> parameter.
+ If <literal>ABSENT ON NULL</literal> is specified, any NULL
+ values are omitted.
+ If <literal>ORDER BY</literal> is specified, the elements will
+ appear in the array in that order rather than in the input order.
+ </para>
+ <para>
+ <literal>SELECT json_arrayagg(v) FROM (VALUES(2),(1)) t(v)</literal>
+ <returnvalue>[2, 1]</returnvalue>
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ <xref linkend="functions-sqljson-misc" /> details SQL/JSON
+ facilities for testing and serializing JSON.
+ </para>
+
+ <table id="functions-sqljson-misc">
+ <title>SQL/JSON Testing and Serializing Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function signature
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para></entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>IS JSON</primary></indexterm>
+ <replaceable>expression</replaceable> <literal>IS</literal> <optional> <literal>NOT</literal> </optional> <literal>JSON</literal>
+ <optional> { <literal>VALUE</literal> | <literal>SCALAR</literal> | <literal>ARRAY</literal> | <literal>OBJECT</literal> } </optional>
+ <optional> { <literal>WITH</literal> | <literal>WITHOUT</literal> } <literal>UNIQUE</literal> <optional> <literal>KEYS</literal> </optional> </optional>
+ </para>
+ <para>
+ This predicate tests whether <replaceable>expression</replaceable> can be
+ parsed as JSON, possibly of a specified type.
+ If <literal>SCALAR</literal> or <literal>ARRAY</literal> or
+ <literal>OBJECT</literal> is specified, the
+ test is whether or not the JSON is of that particular type. If
+ <literal>WITH UNIQUE</literal> is specified, then any object in the
+ <replaceable>expression</replaceable> is also tested to see if it
+ has duplicate keys.
+ </para>
+ <para>
+<screen>
+SELECT js,
+ js IS JSON "json?",
+ js IS JSON SCALAR "scalar?",
+ js IS JSON OBJECT "object?",
+ js IS JSON ARRAY "array?"
+FROM
+(VALUES ('123'), ('"abc"'), ('{"a": "b"}'),
+('[1,2]'),('abc')) foo(js);
+ js | json? | scalar? | object? | array?
+------------+-------+---------+---------+--------
+ 123 | t | t | f | f
+ "abc" | t | t | f | f
+ {"a": "b"} | t | f | t | f
+ [1,2] | t | f | f | t
+ abc | f | f | f | f
+</screen>
+ </para></entry>
+ </row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>json_serialize</function> (
+ <replaceable>expression</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional>
+ <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>)
+ </para>
+ <para>
+ Transforms an SQL/JSON value into a character or binary string. The
+ <replaceable>expression</replaceable> can be of any JSON type, any
+ character string type, or <type>bytea</type> in UTF8 encoding.
+ The returned type can be any character string type or
+ <type>bytea</type>. The default is <type>text</type>.
+ </para>
+ <para>
+ <literal>json_serialize('{ "a" : 1 } ' RETURNING bytea)</literal>
+ <returnvalue>\x7b20226122203a2031207d20</returnvalue>
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ <xref linkend="functions-sqljson-querying"/> details the SQL/JSON
+ functions that can be used to query JSON data, except
+ for <function>json_table</function>.
+ </para>
+
+ <note>
+ <para>
+ SQL/JSON paths can only be applied to the <type>jsonb</type> type, so it
+ might be necessary to cast the <replaceable>context_item</replaceable>
+ argument of these functions to <type>jsonb</type>.
+ </para>
+ </note>
+
+ <table id="functions-sqljson-querying">
+ <title>SQL/JSON Query Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function signature
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para></entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>json_exists</primary></indexterm>
+ <function>json_exists</function> (
+ <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
+ <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> </optional>
+ <optional> { <literal>TRUE</literal> | <literal>FALSE</literal> |<literal> UNKNOWN</literal> | <literal>ERROR</literal> } <literal>ON ERROR</literal> </optional>)
+ </para>
+ <para>
+ Returns true if the SQL/JSON <replaceable>path_expression</replaceable>
+ applied to the <replaceable>context_item</replaceable> using the
+ <replaceable>value</replaceable>s yields any items.
+ The <literal>ON ERROR</literal> clause specifies what is returned if
+ an error occurs. Note that if the <replaceable>path_expression</replaceable>
+ is <literal>strict</literal>, an error is generated if it yields no items.
+ The default value is <literal>UNKNOWN</literal> which causes a NULL
+ result.
+ </para>
+ <para>
+ <literal>json_exists(jsonb '{"key1": [1,2,3]}', 'strict $.key1[*] ? (@ > 2)')</literal>
+ <returnvalue>t</returnvalue>
+ </para>
+ <para>
+ <literal>json_exists(jsonb '{"a": [1,2,3]}', 'lax $.a[5]' ERROR ON ERROR)</literal>
+ <returnvalue>f</returnvalue>
+ </para>
+ <para>
+ <literal>json_exists(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR)</literal>
+ <returnvalue>ERROR: jsonpath array subscript is out of bounds</returnvalue>
+ </para></entry>
+ </row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>json_value</primary></indexterm>
+ <function>json_value</function> (
+ <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable>
+ <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
+ <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> </optional>
+ <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON EMPTY</literal> </optional>
+ <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON ERROR</literal> </optional>)
+ </para>
+ <para>
+ Returns the result of applying the
+ <replaceable>path_expression</replaceable> to the
+ <replaceable>context_item</replaceable> using the
+ <replaceable>value</replaceable>s. The extracted value must be
+ a single <acronym>SQL/JSON</acronym> scalar item. For results that
+ are objects or arrays, use the <function>json_query</function>
+ function instead.
+ The returned <replaceable>data_type</replaceable> has the same semantics
+ as for constructor functions like <function>json_objectagg</function>.
+ The default returned type is <type>text</type>.
+ The <literal>ON EMPTY</literal> clause specifies the behavior if the
+ <replaceable>path_expression</replaceable> yields no value at all.
+ The <literal>ON ERROR</literal> clause specifies the behavior if an
+ error occurs as a result of <type>jsonpath</type> evaluation
+ (including cast to the output type) or execution of
+ <literal>ON EMPTY</literal> behavior (that was caused by empty result
+ of <type>jsonpath</type> evaluation).
+ </para>
+ <para>
+ <literal>json_value(jsonb '"123.45"', '$' RETURNING float)</literal>
+ <returnvalue>123.45</returnvalue>
+ </para>
+ <para>
+ <literal>json_value(jsonb '"03:04 2015-02-01"', '$.datetime("HH24:MI YYYY-MM-DD")' RETURNING date)</literal>
+ <returnvalue>2015-02-01</returnvalue>
+ </para>
+ <para>
+ <literal>json_value(jsonb '[1,2]', 'strict $[*]' DEFAULT 9 ON ERROR)</literal>
+ <returnvalue>9</returnvalue>
+ </para></entry>
+ </row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>json_query</primary></indexterm>
+ <function>json_query</function> (
+ <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
+ <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>
+ <optional> { <literal>WITHOUT</literal> | <literal>WITH</literal> { <literal>CONDITIONAL</literal> | <optional><literal>UNCONDITIONAL</literal></optional> } } <optional> <literal>ARRAY</literal> </optional> <literal>WRAPPER</literal> </optional>
+ <optional> { <literal>KEEP</literal> | <literal>OMIT</literal> } <literal>QUOTES</literal> <optional> <literal>ON SCALAR STRING</literal> </optional> </optional>
+ <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON EMPTY</literal> </optional>
+ <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON ERROR</literal> </optional>)
+ </para>
+ <para>
+ Returns the result of applying the
+ <replaceable>path_expression</replaceable> to the
+ <replaceable>context_item</replaceable> using the
+ <replaceable>value</replaceable>s.
+ This function must return a JSON string, so if the path expression
+ returns multiple SQL/JSON items, you must wrap the result using the
+ <literal>WITH WRAPPER</literal> clause. If the wrapper is
+ <literal>UNCONDITIONAL</literal>, an array wrapper will always
+ be applied, even if the returned value is already a single JSON object
+ or array, but if it is <literal>CONDITIONAL</literal>, it will not be
+ applied to a single array or object. <literal>UNCONDITIONAL</literal>
+ is the default.
+ If the result is a scalar string, by default the value returned will have
+ surrounding quotes making it a valid JSON value. However, this behavior
+ is reversed if <literal>OMIT QUOTES</literal> is specified.
+ The <literal>ON ERROR</literal> and <literal>ON EMPTY</literal>
+ clauses have similar semantics to those clauses for
+ <function>json_value</function>.
+ The returned <replaceable>data_type</replaceable> has the same semantics
+ as for constructor functions like <function>json_objectagg</function>.
+ The default returned type is <type>text</type>.
+ </para>
+ <para>
+ <literal>json_query(jsonb '[1,[2,3],null]', 'lax $[*][1]' WITH CONDITIONAL WRAPPER)</literal>
+ <returnvalue>[3]</returnvalue>
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect2>
+
+ <sect2 id="functions-sqljson-table">
+ <title>JSON_TABLE</title>
+ <indexterm>
+ <primary>json_table</primary>
+ </indexterm>
+
+ <para>
+ <function>json_table</function> is an SQL/JSON function which
+ 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 a <literal>SELECT</literal> statement.
+ </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>
+
+ <para>
+ Each <literal>NESTED PATH</literal> clause can generate one or more
+ columns. Columns produced by <literal>NESTED PATH</literal>s at the
+ same level are considered to be <firstterm>siblings</firstterm>,
+ while a column produced by a <literal>NESTED PATH</literal> is
+ considered to be a child of the column produced by a
+ <literal>NESTED PATH</literal> or row expression at a higher level.
+ Sibling columns are always joined first. Once they are processed,
+ the resulting rows are joined to the parent row.
+ </para>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal><replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> <literal>AS</literal> <replaceable>json_path_name</replaceable> </optional> <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional></literal>
+ </term>
+ <listitem>
+ <para>
+ The input data to query, the JSON path expression defining the query,
+ and an optional <literal>PASSING</literal> clause, which can provide data
+ values to the <replaceable>path_expression</replaceable>.
+ 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</literal>( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
+ </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.
+ The <replaceable>json_table_column</replaceable>
+ expression has the following syntax variants:
+ </para>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <literal><replaceable>name</replaceable> <replaceable>type</replaceable>
+ <optional> <literal>PATH</literal> <replaceable>json_path_specification</replaceable> </optional></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.
+ <literal>WRAPPER</literal> and <literal>QUOTES</literal> clauses can only
+ be used with JSON, array, and composite types.
+ These clauses have the same syntax and semantics as for
+ <function>json_value</function> and <function>json_query</function>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <replaceable>name</replaceable> <replaceable>type</replaceable> <literal>FORMAT</literal> <replaceable>json_representation</replaceable>
+ <optional> <literal>PATH</literal> <replaceable>json_path_specification</replaceable> </optional>
+ </term>
+ <listitem>
+
+ <para>
+ Generates 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
+ for <function>json_query</function>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <replaceable>name</replaceable> <replaceable>type</replaceable>
+ <literal>EXISTS</literal> <optional> <literal>PATH</literal> <replaceable>json_path_specification</replaceable> </optional>
+ </term>
+ <listitem>
+
+ <para>
+ Generates a column and inserts a boolean 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>,
+ checks whether any SQL/JSON items were returned, and fills the column with
+ resulting boolean value, one for each row.
+ The specified <replaceable>type</replaceable> should have cast from
+ <type>boolean</type>.
+ 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.
+ </para>
+ <para>
+ Optionally, you can add <literal>ON ERROR</literal> clause to define
+ error behavior. This clause has the same syntax and semantics as
+ for <function>json_exists</function>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>NESTED PATH</literal> <replaceable>json_path_specification</replaceable> <optional> <literal>AS</literal> <replaceable>json_path_name</replaceable> </optional>
+ <literal>COLUMNS</literal> ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
+ </term>
+ <listitem>
+
+ <para>
+ Extracts SQL/JSON items from nested levels of the row pattern,
+ generates 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 <literal>NESTED PATH</literal> clauses.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <replaceable>name</replaceable> <literal>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>AS</literal> <replaceable>json_path_name</replaceable>
+ </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 distinct from the 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</literal> ( <replaceable>json_table_plan</replaceable> )
+ </term>
+ <listitem>
+
+ <para>
+ Defines how to join the data returned by <literal>NESTED PATH</literal>
+ clauses to the constructed view.
+ </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>
+ Generate one row for each value produced by each of the sibling
+ columns. The columns from the other siblings are set to null.
+ </para>
+ <para>
+ This is the default option for joining sibling columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>CROSS</literal>
+ </term>
+ <listitem>
+
+ <para>
+ Generate one row for each combination of values from the sibling columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>PLAN DEFAULT</literal> ( <literal><replaceable>OUTER | INNER</replaceable> <optional>, <replaceable>UNION | CROSS</replaceable> </optional></literal> )
+ </term>
+ <listitem>
+ <para>
+ The terms can also be specified in reverse order. The
+ <literal>INNER</literal> or <literal>OUTER</literal> option defines the
+ joining plan for parent/child columns, while <literal>UNION</literal> or
+ <literal>CROSS</literal> affects joins of sibling columns. This form
+ of <literal>PLAN</literal> overrides the default plan for
+ all columns at once. Even though the path names are not included in the
+ <literal>PLAN DEFAULT</literal> form, to conform to the SQL/JSON standard
+ they must be provided for all the paths if the <literal>PLAN</literal>
+ clause is used.
+ </para>
+ <para>
+ <literal>PLAN DEFAULT</literal> is simpler than specifying a complete
+ <literal>PLAN</literal>, and is often all that is required to get the desired
+ output.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <para>Examples</para>
+
+ <para>
+ In these examples the following small table storing some JSON data will be used:
+<programlisting>
+CREATE TABLE my_films ( js jsonb );
+
+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>
+ <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 | Alfred 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;
+
+ director | title1 | kind1 | title2 | kind2
+------------------+---------+----------+--------+--------
+ Alfred Hitchcock | Vertigo | thriller | Psycho | horror
+(1 row)
+</screen>
+ </para>
+ </sect2>
+
</sect1>
<sect1 id="functions-sequence">
@@ -19988,6 +20920,29 @@ SELECT NULLIF(value, '(none)') ...
<entry>No</entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>json_agg_strict</primary>
+ </indexterm>
+ <function>json_agg_strict</function> ( <type>anyelement</type> )
+ <returnvalue>json</returnvalue>
+ </para>
+ <para role="func_signature">
+ <indexterm>
+ <primary>jsonb_agg_strict</primary>
+ </indexterm>
+ <function>jsonb_agg_strict</function> ( <type>anyelement</type> )
+ <returnvalue>jsonb</returnvalue>
+ </para>
+ <para>
+ Collects all the input values, skipping nulls, into a JSON array.
+ Values are converted to JSON as per <function>to_json</function>
+ or <function>to_jsonb</function>.
+ </para></entry>
+ <entry>No</entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -20009,9 +20964,97 @@ SELECT NULLIF(value, '(none)') ...
</para>
<para>
Collects all the key/value pairs into a JSON object. Key arguments
- are coerced to text; value arguments are converted as
- per <function>to_json</function> or <function>to_jsonb</function>.
- Values can be null, but not keys.
+ are coerced to text; value arguments are converted as per
+ <function>to_json</function> or <function>to_jsonb</function>
+ Values can be null, but keys cannot.
+ </para></entry>
+ <entry>No</entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>json_object_agg_strict</primary>
+ </indexterm>
+ <function>json_object_agg_strict</function> (
+ <parameter>key</parameter> <type>"any"</type>,
+ <parameter>value</parameter> <type>"any"</type> )
+ <returnvalue>json</returnvalue>
+ </para>
+ <para role="func_signature">
+ <indexterm>
+ <primary>jsonb_object_agg_strict</primary>
+ </indexterm>
+ <function>jsonb_object_agg_strict</function> (
+ <parameter>key</parameter> <type>"any"</type>,
+ <parameter>value</parameter> <type>"any"</type> )
+ <returnvalue>jsonb</returnvalue>
+ </para>
+ <para>
+ Collects all the key/value pairs into a JSON object. Key arguments
+ are coerced to text; value arguments are converted as per
+ <function>to_json</function> or <function>to_jsonb</function>.
+ The <parameter>key</parameter> can not be null. If the
+ <parameter>value</parameter> is null then the entry is skipped,
+ </para></entry>
+ <entry>No</entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>json_object_agg_unique</primary>
+ </indexterm>
+ <function>json_object_agg_unique</function> (
+ <parameter>key</parameter> <type>"any"</type>,
+ <parameter>value</parameter> <type>"any"</type> )
+ <returnvalue>json</returnvalue>
+ </para>
+ <para role="func_signature">
+ <indexterm>
+ <primary>jsonb_object_agg_unique</primary>
+ </indexterm>
+ <function>jsonb_object_agg_unique</function> (
+ <parameter>key</parameter> <type>"any"</type>,
+ <parameter>value</parameter> <type>"any"</type> )
+ <returnvalue>jsonb</returnvalue>
+ </para>
+ <para>
+ Collects all the key/value pairs into a JSON object. Key arguments
+ are coerced to text; value arguments are converted as per
+ <function>to_json</function> or <function>to_jsonb</function>.
+ Values can be null, but keys cannot.
+ If there is a duplicate key an error is thrown.
+ </para></entry>
+ <entry>No</entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>json_object_agg_unique_strict</primary>
+ </indexterm>
+ <function>json_object_agg_unique_strict</function> (
+ <parameter>key</parameter> <type>"any"</type>,
+ <parameter>value</parameter> <type>"any"</type> )
+ <returnvalue>json</returnvalue>
+ </para>
+ <para role="func_signature">
+ <indexterm>
+ <primary>jsonb_object_agg_unique_strict</primary>
+ </indexterm>
+ <function>jsonb_object_agg_unique_strict</function> (
+ <parameter>key</parameter> <type>"any"</type>,
+ <parameter>value</parameter> <type>"any"</type> )
+ <returnvalue>jsonb</returnvalue>
+ </para>
+ <para>
+ Collects all the key/value pairs into a JSON object. Key arguments
+ are coerced to text; value arguments are converted as per
+ <function>to_json</function> or <function>to_jsonb</function>.
+ The <parameter>key</parameter> can not be null. If the
+ <parameter>value</parameter> is null then the entry is skipped,
+ If there is a duplicate key an error is thrown.
</para></entry>
<entry>No</entry>
</row>
@@ -20189,7 +21232,12 @@ SELECT NULLIF(value, '(none)') ...
<para>
The aggregate functions <function>array_agg</function>,
<function>json_agg</function>, <function>jsonb_agg</function>,
+ <function>json_agg_strict</function>, <function>jsonb_agg_strict</function>,
<function>json_object_agg</function>, <function>jsonb_object_agg</function>,
+ <function>json_object_agg_strict</function>, <function>jsonb_object_agg_strict</function>,
+ <function>json_object_agg_unique</function>, <function>jsonb_object_agg_unique</function>,
+ <function>json_object_agg_unique_strict</function>,
+ <function>jsonb_object_agg_unique_strict</function>,
<function>string_agg</function>,
and <function>xmlagg</function>, as well as similar user-defined
aggregate functions, produce meaningfully different result values
@@ -20209,6 +21257,13 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
subquery's output to be reordered before the aggregate is computed.
</para>
+ <note>
+ <para>
+ In addition to the JSON aggregates shown here, see the <function>json_objectagg</function>
+ and <function>json_arrayagg</function> constructors in <xref linkend="functions-sqljson"/>.
+ </para>
+ </note>
+
<note>
<indexterm>
<primary>ANY</primary>
--
2.35.3