Home Explore Blog CI



postgresql

12th chunk of `doc/src/sgml/json.sgml`
9c790d05b90300495a10e82640faced3b307359d32fd6e0c0000000100000fa1
 JSON data for further processing with the
   SQL/JSON query functions.
  </para>

  <para>
   The semantics of SQL/JSON path predicates and operators generally follow SQL.
   At the same time, to provide a natural way of working with JSON data,
   SQL/JSON path syntax uses some JavaScript conventions:
  </para>

  <itemizedlist>
   <listitem>
    <para>
     Dot (<literal>.</literal>) is used for member access.
    </para>
   </listitem>
   <listitem>
    <para>
     Square brackets (<literal>[]</literal>) are used for array access.
    </para>
   </listitem>
   <listitem>
    <para>
     SQL/JSON arrays are 0-relative, unlike regular SQL arrays that start from 1.
    </para>
   </listitem>
  </itemizedlist>

  <para>
   Numeric literals in SQL/JSON path expressions follow JavaScript rules,
   which are different from both SQL and JSON in some minor details.  For
   example, SQL/JSON path allows <literal>.1</literal> and
   <literal>1.</literal>, which are invalid in JSON.  Non-decimal integer
   literals and underscore separators are supported, for example,
   <literal>1_000_000</literal>, <literal>0x1EEE_FFFF</literal>,
   <literal>0o273</literal>, <literal>0b100101</literal>.  In SQL/JSON path
   (and in JavaScript, but not in SQL proper), there must not be an underscore
   separator directly after the radix prefix.
  </para>

  <para>
   An SQL/JSON path expression is typically written in an SQL query as an
   SQL character string literal, so it must be enclosed in single quotes,
   and any single quotes desired within the value must be doubled
   (see <xref linkend="sql-syntax-strings"/>).
   Some forms of path expressions require string literals within them.
   These embedded string literals follow JavaScript/ECMAScript conventions:
   they must be surrounded by double quotes, and backslash escapes may be
   used within them to represent otherwise-hard-to-type characters.
   In particular, the way to write a double quote within an embedded string
   literal is <literal>\"</literal>, and to write a backslash itself, you
   must write <literal>\\</literal>.  Other special backslash sequences
   include those recognized in JavaScript strings:
   <literal>\b</literal>,
   <literal>\f</literal>,
   <literal>\n</literal>,
   <literal>\r</literal>,
   <literal>\t</literal>,
   <literal>\v</literal>
   for various ASCII control characters,
   <literal>\x<replaceable>NN</replaceable></literal> for a character code
   written with only two hex digits,
   <literal>\u<replaceable>NNNN</replaceable></literal> for a Unicode
   character identified by its 4-hex-digit code point, and
   <literal>\u{<replaceable>N...</replaceable>}</literal> for a Unicode
   character code point written with 1 to 6 hex digits.
  </para>

  <para>
   A path expression consists of a sequence of path elements,
   which can be any of the following:
   <itemizedlist>
    <listitem>
     <para>
      Path literals of JSON primitive types:
      Unicode text, numeric, true, false, or null.
     </para>
    </listitem>
    <listitem>
     <para>
      Path variables listed in <xref linkend="type-jsonpath-variables"/>.
     </para>
    </listitem>
    <listitem>
     <para>
      Accessor operators listed in <xref linkend="type-jsonpath-accessors"/>.
     </para>
    </listitem>
    <listitem>
     <para>
      <type>jsonpath</type> operators and methods listed
      in <xref linkend="functions-sqljson-path-operators"/>.
     </para>
    </listitem>
    <listitem>
     <para>
      Parentheses, which can be used to provide filter expressions
      or define the order of path evaluation.
     </para>
    </listitem>
   </itemizedlist>
  </para>

  <para>
   For details on using <type>jsonpath</type> expressions with SQL/JSON
   query functions, see <xref linkend="functions-sqljson-path"/>.
  </para>

  <table id="type-jsonpath-variables">
   <title><type>jsonpath</type> Variables</title>
   <tgroup cols="2">
    <colspec colname="col1" colwidth="1*"/>
    <colspec

Title: SQL/JSON Path Syntax and Variables
Summary
The SQL/JSON path syntax follows JavaScript conventions, using dot notation for member access and square brackets for array access, with 0-relative arrays, and supports various literals and escape sequences, and path expressions can include literals, variables, accessor operators, and functions, with details on variables and usage provided in related sections.