Adding upstream version 15.4.upstream/15.4 upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-16 19:46:48 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-16 19:46:48 +0000
commit: 311bcfc6b3acdd6fd152798c7f287ddf74fa2a98 (patch)
tree: 0ec307299b1dada3701e42f4ca6eda57d708261e /doc/src/sgml/html/functions-json.html
parent: Initial commit. (diff)
download: postgresql-15-upstream.tar.xz
postgresql-15-upstream.zip
1 files changed, 1781 insertions, 0 deletions
diff --git a/doc/src/sgml/html/functions-json.html b/doc/src/sgml/html/functions-json.html
new file mode 100644
index 0000000..eff9140
--- /dev/null
+++ b/doc/src/sgml/html/functions-json.html
@@ -0,0 +1,1781 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.16. JSON Functions and Operators</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-xml.html" title="9.15. XML Functions" /><link rel="next" href="functions-sequence.html" title="9.17. Sequence Manipulation Functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.16. JSON Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-xml.html" title="9.15. XML Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-sequence.html" title="9.17. Sequence Manipulation Functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-JSON"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.16. JSON Functions and Operators</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="functions-json.html#FUNCTIONS-JSON-PROCESSING">9.16.1. Processing and Creating JSON Data</a></span></dt><dt><span class="sect2"><a href="functions-json.html#FUNCTIONS-SQLJSON-PATH">9.16.2. The SQL/JSON Path Language</a></span></dt></dl></div><a id="id-1.5.8.22.2" class="indexterm"></a><p>
+   This section describes:
+
+   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
+      functions and operators for processing and creating JSON data
+     </p></li><li class="listitem"><p>
+      the SQL/JSON path language
+     </p></li></ul></div><p>
+  </p><p>
+   To learn more about the SQL/JSON standard, see
+   <a class="xref" href="biblio.html#SQLTR-19075-6" title="SQL Technical Report">[sqltr-19075-6]</a>. For details on JSON types
+   supported in <span class="productname">PostgreSQL</span>,
+   see <a class="xref" href="datatype-json.html" title="8.14. JSON Types">Section 8.14</a>.
+  </p><div class="sect2" id="FUNCTIONS-JSON-PROCESSING"><div class="titlepage"><div><div><h3 class="title">9.16.1. Processing and Creating JSON Data</h3></div></div></div><p>
+   <a class="xref" href="functions-json.html#FUNCTIONS-JSON-OP-TABLE" title="Table 9.45. json and jsonb Operators">Table 9.45</a> shows the operators that
+   are available for use with JSON data types (see <a class="xref" href="datatype-json.html" title="8.14. JSON Types">Section 8.14</a>).
+   In addition, the usual comparison operators shown in <a class="xref" href="functions-comparison.html#FUNCTIONS-COMPARISON-OP-TABLE" title="Table 9.1. Comparison Operators">Table 9.1</a> are available for
+   <code class="type">jsonb</code>, though not for <code class="type">json</code>.  The comparison
+   operators follow the ordering rules for B-tree operations outlined in
+   <a class="xref" href="datatype-json.html#JSON-INDEXING" title="8.14.4. jsonb Indexing">Section 8.14.4</a>.
+   See also <a class="xref" href="functions-aggregate.html" title="9.21. Aggregate Functions">Section 9.21</a> for the aggregate
+   function <code class="function">json_agg</code> which aggregates record
+   values as JSON, the aggregate function
+   <code class="function">json_object_agg</code> which aggregates pairs of values
+   into a JSON object, and their <code class="type">jsonb</code> equivalents,
+   <code class="function">jsonb_agg</code> and <code class="function">jsonb_object_agg</code>.
+  </p><div class="table" id="FUNCTIONS-JSON-OP-TABLE"><p class="title"><strong>Table 9.45. <code class="type">json</code> and <code class="type">jsonb</code> Operators</strong></p><div class="table-contents"><table class="table" summary="json and jsonb Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
+        Operator
+       </p>
+       <p>
+        Description
+       </p>
+       <p>
+        Example(s)
+       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">json</code> <code class="literal">-&gt;</code> <code class="type">integer</code>
+        → <code class="returnvalue">json</code>
+       </p>
+       <p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">-&gt;</code> <code class="type">integer</code>
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Extracts <em class="parameter"><code>n</code></em>'th element of JSON array
+        (array elements are indexed from zero, but negative integers count
+        from the end).
+       </p>
+       <p>
+        <code class="literal">'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -&gt; 2</code>
+        → <code class="returnvalue">{"c":"baz"}</code>
+       </p>
+       <p>
+        <code class="literal">'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -&gt; -3</code>
+        → <code class="returnvalue">{"a":"foo"}</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">json</code> <code class="literal">-&gt;</code> <code class="type">text</code>
+        → <code class="returnvalue">json</code>
+       </p>
+       <p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">-&gt;</code> <code class="type">text</code>
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Extracts JSON object field with the given key.
+       </p>
+       <p>
+        <code class="literal">'{"a": {"b":"foo"}}'::json -&gt; 'a'</code>
+        → <code class="returnvalue">{"b":"foo"}</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">json</code> <code class="literal">-&gt;&gt;</code> <code class="type">integer</code>
+        → <code class="returnvalue">text</code>
+       </p>
+       <p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">-&gt;&gt;</code> <code class="type">integer</code>
+        → <code class="returnvalue">text</code>
+       </p>
+       <p>
+        Extracts <em class="parameter"><code>n</code></em>'th element of JSON array,
+        as <code class="type">text</code>.
+       </p>
+       <p>
+        <code class="literal">'[1,2,3]'::json -&gt;&gt; 2</code>
+        → <code class="returnvalue">3</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">json</code> <code class="literal">-&gt;&gt;</code> <code class="type">text</code>
+        → <code class="returnvalue">text</code>
+       </p>
+       <p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">-&gt;&gt;</code> <code class="type">text</code>
+        → <code class="returnvalue">text</code>
+       </p>
+       <p>
+        Extracts JSON object field with the given key, as <code class="type">text</code>.
+       </p>
+       <p>
+        <code class="literal">'{"a":1,"b":2}'::json -&gt;&gt; 'b'</code>
+        → <code class="returnvalue">2</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">json</code> <code class="literal">#&gt;</code> <code class="type">text[]</code>
+        → <code class="returnvalue">json</code>
+       </p>
+       <p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">#&gt;</code> <code class="type">text[]</code>
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Extracts JSON sub-object at the specified path, where path elements
+        can be either field keys or array indexes.
+       </p>
+       <p>
+        <code class="literal">'{"a": {"b": ["foo","bar"]}}'::json #&gt; '{a,b,1}'</code>
+        → <code class="returnvalue">"bar"</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">json</code> <code class="literal">#&gt;&gt;</code> <code class="type">text[]</code>
+        → <code class="returnvalue">text</code>
+       </p>
+       <p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">#&gt;&gt;</code> <code class="type">text[]</code>
+        → <code class="returnvalue">text</code>
+       </p>
+       <p>
+        Extracts JSON sub-object at the specified path as <code class="type">text</code>.
+       </p>
+       <p>
+        <code class="literal">'{"a": {"b": ["foo","bar"]}}'::json #&gt;&gt; '{a,b,1}'</code>
+        → <code class="returnvalue">bar</code>
+       </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>
+    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 key or array element exists.
+   </p></div><p>
+   Some further operators exist only for <code class="type">jsonb</code>, as shown
+   in <a class="xref" href="functions-json.html#FUNCTIONS-JSONB-OP-TABLE" title="Table 9.46. Additional jsonb Operators">Table 9.46</a>.
+   <a class="xref" href="datatype-json.html#JSON-INDEXING" title="8.14.4. jsonb Indexing">Section 8.14.4</a>
+   describes how these operators can be used to effectively search indexed
+   <code class="type">jsonb</code> data.
+  </p><div class="table" id="FUNCTIONS-JSONB-OP-TABLE"><p class="title"><strong>Table 9.46. Additional <code class="type">jsonb</code> Operators</strong></p><div class="table-contents"><table class="table" summary="Additional jsonb Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
+        Operator
+       </p>
+       <p>
+        Description
+       </p>
+       <p>
+        Example(s)
+       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">@&gt;</code> <code class="type">jsonb</code>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Does the first JSON value contain the second?
+        (See <a class="xref" href="datatype-json.html#JSON-CONTAINMENT" title="8.14.3. jsonb Containment and Existence">Section 8.14.3</a> for details about containment.)
+       </p>
+       <p>
+        <code class="literal">'{"a":1, "b":2}'::jsonb @&gt; '{"b":2}'::jsonb</code>
+        → <code class="returnvalue">t</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">&lt;@</code> <code class="type">jsonb</code>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Is the first JSON value contained in the second?
+       </p>
+       <p>
+        <code class="literal">'{"b":2}'::jsonb &lt;@ '{"a":1, "b":2}'::jsonb</code>
+        → <code class="returnvalue">t</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">?</code> <code class="type">text</code>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Does the text string exist as a top-level key or array element within
+        the JSON value?
+       </p>
+       <p>
+        <code class="literal">'{"a":1, "b":2}'::jsonb ? 'b'</code>
+        → <code class="returnvalue">t</code>
+       </p>
+       <p>
+        <code class="literal">'["a", "b", "c"]'::jsonb ? 'b'</code>
+        → <code class="returnvalue">t</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">?|</code> <code class="type">text[]</code>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Do any of the strings in the text array exist as top-level keys or
+        array elements?
+       </p>
+       <p>
+        <code class="literal">'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd']</code>
+        → <code class="returnvalue">t</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">?&amp;</code> <code class="type">text[]</code>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Do all of the strings in the text array exist as top-level keys or
+        array elements?
+       </p>
+       <p>
+        <code class="literal">'["a", "b", "c"]'::jsonb ?&amp; array['a', 'b']</code>
+        → <code class="returnvalue">t</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">||</code> <code class="type">jsonb</code>
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Concatenates two <code class="type">jsonb</code> values.
+        Concatenating two arrays generates an array containing all the
+        elements of each input.  Concatenating two objects generates an
+        object containing the union of their
+        keys, taking the second object's value when there are duplicate keys.
+        All other cases are treated by converting a non-array input into a
+        single-element array, and then proceeding as for two arrays.
+        Does not operate recursively: only the top-level array or object
+        structure is merged.
+       </p>
+       <p>
+        <code class="literal">'["a", "b"]'::jsonb || '["a", "d"]'::jsonb</code>
+        → <code class="returnvalue">["a", "b", "a", "d"]</code>
+       </p>
+       <p>
+        <code class="literal">'{"a": "b"}'::jsonb || '{"c": "d"}'::jsonb</code>
+        → <code class="returnvalue">{"a": "b", "c": "d"}</code>
+       </p>
+       <p>
+        <code class="literal">'[1, 2]'::jsonb || '3'::jsonb</code>
+        → <code class="returnvalue">[1, 2, 3]</code>
+       </p>
+       <p>
+        <code class="literal">'{"a": "b"}'::jsonb || '42'::jsonb</code>
+        → <code class="returnvalue">[{"a": "b"}, 42]</code>
+       </p>
+       <p>
+        To append an array to another array as a single entry, wrap it
+        in an additional layer of array, for example:
+       </p>
+       <p>
+        <code class="literal">'[1, 2]'::jsonb || jsonb_build_array('[3, 4]'::jsonb)</code>
+        → <code class="returnvalue">[1, 2, [3, 4]]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">-</code> <code class="type">text</code>
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Deletes a key (and its value) from a JSON object, or matching string
+        value(s) from a JSON array.
+       </p>
+       <p>
+        <code class="literal">'{"a": "b", "c": "d"}'::jsonb - 'a'</code>
+        → <code class="returnvalue">{"c": "d"}</code>
+       </p>
+       <p>
+        <code class="literal">'["a", "b", "c", "b"]'::jsonb - 'b'</code>
+        → <code class="returnvalue">["a", "c"]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">-</code> <code class="type">text[]</code>
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Deletes all matching keys or array elements from the left operand.
+       </p>
+       <p>
+        <code class="literal">'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]</code>
+        → <code class="returnvalue">{}</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">-</code> <code class="type">integer</code>
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Deletes the array element with specified index (negative
+        integers count from the end).  Throws an error if JSON value
+        is not an array.
+       </p>
+       <p>
+        <code class="literal">'["a", "b"]'::jsonb - 1 </code>
+        → <code class="returnvalue">["a"]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">#-</code> <code class="type">text[]</code>
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Deletes the field or array element at the specified path, where path
+        elements can be either field keys or array indexes.
+       </p>
+       <p>
+        <code class="literal">'["a", {"b":1}]'::jsonb #- '{1,b}'</code>
+        → <code class="returnvalue">["a", {}]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">@?</code> <code class="type">jsonpath</code>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Does JSON path return any item for the specified JSON value?
+       </p>
+       <p>
+        <code class="literal">'{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ &gt; 2)'</code>
+        → <code class="returnvalue">t</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="type">jsonb</code> <code class="literal">@@</code> <code class="type">jsonpath</code>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Returns the result of a JSON path predicate check for the
+        specified JSON value.  Only the first item of the result is taken into
+        account.  If the result is not Boolean, then <code class="literal">NULL</code>
+        is returned.
+       </p>
+       <p>
+        <code class="literal">'{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] &gt; 2'</code>
+        → <code class="returnvalue">t</code>
+       </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>
+    The <code class="type">jsonpath</code> operators <code class="literal">@?</code>
+    and <code class="literal">@@</code> suppress the following errors: missing object
+    field or array element, unexpected JSON item type, datetime and numeric
+    errors.  The <code class="type">jsonpath</code>-related functions described below can
+    also be told to suppress these types of errors.  This behavior might be
+    helpful when searching JSON document collections of varying structure.
+   </p></div><p>
+   <a class="xref" href="functions-json.html#FUNCTIONS-JSON-CREATION-TABLE" title="Table 9.47. JSON Creation Functions">Table 9.47</a> shows the functions that are
+   available for constructing <code class="type">json</code> and <code class="type">jsonb</code> values.
+  </p><div class="table" id="FUNCTIONS-JSON-CREATION-TABLE"><p class="title"><strong>Table 9.47. JSON Creation Functions</strong></p><div class="table-contents"><table class="table" summary="JSON Creation Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
+        Function
+       </p>
+       <p>
+        Description
+       </p>
+       <p>
+        Example(s)
+       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.9.2.2.1.1.1.1" class="indexterm"></a>
+        <code class="function">to_json</code> ( <code class="type">anyelement</code> )
+        → <code class="returnvalue">json</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.9.2.2.1.1.2.1" class="indexterm"></a>
+        <code class="function">to_jsonb</code> ( <code class="type">anyelement</code> )
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Converts any SQL value to <code class="type">json</code> or <code class="type">jsonb</code>.
+        Arrays and composites are converted recursively to arrays and
+        objects (multidimensional arrays become arrays of arrays in JSON).
+        Otherwise, if there is a cast from the SQL data type
+        to <code class="type">json</code>, the cast function will be used to perform the
+        conversion;<a href="#ftn.id-1.5.8.22.5.9.2.2.1.1.3.4" class="footnote"><sup class="footnote" id="id-1.5.8.22.5.9.2.2.1.1.3.4">[a]</sup></a>
+        otherwise, a scalar JSON value is produced.  For any scalar other than
+        a number, a Boolean, or a null value, the text representation will be
+        used, with escaping as necessary to make it a valid JSON string value.
+       </p>
+       <p>
+        <code class="literal">to_json('Fred said "Hi."'::text)</code>
+        → <code class="returnvalue">"Fred said \"Hi.\""</code>
+       </p>
+       <p>
+        <code class="literal">to_jsonb(row(42, 'Fred said "Hi."'::text))</code>
+        → <code class="returnvalue">{"f1": 42, "f2": "Fred said \"Hi.\""}</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.9.2.2.2.1.1.1" class="indexterm"></a>
+        <code class="function">array_to_json</code> ( <code class="type">anyarray</code> [<span class="optional">, <code class="type">boolean</code> </span>] )
+        → <code class="returnvalue">json</code>
+       </p>
+       <p>
+        Converts an SQL array to a JSON array.  The behavior is the same
+        as <code class="function">to_json</code> except that line feeds will be added
+        between top-level array elements if the optional boolean parameter is
+        true.
+       </p>
+       <p>
+        <code class="literal">array_to_json('{{1,5},{99,100}}'::int[])</code>
+        → <code class="returnvalue">[[1,5],[99,100]]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.9.2.2.3.1.1.1" class="indexterm"></a>
+        <code class="function">row_to_json</code> ( <code class="type">record</code> [<span class="optional">, <code class="type">boolean</code> </span>] )
+        → <code class="returnvalue">json</code>
+       </p>
+       <p>
+        Converts an SQL composite value to a JSON object.  The behavior is the
+        same as <code class="function">to_json</code> except that line feeds will be
+        added between top-level elements if the optional boolean parameter is
+        true.
+       </p>
+       <p>
+        <code class="literal">row_to_json(row(1,'foo'))</code>
+        → <code class="returnvalue">{"f1":1,"f2":"foo"}</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.9.2.2.4.1.1.1" class="indexterm"></a>
+        <code class="function">json_build_array</code> ( <code class="literal">VARIADIC</code> <code class="type">"any"</code> )
+        → <code class="returnvalue">json</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.9.2.2.4.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_build_array</code> ( <code class="literal">VARIADIC</code> <code class="type">"any"</code> )
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Builds a possibly-heterogeneously-typed JSON array out of a variadic
+        argument list.  Each argument is converted as
+        per <code class="function">to_json</code> or <code class="function">to_jsonb</code>.
+       </p>
+       <p>
+        <code class="literal">json_build_array(1, 2, 'foo', 4, 5)</code>
+        → <code class="returnvalue">[1, 2, "foo", 4, 5]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.9.2.2.5.1.1.1" class="indexterm"></a>
+        <code class="function">json_build_object</code> ( <code class="literal">VARIADIC</code> <code class="type">"any"</code> )
+        → <code class="returnvalue">json</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.9.2.2.5.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_build_object</code> ( <code class="literal">VARIADIC</code> <code class="type">"any"</code> )
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Builds a JSON object out of a variadic argument list.  By convention,
+        the argument list consists of alternating keys and values.  Key
+        arguments are coerced to text; value arguments are converted as
+        per <code class="function">to_json</code> or <code class="function">to_jsonb</code>.
+       </p>
+       <p>
+        <code class="literal">json_build_object('foo', 1, 2, row(3,'bar'))</code>
+        → <code class="returnvalue">{"foo" : 1, "2" : {"f1":3,"f2":"bar"}}</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.9.2.2.6.1.1.1" class="indexterm"></a>
+        <code class="function">json_object</code> ( <code class="type">text[]</code> )
+        → <code class="returnvalue">json</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.9.2.2.6.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_object</code> ( <code class="type">text[]</code> )
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        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.  All values are converted to JSON
+        strings.
+       </p>
+       <p>
+        <code class="literal">json_object('{a, 1, b, "def", c, 3.5}')</code>
+        → <code class="returnvalue">{"a" : "1", "b" : "def", "c" : "3.5"}</code>
+       </p>
+        <p><code class="literal">json_object('{{a, 1}, {b, "def"}, {c, 3.5}}')</code>
+        → <code class="returnvalue">{"a" : "1", "b" : "def", "c" : "3.5"}</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="function">json_object</code> ( <em class="parameter"><code>keys</code></em> <code class="type">text[]</code>, <em class="parameter"><code>values</code></em> <code class="type">text[]</code> )
+        → <code class="returnvalue">json</code>
+       </p>
+       <p class="func_signature">
+        <code class="function">jsonb_object</code> ( <em class="parameter"><code>keys</code></em> <code class="type">text[]</code>, <em class="parameter"><code>values</code></em> <code class="type">text[]</code> )
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        This form of <code class="function">json_object</code> takes keys and values
+        pairwise from separate text arrays.  Otherwise it is identical to
+        the one-argument form.
+       </p>
+       <p>
+        <code class="literal">json_object('{a,b}', '{1,2}')</code>
+        → <code class="returnvalue">{"a": "1", "b": "2"}</code>
+       </p></td></tr></tbody><tbody class="footnotes"><tr><td colspan="1"><div id="ftn.id-1.5.8.22.5.9.2.2.1.1.3.4" class="footnote"><p><a href="#id-1.5.8.22.5.9.2.2.1.1.3.4" class="para"><sup class="para">[a] </sup></a>
+          For example, the <a class="xref" href="hstore.html" title="F.18. hstore">hstore</a> extension has a cast
+          from <code class="type">hstore</code> to <code class="type">json</code>, so that
+          <code class="type">hstore</code> values converted via the JSON creation functions
+          will be represented as JSON objects, not as primitive string values.
+         </p></div></td></tr></tbody></table></div></div><br class="table-break" /><p>
+   <a class="xref" href="functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE" title="Table 9.48. JSON Processing Functions">Table 9.48</a> shows the functions that
+   are available for processing <code class="type">json</code> and <code class="type">jsonb</code> values.
+  </p><div class="table" id="FUNCTIONS-JSON-PROCESSING-TABLE"><p class="title"><strong>Table 9.48. JSON Processing Functions</strong></p><div class="table-contents"><table class="table" summary="JSON Processing Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
+        Function
+       </p>
+       <p>
+        Description
+       </p>
+       <p>
+        Example(s)
+       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.1.1.1.1" class="indexterm"></a>
+        <code class="function">json_array_elements</code> ( <code class="type">json</code> )
+        → <code class="returnvalue">setof json</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.1.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_array_elements</code> ( <code class="type">jsonb</code> )
+        → <code class="returnvalue">setof jsonb</code>
+       </p>
+       <p>
+        Expands the top-level JSON array into a set of JSON values.
+       </p>
+       <p>
+        <code class="literal">select * from json_array_elements('[1,true, [2,false]]')</code>
+        → <code class="returnvalue"></code>
+</p><pre class="programlisting">
+   value
+-----------
+ 1
+ true
+ [2,false]
+</pre><p>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.2.1.1.1" class="indexterm"></a>
+        <code class="function">json_array_elements_text</code> ( <code class="type">json</code> )
+        → <code class="returnvalue">setof text</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.2.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_array_elements_text</code> ( <code class="type">jsonb</code> )
+        → <code class="returnvalue">setof text</code>
+       </p>
+       <p>
+        Expands the top-level JSON array into a set of <code class="type">text</code> values.
+       </p>
+       <p>
+        <code class="literal">select * from json_array_elements_text('["foo", "bar"]')</code>
+        → <code class="returnvalue"></code>
+</p><pre class="programlisting">
+   value
+-----------
+ foo
+ bar
+</pre><p>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.3.1.1.1" class="indexterm"></a>
+        <code class="function">json_array_length</code> ( <code class="type">json</code> )
+        → <code class="returnvalue">integer</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.3.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_array_length</code> ( <code class="type">jsonb</code> )
+        → <code class="returnvalue">integer</code>
+       </p>
+       <p>
+        Returns the number of elements in the top-level JSON array.
+       </p>
+       <p>
+        <code class="literal">json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')</code>
+        → <code class="returnvalue">5</code>
+       </p>
+       <p>
+        <code class="literal">jsonb_array_length('[]')</code>
+        → <code class="returnvalue">0</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.4.1.1.1" class="indexterm"></a>
+        <code class="function">json_each</code> ( <code class="type">json</code> )
+        → <code class="returnvalue">setof record</code>
+        ( <em class="parameter"><code>key</code></em> <code class="type">text</code>,
+        <em class="parameter"><code>value</code></em> <code class="type">json</code> )
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.4.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_each</code> ( <code class="type">jsonb</code> )
+        → <code class="returnvalue">setof record</code>
+        ( <em class="parameter"><code>key</code></em> <code class="type">text</code>,
+        <em class="parameter"><code>value</code></em> <code class="type">jsonb</code> )
+       </p>
+       <p>
+        Expands the top-level JSON object into a set of key/value pairs.
+       </p>
+       <p>
+        <code class="literal">select * from json_each('{"a":"foo", "b":"bar"}')</code>
+        → <code class="returnvalue"></code>
+</p><pre class="programlisting">
+ key | value
+-----+-------
+ a   | "foo"
+ b   | "bar"
+</pre><p>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.5.1.1.1" class="indexterm"></a>
+        <code class="function">json_each_text</code> ( <code class="type">json</code> )
+        → <code class="returnvalue">setof record</code>
+        ( <em class="parameter"><code>key</code></em> <code class="type">text</code>,
+        <em class="parameter"><code>value</code></em> <code class="type">text</code> )
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.5.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_each_text</code> ( <code class="type">jsonb</code> )
+        → <code class="returnvalue">setof record</code>
+        ( <em class="parameter"><code>key</code></em> <code class="type">text</code>,
+        <em class="parameter"><code>value</code></em> <code class="type">text</code> )
+       </p>
+       <p>
+        Expands the top-level JSON object into a set of key/value pairs.
+        The returned <em class="parameter"><code>value</code></em>s will be of
+        type <code class="type">text</code>.
+       </p>
+       <p>
+        <code class="literal">select * from json_each_text('{"a":"foo", "b":"bar"}')</code>
+        → <code class="returnvalue"></code>
+</p><pre class="programlisting">
+ key | value
+-----+-------
+ a   | foo
+ b   | bar
+</pre><p>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.6.1.1.1" class="indexterm"></a>
+        <code class="function">json_extract_path</code> ( <em class="parameter"><code>from_json</code></em> <code class="type">json</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>path_elems</code></em> <code class="type">text[]</code> )
+        → <code class="returnvalue">json</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.6.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_extract_path</code> ( <em class="parameter"><code>from_json</code></em> <code class="type">jsonb</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>path_elems</code></em> <code class="type">text[]</code> )
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Extracts JSON sub-object at the specified path.
+        (This is functionally equivalent to the <code class="literal">#&gt;</code>
+        operator, but writing the path out as a variadic list can be more
+        convenient in some cases.)
+       </p>
+       <p>
+        <code class="literal">json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')</code>
+        → <code class="returnvalue">"foo"</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.7.1.1.1" class="indexterm"></a>
+        <code class="function">json_extract_path_text</code> ( <em class="parameter"><code>from_json</code></em> <code class="type">json</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>path_elems</code></em> <code class="type">text[]</code> )
+        → <code class="returnvalue">text</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.7.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_extract_path_text</code> ( <em class="parameter"><code>from_json</code></em> <code class="type">jsonb</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>path_elems</code></em> <code class="type">text[]</code> )
+        → <code class="returnvalue">text</code>
+       </p>
+       <p>
+        Extracts JSON sub-object at the specified path as <code class="type">text</code>.
+        (This is functionally equivalent to the <code class="literal">#&gt;&gt;</code>
+        operator.)
+       </p>
+       <p>
+        <code class="literal">json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')</code>
+        → <code class="returnvalue">foo</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.8.1.1.1" class="indexterm"></a>
+        <code class="function">json_object_keys</code> ( <code class="type">json</code> )
+        → <code class="returnvalue">setof text</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.8.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_object_keys</code> ( <code class="type">jsonb</code> )
+        → <code class="returnvalue">setof text</code>
+       </p>
+       <p>
+        Returns the set of keys in the top-level JSON object.
+       </p>
+       <p>
+        <code class="literal">select * from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')</code>
+        → <code class="returnvalue"></code>
+</p><pre class="programlisting">
+ json_object_keys
+------------------
+ f1
+ f2
+</pre><p>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.9.1.1.1" class="indexterm"></a>
+        <code class="function">json_populate_record</code> ( <em class="parameter"><code>base</code></em> <code class="type">anyelement</code>, <em class="parameter"><code>from_json</code></em> <code class="type">json</code> )
+        → <code class="returnvalue">anyelement</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.9.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_populate_record</code> ( <em class="parameter"><code>base</code></em> <code class="type">anyelement</code>, <em class="parameter"><code>from_json</code></em> <code class="type">jsonb</code> )
+        → <code class="returnvalue">anyelement</code>
+       </p>
+       <p>
+        Expands the top-level JSON object to a row having the composite type
+        of the <em class="parameter"><code>base</code></em> argument.  The JSON object
+        is scanned for fields whose names match column names of the output row
+        type, and their values are inserted into those columns of the output.
+        (Fields that do not correspond to any output column name are ignored.)
+        In typical use, the value of <em class="parameter"><code>base</code></em> is just
+        <code class="literal">NULL</code>, which means that any output columns that do
+        not match any object field will be filled with nulls.  However,
+        if <em class="parameter"><code>base</code></em> isn't <code class="literal">NULL</code> then
+        the values it contains will be used for unmatched columns.
+       </p>
+       <p>
+        To convert a JSON value to the SQL type of an output column, the
+        following rules are applied in sequence:
+        </p><div class="itemizedlist"><ul class="itemizedlist compact" style="list-style-type: disc; "><li class="listitem"><p>
+           A JSON null value is converted to an SQL null in all cases.
+          </p></li><li class="listitem"><p>
+           If the output column is of type <code class="type">json</code>
+           or <code class="type">jsonb</code>, the JSON value is just reproduced exactly.
+          </p></li><li class="listitem"><p>
+           If the output column is a composite (row) type, and the JSON value
+           is a JSON object, the fields of the object are converted to columns
+           of the output row type by recursive application of these rules.
+          </p></li><li class="listitem"><p>
+           Likewise, if the output column is an array type and the JSON value
+           is a JSON array, the elements of the JSON array are converted to
+           elements of the output array by recursive application of these
+           rules.
+          </p></li><li class="listitem"><p>
+           Otherwise, if the JSON value is a string, the contents of the
+           string are fed to the input conversion function for the column's
+           data type.
+          </p></li><li class="listitem"><p>
+           Otherwise, the ordinary text representation of the JSON value is
+           fed to the input conversion function for the column's data type.
+          </p></li></ul></div><p>
+       </p>
+       <p>
+        While the example below uses a constant JSON value, typical use would
+        be to reference a <code class="type">json</code> or <code class="type">jsonb</code> column
+        laterally from another table in the query's <code class="literal">FROM</code>
+        clause.  Writing <code class="function">json_populate_record</code> in
+        the <code class="literal">FROM</code> clause is good practice, since all of the
+        extracted columns are available for use without duplicate function
+        calls.
+       </p>
+       <p>
+        <code class="literal">create type subrowtype as (d int, e text);</code>
+        <code class="literal">create type myrowtype as (a int, b text[], c subrowtype);</code>
+       </p>
+       <p>
+        <code class="literal">select * from json_populate_record(null::myrowtype,
+         '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a  b c"}, "x": "foo"}')</code>
+        → <code class="returnvalue"></code>
+</p><pre class="programlisting">
+ a |   b       |      c
+---+-----------+-------------
+ 1 | {2,"a b"} | (4,"a b c")
+</pre><p>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.10.1.1.1" class="indexterm"></a>
+        <code class="function">json_populate_recordset</code> ( <em class="parameter"><code>base</code></em> <code class="type">anyelement</code>, <em class="parameter"><code>from_json</code></em> <code class="type">json</code> )
+        → <code class="returnvalue">setof anyelement</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.10.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_populate_recordset</code> ( <em class="parameter"><code>base</code></em> <code class="type">anyelement</code>, <em class="parameter"><code>from_json</code></em> <code class="type">jsonb</code> )
+        → <code class="returnvalue">setof anyelement</code>
+       </p>
+       <p>
+        Expands the top-level JSON array of objects to a set of rows having
+        the composite type of the <em class="parameter"><code>base</code></em> argument.
+        Each element of the JSON array is processed as described above
+        for <code class="function">json[b]_populate_record</code>.
+       </p>
+       <p>
+        <code class="literal">create type twoints as (a int, b int);</code>
+       </p>
+       <p>
+        <code class="literal">select * from json_populate_recordset(null::twoints, '[{"a":1,"b":2}, {"a":3,"b":4}]')</code>
+        → <code class="returnvalue"></code>
+</p><pre class="programlisting">
+ a | b
+---+---
+ 1 | 2
+ 3 | 4
+</pre><p>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.11.1.1.1" class="indexterm"></a>
+        <code class="function">json_to_record</code> ( <code class="type">json</code> )
+        → <code class="returnvalue">record</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.11.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_to_record</code> ( <code class="type">jsonb</code> )
+        → <code class="returnvalue">record</code>
+       </p>
+       <p>
+        Expands the top-level JSON object to a row having the composite type
+        defined by an <code class="literal">AS</code> clause.  (As with all functions
+        returning <code class="type">record</code>, the calling query must explicitly
+        define the structure of the record with an <code class="literal">AS</code>
+        clause.)  The output record is filled from fields of the JSON object,
+        in the same way as described above
+        for <code class="function">json[b]_populate_record</code>.  Since there is no
+        input record value, unmatched columns are always filled with nulls.
+       </p>
+       <p>
+        <code class="literal">create type myrowtype as (a int, b text);</code>
+       </p>
+       <p>
+        <code class="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)</code>
+        → <code class="returnvalue"></code>
+</p><pre class="programlisting">
+ a |    b    |    c    | d |       r
+---+---------+---------+---+---------------
+ 1 | [1,2,3] | {1,2,3} |   | (123,"a b c")
+</pre><p>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.12.1.1.1" class="indexterm"></a>
+        <code class="function">json_to_recordset</code> ( <code class="type">json</code> )
+        → <code class="returnvalue">setof record</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.12.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_to_recordset</code> ( <code class="type">jsonb</code> )
+        → <code class="returnvalue">setof record</code>
+       </p>
+       <p>
+        Expands the top-level JSON array of objects to a set of rows having
+        the composite type defined by an <code class="literal">AS</code> clause.  (As
+        with all functions returning <code class="type">record</code>, the calling query
+        must explicitly define the structure of the record with
+        an <code class="literal">AS</code> clause.)  Each element of the JSON array is
+        processed as described above
+        for <code class="function">json[b]_populate_record</code>.
+       </p>
+       <p>
+        <code class="literal">select * from json_to_recordset('[{"a":1,"b":"foo"}, {"a":"2","c":"bar"}]') as x(a int, b text)</code>
+        → <code class="returnvalue"></code>
+</p><pre class="programlisting">
+ a |  b
+---+-----
+ 1 | foo
+ 2 |
+</pre><p>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.13.1.1.1" class="indexterm"></a>
+        <code class="function">jsonb_set</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">text[]</code>, <em class="parameter"><code>new_value</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>create_if_missing</code></em> <code class="type">boolean</code> </span>] )
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Returns <em class="parameter"><code>target</code></em>
+        with the item designated by <em class="parameter"><code>path</code></em>
+        replaced by <em class="parameter"><code>new_value</code></em>, or with
+        <em class="parameter"><code>new_value</code></em> added if
+        <em class="parameter"><code>create_if_missing</code></em> is true (which is the
+        default) and the item designated by <em class="parameter"><code>path</code></em>
+        does not exist.
+        All earlier steps in the path must exist, or
+        the <em class="parameter"><code>target</code></em> is returned unchanged.
+        As with the path oriented operators, negative integers that
+        appear in the <em class="parameter"><code>path</code></em> count from the end
+        of JSON arrays.
+        If the last path step is an array index that is out of range,
+        and <em class="parameter"><code>create_if_missing</code></em> is true, the new
+        value is added at the beginning of the array if the index is negative,
+        or at the end of the array if it is positive.
+       </p>
+       <p>
+        <code class="literal">jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', '[2,3,4]', false)</code>
+        → <code class="returnvalue">[{"f1": [2, 3, 4], "f2": null}, 2, null, 3]</code>
+       </p>
+       <p>
+        <code class="literal">jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}', '[2,3,4]')</code>
+        → <code class="returnvalue">[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.14.1.1.1" class="indexterm"></a>
+        <code class="function">jsonb_set_lax</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">text[]</code>, <em class="parameter"><code>new_value</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>create_if_missing</code></em> <code class="type">boolean</code> [<span class="optional">, <em class="parameter"><code>null_value_treatment</code></em> <code class="type">text</code> </span>]</span>] )
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        If <em class="parameter"><code>new_value</code></em> is not <code class="literal">NULL</code>,
+        behaves identically to <code class="literal">jsonb_set</code>. Otherwise behaves
+        according to the value
+        of <em class="parameter"><code>null_value_treatment</code></em> which must be one
+        of <code class="literal">'raise_exception'</code>,
+        <code class="literal">'use_json_null'</code>, <code class="literal">'delete_key'</code>, or
+        <code class="literal">'return_target'</code>. The default is
+        <code class="literal">'use_json_null'</code>.
+       </p>
+       <p>
+        <code class="literal">jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', null)</code>
+        → <code class="returnvalue">[{"f1": null, "f2": null}, 2, null, 3]</code>
+       </p>
+       <p>
+        <code class="literal">jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}', null, true, 'return_target')</code>
+        → <code class="returnvalue">[{"f1": 99, "f2": null}, 2]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.15.1.1.1" class="indexterm"></a>
+        <code class="function">jsonb_insert</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">text[]</code>, <em class="parameter"><code>new_value</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>insert_after</code></em> <code class="type">boolean</code> </span>] )
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Returns <em class="parameter"><code>target</code></em>
+        with <em class="parameter"><code>new_value</code></em> inserted.  If the item
+        designated by the <em class="parameter"><code>path</code></em> is an array
+        element, <em class="parameter"><code>new_value</code></em> will be inserted before
+        that item if <em class="parameter"><code>insert_after</code></em> is false (which
+        is the default), or after it
+        if <em class="parameter"><code>insert_after</code></em> is true.  If the item
+        designated by the <em class="parameter"><code>path</code></em> is an object
+        field, <em class="parameter"><code>new_value</code></em> will be inserted only if
+        the object does not already contain that key.
+        All earlier steps in the path must exist, or
+        the <em class="parameter"><code>target</code></em> is returned unchanged.
+        As with the path oriented operators, negative integers that
+        appear in the <em class="parameter"><code>path</code></em> count from the end
+        of JSON arrays.
+        If the last path step is an array index that is out of range, the new
+        value is added at the beginning of the array if the index is negative,
+        or at the end of the array if it is positive.
+       </p>
+       <p>
+        <code class="literal">jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"')</code>
+        → <code class="returnvalue">{"a": [0, "new_value", 1, 2]}</code>
+       </p>
+       <p>
+        <code class="literal">jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true)</code>
+        → <code class="returnvalue">{"a": [0, 1, "new_value", 2]}</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.16.1.1.1" class="indexterm"></a>
+        <code class="function">json_strip_nulls</code> ( <code class="type">json</code> )
+        → <code class="returnvalue">json</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.16.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_strip_nulls</code> ( <code class="type">jsonb</code> )
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Deletes all object fields that have null values from the given JSON
+        value, recursively.  Null values that are not object fields are
+        untouched.
+       </p>
+       <p>
+        <code class="literal">json_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]')</code>
+        → <code class="returnvalue">[{"f1":1},2,null,3]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.17.1.1.1" class="indexterm"></a>
+        <code class="function">jsonb_path_exists</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Checks whether the JSON path returns any item for the specified JSON
+        value.
+        If the <em class="parameter"><code>vars</code></em> argument is specified, it must
+        be a JSON object, and its fields provide named values to be
+        substituted into the <code class="type">jsonpath</code> expression.
+        If the <em class="parameter"><code>silent</code></em> argument is specified and
+        is <code class="literal">true</code>, the function suppresses the same errors
+        as the <code class="literal">@?</code> and <code class="literal">@@</code> operators do.
+       </p>
+       <p>
+        <code class="literal">jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ &gt;= $min &amp;&amp; @ &lt;= $max)', '{"min":2, "max":4}')</code>
+        → <code class="returnvalue">t</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.18.1.1.1" class="indexterm"></a>
+        <code class="function">jsonb_path_match</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Returns the result of a JSON path predicate check for the specified
+        JSON value.  Only the first item of the result is taken into account.
+        If the result is not Boolean, then <code class="literal">NULL</code> is returned.
+        The optional <em class="parameter"><code>vars</code></em>
+        and <em class="parameter"><code>silent</code></em> arguments act the same as
+        for <code class="function">jsonb_path_exists</code>.
+       </p>
+       <p>
+        <code class="literal">jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ &gt;= $min &amp;&amp; @ &lt;= $max))', '{"min":2, "max":4}')</code>
+        → <code class="returnvalue">t</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.19.1.1.1" class="indexterm"></a>
+        <code class="function">jsonb_path_query</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )
+        → <code class="returnvalue">setof jsonb</code>
+       </p>
+       <p>
+        Returns all JSON items returned by the JSON path for the specified
+        JSON value.
+        The optional <em class="parameter"><code>vars</code></em>
+        and <em class="parameter"><code>silent</code></em> arguments act the same as
+        for <code class="function">jsonb_path_exists</code>.
+       </p>
+       <p>
+        <code class="literal">select * from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ &gt;= $min &amp;&amp; @ &lt;= $max)', '{"min":2, "max":4}')</code>
+        → <code class="returnvalue"></code>
+</p><pre class="programlisting">
+ jsonb_path_query
+------------------
+ 2
+ 3
+ 4
+</pre><p>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.20.1.1.1" class="indexterm"></a>
+        <code class="function">jsonb_path_query_array</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Returns all JSON items returned by the JSON path for the specified
+        JSON value, as a JSON array.
+        The optional <em class="parameter"><code>vars</code></em>
+        and <em class="parameter"><code>silent</code></em> arguments act the same as
+        for <code class="function">jsonb_path_exists</code>.
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ &gt;= $min &amp;&amp; @ &lt;= $max)', '{"min":2, "max":4}')</code>
+        → <code class="returnvalue">[2, 3, 4]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.21.1.1.1" class="indexterm"></a>
+        <code class="function">jsonb_path_query_first</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        Returns the first JSON item returned by the JSON path for the
+        specified JSON value.  Returns <code class="literal">NULL</code> if there are no
+        results.
+        The optional <em class="parameter"><code>vars</code></em>
+        and <em class="parameter"><code>silent</code></em> arguments act the same as
+        for <code class="function">jsonb_path_exists</code>.
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ &gt;= $min &amp;&amp; @ &lt;= $max)', '{"min":2, "max":4}')</code>
+        → <code class="returnvalue">2</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.22.1.1.1" class="indexterm"></a>
+        <code class="function">jsonb_path_exists_tz</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.22.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_path_match_tz</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.22.1.3.1" class="indexterm"></a>
+        <code class="function">jsonb_path_query_tz</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )
+        → <code class="returnvalue">setof jsonb</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.22.1.4.1" class="indexterm"></a>
+        <code class="function">jsonb_path_query_array_tz</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.22.1.5.1" class="indexterm"></a>
+        <code class="function">jsonb_path_query_first_tz</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )
+        → <code class="returnvalue">jsonb</code>
+       </p>
+       <p>
+        These functions act like their counterparts described above without
+        the <code class="literal">_tz</code> suffix, except that these functions support
+        comparisons of date/time values that require timezone-aware
+        conversions.  The example below requires interpretation of the
+        date-only value <code class="literal">2015-08-02</code> as a timestamp with time
+        zone, so the result depends on the current
+        <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> setting.  Due to this dependency, these
+        functions are marked as stable, which means these functions cannot be
+        used in indexes.  Their counterparts are immutable, and so can be used
+        in indexes; but they will throw errors if asked to make such
+        comparisons.
+       </p>
+       <p>
+        <code class="literal">jsonb_path_exists_tz('["2015-08-01 12:00:00-05"]', '$[*] ? (@.datetime() &lt; "2015-08-02".datetime())')</code>
+        → <code class="returnvalue">t</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.23.1.1.1" class="indexterm"></a>
+        <code class="function">jsonb_pretty</code> ( <code class="type">jsonb</code> )
+        → <code class="returnvalue">text</code>
+       </p>
+       <p>
+        Converts the given JSON value to pretty-printed, indented text.
+       </p>
+       <p>
+        <code class="literal">jsonb_pretty('[{"f1":1,"f2":null}, 2]')</code>
+        → <code class="returnvalue"></code>
+</p><pre class="programlisting">
+[
+    {
+        "f1": 1,
+        "f2": null
+    },
+    2
+]
+</pre><p>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.24.1.1.1" class="indexterm"></a>
+        <code class="function">json_typeof</code> ( <code class="type">json</code> )
+        → <code class="returnvalue">text</code>
+       </p>
+       <p class="func_signature">
+        <a id="id-1.5.8.22.5.11.2.2.24.1.2.1" class="indexterm"></a>
+        <code class="function">jsonb_typeof</code> ( <code class="type">jsonb</code> )
+        → <code class="returnvalue">text</code>
+       </p>
+       <p>
+        Returns the type of the top-level JSON value as a text string.
+        Possible types are
+        <code class="literal">object</code>, <code class="literal">array</code>,
+        <code class="literal">string</code>, <code class="literal">number</code>,
+        <code class="literal">boolean</code>, and <code class="literal">null</code>.
+        (The <code class="literal">null</code> result should not be confused
+        with an SQL NULL; see the examples.)
+       </p>
+       <p>
+        <code class="literal">json_typeof('-123.4')</code>
+        → <code class="returnvalue">number</code>
+       </p>
+       <p>
+        <code class="literal">json_typeof('null'::json)</code>
+        → <code class="returnvalue">null</code>
+       </p>
+       <p>
+        <code class="literal">json_typeof(NULL::json) IS NULL</code>
+        → <code class="returnvalue">t</code>
+       </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-SQLJSON-PATH"><div class="titlepage"><div><div><h3 class="title">9.16.2. The SQL/JSON Path Language</h3></div></div></div><a id="id-1.5.8.22.6.2" class="indexterm"></a><p>
+   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 <span class="productname">PostgreSQL</span>,
+   path expressions are implemented as the <code class="type">jsonpath</code>
+   data type and can use any elements described in
+   <a class="xref" href="datatype-json.html#DATATYPE-JSONPATH" title="8.14.7. jsonpath Type">Section 8.14.7</a>.
+  </p><p>
+   JSON query functions and operators
+   pass the provided path expression to the <em class="firstterm">path engine</em>
+   for evaluation. If the expression matches the queried JSON data,
+   the corresponding JSON item, or set of items, is returned.
+   Path expressions are written in the SQL/JSON path language
+   and can include arithmetic expressions and functions.
+  </p><p>
+   A path expression consists of a sequence of elements allowed
+   by the <code class="type">jsonpath</code> data type.
+   The path expression is normally evaluated from left to right, but
+   you can use parentheses to change the order of operations.
+   If the evaluation is successful, a sequence of JSON items is produced,
+   and the evaluation result is returned to the JSON query function
+   that completes the specified computation.
+  </p><p>
+   To refer to the JSON value being queried (the
+   <em class="firstterm">context item</em>), use the <code class="literal">$</code> variable
+   in the path expression. It can be followed by one or more
+   <a class="link" href="datatype-json.html#TYPE-JSONPATH-ACCESSORS" title="Table 8.25. jsonpath Accessors">accessor operators</a>,
+   which go down the JSON structure level by level to retrieve sub-items
+   of the context item. Each operator that follows deals with the
+   result of the previous evaluation step.
+  </p><p>
+   For example, suppose you have some JSON data from a GPS tracker that you
+   would like to parse, such as:
+</p><pre class="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": 135
+      }
+    ]
+  }
+}
+</pre><p>
+  </p><p>
+   To retrieve the available track segments, you need to use the
+   <code class="literal">.<em class="replaceable"><code>key</code></em></code> accessor
+   operator to descend through surrounding JSON objects:
+</p><pre class="programlisting">
+$.track.segments
+</pre><p>
+  </p><p>
+   To retrieve the contents of an array, you typically use the
+   <code class="literal">[*]</code> operator. For example,
+   the following path will return the location coordinates for all
+   the available track segments:
+</p><pre class="programlisting">
+$.track.segments[*].location
+</pre><p>
+  </p><p>
+   To return the coordinates of the first segment only, you can
+   specify the corresponding subscript in the <code class="literal">[]</code>
+   accessor operator. Recall that JSON array indexes are 0-relative:
+</p><pre class="programlisting">
+$.track.segments[0].location
+</pre><p>
+  </p><p>
+   The result of each path evaluation step can be processed
+   by one or more <code class="type">jsonpath</code> operators and methods
+   listed in <a class="xref" href="functions-json.html#FUNCTIONS-SQLJSON-PATH-OPERATORS" title="9.16.2.2. SQL/JSON Path Operators and Methods">Section 9.16.2.2</a>.
+   Each method name must be preceded by a dot. For example,
+   you can get the size of an array:
+</p><pre class="programlisting">
+$.track.segments.size()
+</pre><p>
+   More examples of using <code class="type">jsonpath</code> operators
+   and methods within path expressions appear below in
+   <a class="xref" href="functions-json.html#FUNCTIONS-SQLJSON-PATH-OPERATORS" title="9.16.2.2. SQL/JSON Path Operators and Methods">Section 9.16.2.2</a>.
+  </p><p>
+   When defining a path, you can also use one or more
+   <em class="firstterm">filter expressions</em> that work similarly to the
+   <code class="literal">WHERE</code> clause in SQL. A filter expression begins with
+   a question mark and provides a condition in parentheses:
+
+</p><pre class="programlisting">
+? (<em class="replaceable"><code>condition</code></em>)
+</pre><p>
+  </p><p>
+   Filter expressions must be written just after the path evaluation step
+   to which they should apply. The result of that step is filtered to include
+   only those items that satisfy the provided condition. SQL/JSON defines
+   three-valued logic, so the condition can be <code class="literal">true</code>, <code class="literal">false</code>,
+   or <code class="literal">unknown</code>. The <code class="literal">unknown</code> value
+   plays the same role as SQL <code class="literal">NULL</code> and can be tested
+   for with the <code class="literal">is unknown</code> predicate. Further path
+   evaluation steps use only those items for which the filter expression
+   returned <code class="literal">true</code>.
+  </p><p>
+   The functions and operators that can be used in filter expressions are
+   listed in <a class="xref" href="functions-json.html#FUNCTIONS-SQLJSON-FILTER-EX-TABLE" title="Table 9.50. jsonpath Filter Expression Elements">Table 9.50</a>.  Within a
+   filter expression, the <code class="literal">@</code> variable denotes the value
+   being filtered (i.e., one result of the preceding path step).  You can
+   write accessor operators after <code class="literal">@</code> to retrieve component
+   items.
+  </p><p>
+   For example, suppose you would like to retrieve all heart rate values higher
+   than 130. You can achieve this using the following expression:
+</p><pre class="programlisting">
+$.track.segments[*].HR ? (@ &gt; 130)
+</pre><p>
+  </p><p>
+   To get the start times of segments with such values, you have to
+   filter out irrelevant segments before returning the start times, so the
+   filter expression is applied to the previous step, and the path used
+   in the condition is different:
+</p><pre class="programlisting">
+$.track.segments[*] ? (@.HR &gt; 130)."start time"
+</pre><p>
+  </p><p>
+   You can use several filter expressions in sequence, if required. For
+   example, the following expression selects start times of all segments that
+   contain locations with relevant coordinates and high heart rate values:
+</p><pre class="programlisting">
+$.track.segments[*] ? (@.location[1] &lt; 13.4) ? (@.HR &gt; 130)."start time"
+</pre><p>
+  </p><p>
+   Using filter expressions at different nesting levels is also allowed.
+   The following example first filters all segments by location, and then
+   returns high heart rate values for these segments, if available:
+</p><pre class="programlisting">
+$.track.segments[*] ? (@.location[1] &lt; 13.4).HR ? (@ &gt; 130)
+</pre><p>
+  </p><p>
+   You can also nest filter expressions within each other:
+</p><pre class="programlisting">
+$.track ? (exists(@.segments[*] ? (@.HR &gt; 130))).segments.size()
+</pre><p>
+   This expression returns the size of the track if it contains any
+   segments with high heart rate values, or an empty sequence otherwise.
+  </p><p>
+   <span class="productname">PostgreSQL</span>'s implementation of the SQL/JSON path
+   language has the following deviations from the SQL/JSON standard:
+  </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
+     A path expression can be a Boolean predicate, although the SQL/JSON
+     standard allows predicates only in filters.  This is necessary for
+     implementation of the <code class="literal">@@</code> operator. For example,
+     the following <code class="type">jsonpath</code> expression is valid in
+     <span class="productname">PostgreSQL</span>:
+</p><pre class="programlisting">
+$.track.segments[*].HR &lt; 70
+</pre><p>
+    </p></li><li class="listitem"><p>
+     There are minor differences in the interpretation of regular
+     expression patterns used in <code class="literal">like_regex</code> filters, as
+     described in <a class="xref" href="functions-json.html#JSONPATH-REGULAR-EXPRESSIONS" title="9.16.2.3. SQL/JSON Regular Expressions">Section 9.16.2.3</a>.
+    </p></li></ul></div><div class="sect3" id="STRICT-AND-LAX-MODES"><div class="titlepage"><div><div><h4 class="title">9.16.2.1. Strict and Lax Modes</h4></div></div></div><p>
+     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:
+    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
+      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.
+     </p></li><li class="listitem"><p>
+      strict — if a structural error occurs, an error is raised.
+     </p></li></ul></div><p>
+    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. An array of size 1 is considered equal to its sole element.
+    Automatic unwrapping is not performed only when:
+    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
+       The path expression contains <code class="literal">type()</code> or
+       <code class="literal">size()</code> methods that return the type
+       and the number of elements in the array, respectively.
+      </p></li><li class="listitem"><p>
+       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.
+      </p></li></ul></div><p>
+   </p><p>
+    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:
+</p><pre class="programlisting">
+lax $.track.segments.location
+</pre><p>
+   </p><p>
+    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
+    <code class="literal">segments</code> array:
+</p><pre class="programlisting">
+strict $.track.segments[*].location
+</pre><p>
+   </p><p>
+    The <code class="literal">.**</code> accessor can lead to surprising results
+    when using the lax mode. For instance, the following query selects every
+    <code class="literal">HR</code> value twice:
+</p><pre class="programlisting">
+lax $.**.HR
+</pre><p>
+    This happens because the <code class="literal">.**</code> accessor selects both
+    the <code class="literal">segments</code> array and each of its elements, while
+    the <code class="literal">.HR</code> accessor automatically unwraps arrays when
+    using the lax mode. To avoid surprising results, we recommend using
+    the <code class="literal">.**</code> accessor only in the strict mode. The
+    following query selects each <code class="literal">HR</code> value just once:
+</p><pre class="programlisting">
+strict $.**.HR
+</pre><p>
+   </p></div><div class="sect3" id="FUNCTIONS-SQLJSON-PATH-OPERATORS"><div class="titlepage"><div><div><h4 class="title">9.16.2.2. SQL/JSON Path Operators and Methods</h4></div></div></div><p>
+    <a class="xref" href="functions-json.html#FUNCTIONS-SQLJSON-OP-TABLE" title="Table 9.49. jsonpath Operators and Methods">Table 9.49</a> shows the operators and
+    methods available in <code class="type">jsonpath</code>.  Note that while the unary
+    operators and methods can be applied to multiple values resulting from a
+    preceding path step, the binary operators (addition etc.) can only be
+    applied to single values.
+   </p><div class="table" id="FUNCTIONS-SQLJSON-OP-TABLE"><p class="title"><strong>Table 9.49. <code class="type">jsonpath</code> Operators and Methods</strong></p><div class="table-contents"><table class="table" summary="jsonpath Operators and Methods" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
+        Operator/Method
+       </p>
+       <p>
+        Description
+       </p>
+       <p>
+        Example(s)
+       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>number</code></em> <code class="literal">+</code> <em class="replaceable"><code>number</code></em>
+        → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code>
+       </p>
+       <p>
+        Addition
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('[2]', '$[0] + 3')</code>
+        → <code class="returnvalue">5</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="literal">+</code> <em class="replaceable"><code>number</code></em>
+        → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code>
+       </p>
+       <p>
+        Unary plus (no operation); unlike addition, this can iterate over
+        multiple values
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('{"x": [2,3,4]}', '+ $.x')</code>
+        → <code class="returnvalue">[2, 3, 4]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>number</code></em> <code class="literal">-</code> <em class="replaceable"><code>number</code></em>
+        → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code>
+       </p>
+       <p>
+        Subtraction
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('[2]', '7 - $[0]')</code>
+        → <code class="returnvalue">5</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="literal">-</code> <em class="replaceable"><code>number</code></em>
+        → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code>
+       </p>
+       <p>
+        Negation; unlike subtraction, this can iterate over
+        multiple values
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('{"x": [2,3,4]}', '- $.x')</code>
+        → <code class="returnvalue">[-2, -3, -4]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>number</code></em> <code class="literal">*</code> <em class="replaceable"><code>number</code></em>
+        → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code>
+       </p>
+       <p>
+        Multiplication
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('[4]', '2 * $[0]')</code>
+        → <code class="returnvalue">8</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>number</code></em> <code class="literal">/</code> <em class="replaceable"><code>number</code></em>
+        → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code>
+       </p>
+       <p>
+        Division
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('[8.5]', '$[0] / 2')</code>
+        → <code class="returnvalue">4.2500000000000000</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>number</code></em> <code class="literal">%</code> <em class="replaceable"><code>number</code></em>
+        → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code>
+       </p>
+       <p>
+        Modulo (remainder)
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('[32]', '$[0] % 10')</code>
+        → <code class="returnvalue">2</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>value</code></em> <code class="literal">.</code> <code class="literal">type()</code>
+        → <code class="returnvalue"><em class="replaceable"><code>string</code></em></code>
+       </p>
+       <p>
+        Type of the JSON item (see <code class="function">json_typeof</code>)
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('[1, "2", {}]', '$[*].type()')</code>
+        → <code class="returnvalue">["number", "string", "object"]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>value</code></em> <code class="literal">.</code> <code class="literal">size()</code>
+        → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code>
+       </p>
+       <p>
+        Size of the JSON item (number of array elements, or 1 if not an
+        array)
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('{"m": [11, 15]}', '$.m.size()')</code>
+        → <code class="returnvalue">2</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>value</code></em> <code class="literal">.</code> <code class="literal">double()</code>
+        → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code>
+       </p>
+       <p>
+        Approximate floating-point number converted from a JSON number or
+        string
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('{"len": "1.9"}', '$.len.double() * 2')</code>
+        → <code class="returnvalue">3.8</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>number</code></em> <code class="literal">.</code> <code class="literal">ceiling()</code>
+        → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code>
+       </p>
+       <p>
+        Nearest integer greater than or equal to the given number
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('{"h": 1.3}', '$.h.ceiling()')</code>
+        → <code class="returnvalue">2</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>number</code></em> <code class="literal">.</code> <code class="literal">floor()</code>
+        → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code>
+       </p>
+       <p>
+        Nearest integer less than or equal to the given number
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('{"h": 1.7}', '$.h.floor()')</code>
+        → <code class="returnvalue">1</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>number</code></em> <code class="literal">.</code> <code class="literal">abs()</code>
+        → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code>
+       </p>
+       <p>
+        Absolute value of the given number
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('{"z": -0.3}', '$.z.abs()')</code>
+        → <code class="returnvalue">0.3</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>string</code></em> <code class="literal">.</code> <code class="literal">datetime()</code>
+        → <code class="returnvalue"><em class="replaceable"><code>datetime_type</code></em></code>
+        (see note)
+       </p>
+       <p>
+        Date/time value converted from a string
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('["2015-8-1", "2015-08-12"]', '$[*] ? (@.datetime() &lt; "2015-08-2".datetime())')</code>
+        → <code class="returnvalue">"2015-8-1"</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>string</code></em> <code class="literal">.</code> <code class="literal">datetime(<em class="replaceable"><code>template</code></em>)</code>
+        → <code class="returnvalue"><em class="replaceable"><code>datetime_type</code></em></code>
+        (see note)
+       </p>
+       <p>
+        Date/time value converted from a string using the
+        specified <code class="function">to_timestamp</code> template
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('["12:30", "18:40"]', '$[*].datetime("HH24:MI")')</code>
+        → <code class="returnvalue">["12:30:00", "18:40:00"]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>object</code></em> <code class="literal">.</code> <code class="literal">keyvalue()</code>
+        → <code class="returnvalue"><em class="replaceable"><code>array</code></em></code>
+       </p>
+       <p>
+        The object's key-value pairs, represented as an array of objects
+        containing three fields: <code class="literal">"key"</code>,
+        <code class="literal">"value"</code>, and <code class="literal">"id"</code>;
+        <code class="literal">"id"</code> is a unique identifier of the object the
+        key-value pair belongs to
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('{"x": "20", "y": 32}', '$.keyvalue()')</code>
+        → <code class="returnvalue">[{"id": 0, "key": "x", "value": "20"}, {"id": 0, "key": "y", "value": 32}]</code>
+       </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>
+      The result type of the <code class="literal">datetime()</code> and
+      <code class="literal">datetime(<em class="replaceable"><code>template</code></em>)</code>
+      methods can be <code class="type">date</code>, <code class="type">timetz</code>, <code class="type">time</code>,
+      <code class="type">timestamptz</code>, or <code class="type">timestamp</code>.
+      Both methods determine their result type dynamically.
+     </p><p>
+      The <code class="literal">datetime()</code> method sequentially tries to
+      match its input string to the ISO formats
+      for <code class="type">date</code>, <code class="type">timetz</code>, <code class="type">time</code>,
+      <code class="type">timestamptz</code>, and <code class="type">timestamp</code>. It stops on
+      the first matching format and emits the corresponding data type.
+     </p><p>
+      The <code class="literal">datetime(<em class="replaceable"><code>template</code></em>)</code>
+      method determines the result type according to the fields used in the
+      provided template string.
+     </p><p>
+      The <code class="literal">datetime()</code> and
+      <code class="literal">datetime(<em class="replaceable"><code>template</code></em>)</code> methods
+      use the same parsing rules as the <code class="literal">to_timestamp</code> SQL
+      function does (see <a class="xref" href="functions-formatting.html" title="9.8. Data Type Formatting Functions">Section 9.8</a>), with three
+      exceptions.  First, these methods don't allow unmatched template
+      patterns.  Second, only the following separators are allowed in the
+      template string: minus sign, period, solidus (slash), comma, apostrophe,
+      semicolon, colon and space.  Third, separators in the template string
+      must exactly match the input string.
+     </p><p>
+      If different date/time types need to be compared, an implicit cast is
+      applied. A <code class="type">date</code> value can be cast to <code class="type">timestamp</code>
+      or <code class="type">timestamptz</code>, <code class="type">timestamp</code> can be cast to
+      <code class="type">timestamptz</code>, and <code class="type">time</code> to <code class="type">timetz</code>.
+      However, all but the first of these conversions depend on the current
+      <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> setting, and thus can only be performed
+      within timezone-aware <code class="type">jsonpath</code> functions.
+     </p></div><p>
+    <a class="xref" href="functions-json.html#FUNCTIONS-SQLJSON-FILTER-EX-TABLE" title="Table 9.50. jsonpath Filter Expression Elements">Table 9.50</a> shows the available
+    filter expression elements.
+   </p><div class="table" id="FUNCTIONS-SQLJSON-FILTER-EX-TABLE"><p class="title"><strong>Table 9.50. <code class="type">jsonpath</code> Filter Expression Elements</strong></p><div class="table-contents"><table class="table" summary="jsonpath Filter Expression Elements" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
+        Predicate/Value
+       </p>
+       <p>
+        Description
+       </p>
+       <p>
+        Example(s)
+       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>value</code></em> <code class="literal">==</code> <em class="replaceable"><code>value</code></em>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Equality comparison (this, and the other comparison operators, work on
+        all JSON scalar values)
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('[1, "a", 1, 3]', '$[*] ? (@ == 1)')</code>
+        → <code class="returnvalue">[1, 1]</code>
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('[1, "a", 1, 3]', '$[*] ? (@ == "a")')</code>
+        → <code class="returnvalue">["a"]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>value</code></em> <code class="literal">!=</code> <em class="replaceable"><code>value</code></em>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p class="func_signature">
+        <em class="replaceable"><code>value</code></em> <code class="literal">&lt;&gt;</code> <em class="replaceable"><code>value</code></em>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Non-equality comparison
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('[1, 2, 1, 3]', '$[*] ? (@ != 1)')</code>
+        → <code class="returnvalue">[2, 3]</code>
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('["a", "b", "c"]', '$[*] ? (@ &lt;&gt; "b")')</code>
+        → <code class="returnvalue">["a", "c"]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>value</code></em> <code class="literal">&lt;</code> <em class="replaceable"><code>value</code></em>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Less-than comparison
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ &lt; 2)')</code>
+        → <code class="returnvalue">[1]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>value</code></em> <code class="literal">&lt;=</code> <em class="replaceable"><code>value</code></em>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Less-than-or-equal-to comparison
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('["a", "b", "c"]', '$[*] ? (@ &lt;= "b")')</code>
+        → <code class="returnvalue">["a", "b"]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>value</code></em> <code class="literal">&gt;</code> <em class="replaceable"><code>value</code></em>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Greater-than comparison
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ &gt; 2)')</code>
+        → <code class="returnvalue">[3]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>value</code></em> <code class="literal">&gt;=</code> <em class="replaceable"><code>value</code></em>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Greater-than-or-equal-to comparison
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ &gt;= 2)')</code>
+        → <code class="returnvalue">[2, 3]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="literal">true</code>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        JSON constant <code class="literal">true</code>
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == true)')</code>
+        → <code class="returnvalue">{"name": "Chris", "parent": true}</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="literal">false</code>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        JSON constant <code class="literal">false</code>
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == false)')</code>
+        → <code class="returnvalue">{"name": "John", "parent": false}</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="literal">null</code>
+        → <code class="returnvalue"><em class="replaceable"><code>value</code></em></code>
+       </p>
+       <p>
+        JSON constant <code class="literal">null</code> (note that, unlike in SQL,
+        comparison to <code class="literal">null</code> works normally)
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('[{"name": "Mary", "job": null}, {"name": "Michael", "job": "driver"}]', '$[*] ? (@.job == null) .name')</code>
+        → <code class="returnvalue">"Mary"</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>boolean</code></em> <code class="literal">&amp;&amp;</code> <em class="replaceable"><code>boolean</code></em>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Boolean AND
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('[1, 3, 7]', '$[*] ? (@ &gt; 1 &amp;&amp; @ &lt; 5)')</code>
+        → <code class="returnvalue">3</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>boolean</code></em> <code class="literal">||</code> <em class="replaceable"><code>boolean</code></em>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Boolean OR
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('[1, 3, 7]', '$[*] ? (@ &lt; 1 || @ &gt; 5)')</code>
+        → <code class="returnvalue">7</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="literal">!</code> <em class="replaceable"><code>boolean</code></em>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Boolean NOT
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('[1, 3, 7]', '$[*] ? (!(@ &lt; 5))')</code>
+        → <code class="returnvalue">7</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>boolean</code></em> <code class="literal">is unknown</code>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Tests whether a Boolean condition is <code class="literal">unknown</code>.
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('[-1, 2, 7, "foo"]', '$[*] ? ((@ &gt; 0) is unknown)')</code>
+        → <code class="returnvalue">"foo"</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>string</code></em> <code class="literal">like_regex</code> <em class="replaceable"><code>string</code></em> [<span class="optional"> <code class="literal">flag</code> <em class="replaceable"><code>string</code></em> </span>]
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Tests whether the first operand matches the regular expression
+        given by the second operand, optionally with modifications
+        described by a string of <code class="literal">flag</code> characters (see
+        <a class="xref" href="functions-json.html#JSONPATH-REGULAR-EXPRESSIONS" title="9.16.2.3. SQL/JSON Regular Expressions">Section 9.16.2.3</a>).
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]', '$[*] ? (@ like_regex "^ab.*c")')</code>
+        → <code class="returnvalue">["abc", "abdacb"]</code>
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]', '$[*] ? (@ like_regex "^ab.*c" flag "i")')</code>
+        → <code class="returnvalue">["abc", "aBdC", "abdacb"]</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <em class="replaceable"><code>string</code></em> <code class="literal">starts with</code> <em class="replaceable"><code>string</code></em>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Tests whether the second operand is an initial substring of the first
+        operand.
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('["John Smith", "Mary Stone", "Bob Johnson"]', '$[*] ? (@ starts with "John")')</code>
+        → <code class="returnvalue">"John Smith"</code>
+       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+        <code class="literal">exists</code> <code class="literal">(</code> <em class="replaceable"><code>path_expression</code></em> <code class="literal">)</code>
+        → <code class="returnvalue">boolean</code>
+       </p>
+       <p>
+        Tests whether a path expression matches at least one SQL/JSON item.
+        Returns <code class="literal">unknown</code> if the path expression would result
+        in an error; the second example uses this to avoid a no-such-key error
+        in strict mode.
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query('{"x": [1, 2], "y": [2, 4]}', 'strict $.* ? (exists (@ ? (@[*] &gt; 2)))')</code>
+        → <code class="returnvalue">[2, 4]</code>
+       </p>
+       <p>
+        <code class="literal">jsonb_path_query_array('{"value": 41}', 'strict $ ? (exists (@.name)) .name')</code>
+        → <code class="returnvalue">[]</code>
+       </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect3" id="JSONPATH-REGULAR-EXPRESSIONS"><div class="titlepage"><div><div><h4 class="title">9.16.2.3. SQL/JSON Regular Expressions</h4></div></div></div><a id="id-1.5.8.22.6.24.2" class="indexterm"></a><p>
+     SQL/JSON path expressions allow matching text to a regular expression
+     with the <code class="literal">like_regex</code> filter.  For example, the
+     following SQL/JSON path query would case-insensitively match all
+     strings in an array that start with an English vowel:
+</p><pre class="programlisting">
+$[*] ? (@ like_regex "^[aeiou]" flag "i")
+</pre><p>
+    </p><p>
+     The optional <code class="literal">flag</code> string may include one or more of
+     the characters
+     <code class="literal">i</code> for case-insensitive match,
+     <code class="literal">m</code> to allow <code class="literal">^</code>
+     and <code class="literal">$</code> to match at newlines,
+     <code class="literal">s</code> to allow <code class="literal">.</code> to match a newline,
+     and <code class="literal">q</code> to quote the whole pattern (reducing the
+     behavior to a simple substring match).
+    </p><p>
+     The SQL/JSON standard borrows its definition for regular expressions
+     from the <code class="literal">LIKE_REGEX</code> operator, which in turn uses the
+     XQuery standard.  PostgreSQL does not currently support the
+     <code class="literal">LIKE_REGEX</code> operator.  Therefore,
+     the <code class="literal">like_regex</code> filter is implemented using the
+     POSIX regular expression engine described in
+     <a class="xref" href="functions-matching.html#FUNCTIONS-POSIX-REGEXP" title="9.7.3. POSIX Regular Expressions">Section 9.7.3</a>.  This leads to various minor
+     discrepancies from standard SQL/JSON behavior, which are cataloged in
+     <a class="xref" href="functions-matching.html#POSIX-VS-XQUERY" title="9.7.3.8. Differences from SQL Standard and XQuery">Section 9.7.3.8</a>.
+     Note, however, that the flag-letter incompatibilities described there
+     do not apply to SQL/JSON, as it translates the XQuery flag letters to
+     match what the POSIX engine expects.
+    </p><p>
+     Keep in mind that the pattern argument of <code class="literal">like_regex</code>
+     is a JSON path string literal, written according to the rules given in
+     <a class="xref" href="datatype-json.html#DATATYPE-JSONPATH" title="8.14.7. jsonpath Type">Section 8.14.7</a>.  This means in particular that any
+     backslashes you want to use in the regular expression must be doubled.
+     For example, to match string values of the root document that contain
+     only digits:
+</p><pre class="programlisting">
+$.* ? (@ like_regex "^\\d+$")
+</pre><p>
+    </p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-xml.html" title="9.15. XML Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-sequence.html" title="9.17. Sequence Manipulation Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.15. XML Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.17. Sequence Manipulation Functions</td></tr></table></div></body></html>
+\ No newline at end of file
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-16 19:46:48 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-16 19:46:48 +0000
commit	311bcfc6b3acdd6fd152798c7f287ddf74fa2a98 (patch)
tree	0ec307299b1dada3701e42f4ca6eda57d708261e /doc/src/sgml/html/functions-json.html
parent	Initial commit. (diff)
download	postgresql-15-upstream.tar.xz postgresql-15-upstream.zip