<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="json-table-functions">
      </a>
      14.17.6 JSON Table Functions
     </h3>
    </div>
   </div>
  </div>
  <p>
   This section contains information about JSON functions that
      convert JSON data to tabular data. MySQL 8.4 supports
      one such function,
   <code class="literal">
    JSON_TABLE()
   </code>
   .
  </p>
  <p>
   <a name="function_json-table">
   </a>
   <a class="link" href="json-table-functions.html#function_json-table">
    <code class="literal">
     JSON_TABLE(
     <em class="replaceable">
      <code>
       expr
      </code>
     </em>
     ,
     <em class="replaceable">
      <code>
       path
      </code>
     </em>
     COLUMNS
      (
     <em class="replaceable">
      <code>
       column_list
      </code>
     </em>
     ) [AS]
     <em class="replaceable">
      <code>
       alias
      </code>
     </em>
     )
    </code>
   </a>
  </p>
  <a class="indexterm" name="idm46045194660016">
  </a>
  <p>
   Extracts data from a JSON document and returns it as a relational
      table having the specified columns. The complete syntax for this
      function is shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-clike"><div class="docs-select-all right" id="sa8508545"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-clike"><span class="token function">JSON_TABLE</span><span class="token punctuation">(</span>
    <em class="replaceable">expr</em><span class="token punctuation">,</span>
    <em class="replaceable">path</em> COLUMNS <span class="token punctuation">(</span><em class="replaceable">column_list</em><span class="token punctuation">)</span>
<span class="token punctuation">)</span>   <span class="token punctuation">[</span>AS<span class="token punctuation">]</span> <em class="replaceable">alias</em>

<em class="replaceable">column_list</em><span class="token punctuation">:</span>
    <em class="replaceable">column</em><span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">column</em><span class="token punctuation">]</span><span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>

<em class="replaceable">column</em><span class="token punctuation">:</span>
    <em class="replaceable">name</em> FOR ORDINALITY
    <span class="token operator">|</span>  <em class="replaceable">name</em> <em class="replaceable">type</em> PATH <em class="replaceable">string path</em> <span class="token punctuation">[</span><em class="replaceable">on_empty</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">on_error</em><span class="token punctuation">]</span>
    <span class="token operator">|</span>  <em class="replaceable">name</em> <em class="replaceable">type</em> EXISTS PATH <em class="replaceable">string path</em>
    <span class="token operator">|</span>  NESTED <span class="token punctuation">[</span>PATH<span class="token punctuation">]</span> <em class="replaceable">path</em> COLUMNS <span class="token punctuation">(</span><em class="replaceable">column_list</em><span class="token punctuation">)</span>

<em class="replaceable">on_empty</em><span class="token punctuation">:</span>
    <span class="token punctuation">{</span>NULL <span class="token operator">|</span> DEFAULT <em class="replaceable">json_string</em> <span class="token operator">|</span> ERROR<span class="token punctuation">}</span> ON EMPTY

<em class="replaceable">on_error</em><span class="token punctuation">:</span>
    <span class="token punctuation">{</span>NULL <span class="token operator">|</span> DEFAULT <em class="replaceable">json_string</em> <span class="token operator">|</span> ERROR<span class="token punctuation">}</span> ON ERROR</code></pre>
  </div>
  <p>
   <em class="replaceable">
    <code>
     expr
    </code>
   </em>
   : This is an expression that
      returns JSON data. This can be a constant
      (
   <code class="literal">
    '{"a":1}'
   </code>
   ), a column
      (
   <code class="literal">
    t1.json_data
   </code>
   , given table
   <code class="literal">
    t1
   </code>
   specified prior to
   <code class="literal">
    JSON_TABLE()
   </code>
   in the
   <code class="literal">
    FROM
   </code>
   clause), or a function call
      (
   <code class="literal">
    JSON_EXTRACT(t1.json_data,'$.post.comments')
   </code>
   ).
  </p>
  <p>
   <em class="replaceable">
    <code>
     path
    </code>
   </em>
   : A JSON path expression, which is
      applied to the data source. We refer to the JSON value matching
      the path as the
   <span class="emphasis">
    <em>
     row source
    </em>
   </span>
   ; this is used to
      generate a row of relational data. The
   <code class="literal">
    COLUMNS
   </code>
   clause evaluates the row source, finds specific JSON values within
      the row source, and returns those JSON values as SQL values in
      individual columns of a row of relational data.
  </p>
  <p>
   The
   <em class="replaceable">
    <code>
     alias
    </code>
   </em>
   is required. The usual rules
      for table aliases apply (see
   <a class="xref" href="identifiers.html" title="11.2 Schema Object Names">
    Section 11.2, “Schema Object Names”
   </a>
   ).
  </p>
  <p>
   This function compares column names in case-insensitive fashion.
  </p>
  <p>
   <code class="literal">
    JSON_TABLE()
   </code>
   supports four types of columns,
      described in the following list:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         name
        </code>
       </em>
       FOR
          ORDINALITY
      </code>
      : This type enumerates rows in the
      <code class="literal">
       COLUMNS
      </code>
      clause; the column named
      <em class="replaceable">
       <code>
        name
       </code>
      </em>
      is a counter whose type is
      <code class="literal">
       UNSIGNED INT
      </code>
      , and whose initial value is 1.
          This is equivalent to specifying a column as
      <code class="literal">
       AUTO_INCREMENT
      </code>
      in a
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statement, and can
          be used to distinguish parent rows with the same value for
          multiple rows generated by a
      <code class="literal">
       NESTED [PATH]
      </code>
      clause.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         name
        </code>
       </em>
       <em class="replaceable">
        <code>
         type
        </code>
       </em>
       PATH
       <em class="replaceable">
        <code>
         string_path
        </code>
       </em>
       [
       <em class="replaceable">
        <code>
         on_empty
        </code>
       </em>
       ]
          [
       <em class="replaceable">
        <code>
         on_error
        </code>
       </em>
       ]
      </code>
      : Columns of
          this type are used to extract values specified by
      <em class="replaceable">
       <code>
        string_path
       </code>
      </em>
      .
      <em class="replaceable">
       <code>
        type
       </code>
      </em>
      is a MySQL scalar data type
          (that is, it cannot be an object or array).
      <code class="literal">
       JSON_TABLE()
      </code>
      extracts data as JSON then
          coerces it to the column type, using the regular automatic
          type conversion applying to JSON data in MySQL. A missing
          value triggers the
      <em class="replaceable">
       <code>
        on_empty
       </code>
      </em>
      clause.
          Saving an object or array triggers the optional
      <em class="replaceable">
       <code>
        on error
       </code>
      </em>
      clause; this also occurs
          when an error takes place during coercion from the value saved
          as JSON to the table column, such as trying to save the string
      <code class="literal">
       'asd'
      </code>
      to an integer column.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         name
        </code>
       </em>
       <em class="replaceable">
        <code>
         type
        </code>
       </em>
       EXISTS PATH
       <em class="replaceable">
        <code>
         path
        </code>
       </em>
      </code>
      : This column returns
          1 if any data is present at the location specified by
      <em class="replaceable">
       <code>
        path
       </code>
      </em>
      , and 0 otherwise.
      <em class="replaceable">
       <code>
        type
       </code>
      </em>
      can be any valid MySQL data
          type, but should normally be specified as some variety of
      <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
       <code class="literal">
        INT
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NESTED [PATH]
       <em class="replaceable">
        <code>
         path
        </code>
       </em>
       COLUMNS
          (
       <em class="replaceable">
        <code>
         column_list
        </code>
       </em>
       )
      </code>
      : This
          flattens nested objects or arrays in JSON data into a single
          row along with the JSON values from the parent object or
          array. Using multiple
      <code class="literal">
       PATH
      </code>
      options allows
          projection of JSON values from multiple levels of nesting into
          a single row.
     </p>
     <p>
      The
      <em class="replaceable">
       <code>
        path
       </code>
      </em>
      is relative to the parent
          path row path of
      <code class="literal">
       JSON_TABLE()
      </code>
      , or the path
          of the parent
      <code class="literal">
       NESTED [PATH]
      </code>
      clause in the
          event of nested paths.
     </p>
    </li>
   </ol>
  </div>
  <p>
   <em class="replaceable">
    <code>
     on empty
    </code>
   </em>
   , if specified, determines what
   <code class="literal">
    JSON_TABLE()
   </code>
   does in the event that data is
      missing (depending on type). This clause is also triggered on a
      column in a
   <code class="literal">
    NESTED PATH
   </code>
   clause when the latter
      has no match and a
   <code class="literal">
    NULL
   </code>
   complemented row is
      produced for it.
   <em class="replaceable">
    <code>
     on empty
    </code>
   </em>
   takes one of
      the following values:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       NULL ON EMPTY
      </code>
      : The column is set to
      <code class="literal">
       NULL
      </code>
      ; this is the default behavior.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DEFAULT
       <em class="replaceable">
        <code>
         json_string
        </code>
       </em>
       ON
          EMPTY
      </code>
      : the provided
      <em class="replaceable">
       <code>
        json_string
       </code>
      </em>
      is parsed as JSON, as
          long as it is valid, and stored instead of the missing value.
          Column type rules also apply to the default value.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ERROR ON EMPTY
      </code>
      : An error is thrown.
     </p>
    </li>
   </ul>
  </div>
  <p>
   If used,
   <em class="replaceable">
    <code>
     on_error
    </code>
   </em>
   takes one of the
      following values with the corresponding result as shown here:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       NULL ON ERROR
      </code>
      : The column is set to
      <code class="literal">
       NULL
      </code>
      ; this is the default behavior.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DEFAULT
       <em class="replaceable">
        <code>
         json string
        </code>
       </em>
       ON
          ERROR
      </code>
      : The
      <em class="replaceable">
       <code>
        json_string
       </code>
      </em>
      is
          parsed as JSON (provided that it is valid) and stored instead
          of the object or array.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ERROR ON ERROR
      </code>
      : An error is thrown.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Specifying
   <code class="literal">
    ON ERROR
   </code>
   before
   <code class="literal">
    ON
      EMPTY
   </code>
   is nonstandard and deprecated in MySQL; trying to
      do so causes the server to issue a warning. Expect support for the
      nonstandard syntax to be removed in a future version of MySQL.
  </p>
  <p>
   When a value saved to a column is truncated, such as saving
      3.14159 in a
   <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
    <code class="literal">
     DECIMAL(10,1)
    </code>
   </a>
   column,
      a warning is issued independently of any
   <code class="literal">
    ON
      ERROR
   </code>
   option. When multiple values are truncated in a
      single statement, the warning is issued only once.
  </p>
  <p>
   When the expression and path passed to this function resolve to
      JSON null,
   <code class="literal">
    JSON_TABLE()
   </code>
   returns SQL
   <code class="literal">
    NULL
   </code>
   , in accordance with the SQL standard, as
      shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa59487310"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">FROM</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">JSON_TABLE</span><span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>       <span class="token string">'[ {"c1": null} ]'</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>       <span class="token string">'$[*]'</span> <span class="token keyword">COLUMNS</span><span class="token punctuation">(</span> c1 <span class="token datatype">INT</span> <span class="token keyword">PATH</span> <span class="token string">'$.c1'</span> <span class="token keyword">ERROR</span> <span class="token keyword">ON</span> <span class="token keyword">ERROR</span> <span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">)</span> <span class="token keyword">as</span> jt<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> c1   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   The following query demonstrates the use of
   <code class="literal">
    ON
      EMPTY
   </code>
   and
   <code class="literal">
    ON ERROR
   </code>
   . The row
      corresponding to
   <code class="literal">
    {"b":1}
   </code>
   is empty for the path
   <code class="literal">
    "$.a"
   </code>
   , and attempting to save
   <code class="literal">
    [1,2]
   </code>
   as a scalar produces an error; these rows
      are highlighted in the output shown.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15697702"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">FROM</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">JSON_TABLE</span><span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     <span class="token string">'[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]'</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token string">"$[*]"</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">COLUMNS</span><span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>       rowid <span class="token keyword">FOR</span> <span class="token keyword">ORDINALITY</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>       ac <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token keyword">PATH</span> <span class="token string">"$.a"</span> <span class="token keyword">DEFAULT</span> <span class="token string">'111'</span> <span class="token keyword">ON</span> <span class="token keyword">EMPTY</span> <span class="token keyword">DEFAULT</span> <span class="token string">'999'</span> <span class="token keyword">ON</span> <span class="token keyword">ERROR</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>       aj <span class="token datatype">JSON</span> <span class="token keyword">PATH</span> <span class="token string">"$.a"</span> <span class="token keyword">DEFAULT</span> <span class="token string">'{"x": 333}'</span> <span class="token keyword">ON</span> <span class="token keyword">EMPTY</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>       bx <span class="token datatype">INT</span> <span class="token keyword">EXISTS</span> <span class="token keyword">PATH</span> <span class="token string">"$.b"</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>   <span class="token punctuation">)</span> <span class="token keyword">AS</span> tt<span class="token punctuation">;</span>

<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> rowid <span class="token punctuation">|</span> ac   <span class="token punctuation">|</span> aj         <span class="token punctuation">|</span> bx   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>     1 <span class="token punctuation">|</span> 3    <span class="token punctuation">|</span> "3"        <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>     2 <span class="token punctuation">|</span> 2    <span class="token punctuation">|</span> 2          <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation"></span><em><span class="token punctuation">|</span>     3 <span class="token punctuation">|</span> 111  <span class="token punctuation">|</span> {"x": 333} <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></em><span class="token punctuation"></span></span>
<span class="token output"><span class="token punctuation">|</span>     4 <span class="token punctuation">|</span> 0    <span class="token punctuation">|</span> 0          <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation"></span><em><span class="token punctuation">|</span>     5 <span class="token punctuation">|</span> 999  <span class="token punctuation">|</span> [1, 2]     <span class="token punctuation">|</span>    0 <span class="token punctuation">|</span></em><span class="token punctuation"></span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">5 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   Column names are subject to the usual rules and limitations
      governing table column names. See
   <a class="xref" href="identifiers.html" title="11.2 Schema Object Names">
    Section 11.2, “Schema Object Names”
   </a>
   .
  </p>
  <p>
   All JSON and JSON path expressions are checked for validity; an
      invalid expression of either type causes an error.
  </p>
  <p>
   Each match for the
   <em class="replaceable">
    <code>
     path
    </code>
   </em>
   preceding the
   <code class="literal">
    COLUMNS
   </code>
   keyword maps to an individual row in
      the result table. For example, the following query gives the
      result shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa84892648"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">FROM</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">JSON_TABLE</span><span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     <span class="token string">'[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]'</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token string">"$[*]"</span> <span class="token keyword">COLUMNS</span><span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>       xval <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token keyword">PATH</span> <span class="token string">"$.x"</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>       yval <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token keyword">PATH</span> <span class="token string">"$.y"</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>   <span class="token punctuation">)</span> <span class="token keyword">AS</span>  jt1<span class="token punctuation">;</span>

<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> xval <span class="token punctuation">|</span> yval <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2    <span class="token punctuation">|</span> 8    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 3    <span class="token punctuation">|</span> 7    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 4    <span class="token punctuation">|</span> 6    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   The expression
   <code class="literal">
    "$[*]"
   </code>
   matches each element of
      the array. You can filter the rows in the result by modifying the
      path. For example, using
   <code class="literal">
    "$[1]"
   </code>
   limits
      extraction to the second element of the JSON array used as the
      source, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75478032"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">FROM</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">JSON_TABLE</span><span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     <span class="token string">'[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]'</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token string">"$[1]"</span> <span class="token keyword">COLUMNS</span><span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>       xval <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token keyword">PATH</span> <span class="token string">"$.x"</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>       yval <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token keyword">PATH</span> <span class="token string">"$.y"</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>   <span class="token punctuation">)</span> <span class="token keyword">AS</span>  jt1<span class="token punctuation">;</span>

<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> xval <span class="token punctuation">|</span> yval <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 3    <span class="token punctuation">|</span> 7    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   Within a column definition,
   <code class="literal">
    "$"
   </code>
   passes the
      entire match to the column;
   <code class="literal">
    "$.x"
   </code>
   and
   <code class="literal">
    "$.y"
   </code>
   pass only the values corresponding to the
      keys
   <code class="literal">
    x
   </code>
   and
   <code class="literal">
    y
   </code>
   , respectively,
      within that match. For more information, see
   <a class="xref" href="json.html#json-path-syntax" title="JSON Path Syntax">
    JSON Path Syntax
   </a>
   .
  </p>
  <p>
   <code class="literal">
    NESTED PATH
   </code>
   (or simply
   <code class="literal">
    NESTED
   </code>
   ;
   <code class="literal">
    PATH
   </code>
   is optional)
      produces a set of records for each match in the
   <code class="literal">
    COLUMNS
   </code>
   clause to which it belongs. If there is
      no match, all columns of the nested path are set to
   <code class="literal">
    NULL
   </code>
   . This implements an outer join between the
      topmost clause and
   <code class="literal">
    NESTED [PATH]
   </code>
   . An inner join
      can be emulated by applying a suitable condition in the
   <code class="literal">
    WHERE
   </code>
   clause, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40620500"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">FROM</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">JSON_TABLE</span><span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     <span class="token string">'[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]'</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token string">'$[*]'</span> <span class="token keyword">COLUMNS</span><span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>             a <span class="token datatype">INT</span> <span class="token keyword">PATH</span> <span class="token string">'$.a'</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>             <span class="token keyword">NESTED</span> <span class="token keyword">PATH</span> <span class="token string">'$.b[*]'</span> <span class="token keyword">COLUMNS</span> <span class="token punctuation">(</span>b <span class="token datatype">INT</span> <span class="token keyword">PATH</span> <span class="token string">'$'</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>            <span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>    <span class="token punctuation">)</span> <span class="token keyword">AS</span> jt
<span class="token prompt">    -&gt;</span> <span class="token keyword">WHERE</span> b <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span>

<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>   11 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>  111 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span>   22 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span>  222 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   Sibling nested paths—that is, two or more instances of
   <code class="literal">
    NESTED [PATH]
   </code>
   in the same
   <code class="literal">
    COLUMNS
   </code>
   clause—are processed one after
      another, one at a time. While one nested path is producing
      records, columns of any sibling nested path expressions are set to
   <code class="literal">
    NULL
   </code>
   . This means that the total number of
      records for a single match within a single containing
   <code class="literal">
    COLUMNS
   </code>
   clause is the sum and not the product
      of all records produced by
   <code class="literal">
    NESTED [PATH]
   </code>
   modifiers, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55342212"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">FROM</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">JSON_TABLE</span><span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     <span class="token string">'[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]'</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token string">'$[*]'</span> <span class="token keyword">COLUMNS</span><span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>         a <span class="token datatype">INT</span> <span class="token keyword">PATH</span> <span class="token string">'$.a'</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>         <span class="token keyword">NESTED</span> <span class="token keyword">PATH</span> <span class="token string">'$.b[*]'</span> <span class="token keyword">COLUMNS</span> <span class="token punctuation">(</span>b1 <span class="token datatype">INT</span> <span class="token keyword">PATH</span> <span class="token string">'$'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>         <span class="token keyword">NESTED</span> <span class="token keyword">PATH</span> <span class="token string">'$.b[*]'</span> <span class="token keyword">COLUMNS</span> <span class="token punctuation">(</span>b2 <span class="token datatype">INT</span> <span class="token keyword">PATH</span> <span class="token string">'$'</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">)</span> <span class="token keyword">AS</span> jt<span class="token punctuation">;</span>

<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a    <span class="token punctuation">|</span> b1   <span class="token punctuation">|</span> b2   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>   11 <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>  111 <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>   11 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>  111 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span>   22 <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span>  222 <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>   22 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>  222 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   A
   <code class="literal">
    FOR ORDINALITY
   </code>
   column enumerates records
      produced by the
   <code class="literal">
    COLUMNS
   </code>
   clause, and can be used
      to distinguish parent records of a nested path, especially if
      values in parent records are the same, as can be seen here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71578329"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">FROM</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">JSON_TABLE</span><span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     <span class="token string">'[{"a": "a_val",
    '</span><span class="token operator">&gt;</span>       <span class="token string">"b"</span>: <span class="token punctuation">[</span>{<span class="token string">"c"</span>: <span class="token string">"c_val"</span><span class="token punctuation">,</span> <span class="token string">"l"</span>: <span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">]</span>}<span class="token punctuation">]</span>}<span class="token punctuation">,</span>
    <span class="token string">'&gt;     {"a": "a_val",
    '</span><span class="token operator">&gt;</span>       <span class="token string">"b"</span>: <span class="token punctuation">[</span>{<span class="token string">"c"</span>: <span class="token string">"c_val"</span><span class="token punctuation">,</span><span class="token string">"l"</span>: <span class="token punctuation">[</span><span class="token number">11</span><span class="token punctuation">]</span>}<span class="token punctuation">,</span> {<span class="token string">"c"</span>: <span class="token string">"c_val"</span><span class="token punctuation">,</span> <span class="token string">"l"</span>: <span class="token punctuation">[</span><span class="token number">22</span><span class="token punctuation">]</span>}<span class="token punctuation">]</span>}<span class="token punctuation">]</span><span class="token string">',
    -&gt;     '</span>$<span class="token punctuation">[</span><span class="token operator">*</span><span class="token punctuation">]</span><span class="token string">' COLUMNS(
    -&gt;       top_ord FOR ORDINALITY,
    -&gt;       apath VARCHAR(10) PATH '</span>$<span class="token punctuation">.</span>a<span class="token string">',
    -&gt;       NESTED PATH '</span>$<span class="token punctuation">.</span>b<span class="token punctuation">[</span><span class="token operator">*</span><span class="token punctuation">]</span><span class="token string">' COLUMNS (
    -&gt;         bpath VARCHAR(10) PATH '</span>$<span class="token punctuation">.</span>c<span class="token string">',
    -&gt;         ord FOR ORDINALITY,
    -&gt;         NESTED PATH '</span>$<span class="token punctuation">.</span>l<span class="token punctuation">[</span><span class="token operator">*</span><span class="token punctuation">]</span><span class="token string">' COLUMNS (lpath varchar(10) PATH '</span>$'<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>         <span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">)</span> <span class="token keyword">as</span> jt<span class="token punctuation">;</span>

<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> top_ord <span class="token punctuation">|</span> apath   <span class="token punctuation">|</span> bpath   <span class="token punctuation">|</span> ord  <span class="token punctuation">|</span> lpath <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>       1 <span class="token punctuation">|</span>  a_val  <span class="token punctuation">|</span>  c_val  <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span> 1     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>       1 <span class="token punctuation">|</span>  a_val  <span class="token punctuation">|</span>  c_val  <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span> 2     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>       2 <span class="token punctuation">|</span>  a_val  <span class="token punctuation">|</span>  c_val  <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span> 11    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>       2 <span class="token punctuation">|</span>  a_val  <span class="token punctuation">|</span>  c_val  <span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> 22    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   The source document contains an array of two elements; each of
      these elements produces two rows. The values of
   <code class="literal">
    apath
   </code>
   and
   <code class="literal">
    bpath
   </code>
   are the same
      over the entire result set; this means that they cannot be used to
      determine whether
   <code class="literal">
    lpath
   </code>
   values came from the
      same or different parents. The value of the
   <code class="literal">
    ord
   </code>
   column remains the same as the set of records having
   <code class="literal">
    top_ord
   </code>
   equal to 1, so these two values are
      from a single object. The remaining two values are from different
      objects, since they have different values in the
   <code class="literal">
    ord
   </code>
   column.
  </p>
  <p>
   Normally, you cannot join a derived table which depends on columns
      of preceding tables in the same
   <code class="literal">
    FROM
   </code>
   clause.
      MySQL, per the SQL standard, makes an exception for table
      functions; these are considered lateral derived tables. This is
      implicit, and for this reason is not allowed before
   <code class="literal">
    JSON_TABLE()
   </code>
   , also according to the standard.
  </p>
  <p>
   Suppose you have a table
   <code class="literal">
    t1
   </code>
   created and
      populated using the statements shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa69359500"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span><span class="token punctuation">,</span> c2 <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> c3 <span class="token datatype">JSON</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">VALUES</span>
	<span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'z'</span><span class="token punctuation">,</span> <span class="token function">JSON_OBJECT</span><span class="token punctuation">(</span><span class="token string">'a'</span><span class="token punctuation">,</span> <span class="token number">23</span><span class="token punctuation">,</span> <span class="token string">'b'</span><span class="token punctuation">,</span> <span class="token number">27</span><span class="token punctuation">,</span> <span class="token string">'c'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
	<span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'y'</span><span class="token punctuation">,</span> <span class="token function">JSON_OBJECT</span><span class="token punctuation">(</span><span class="token string">'a'</span><span class="token punctuation">,</span> <span class="token number">44</span><span class="token punctuation">,</span> <span class="token string">'b'</span><span class="token punctuation">,</span> <span class="token number">22</span><span class="token punctuation">,</span> <span class="token string">'c'</span><span class="token punctuation">,</span> <span class="token number">11</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
	<span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token string">'x'</span><span class="token punctuation">,</span> <span class="token function">JSON_OBJECT</span><span class="token punctuation">(</span><span class="token string">'b'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'c'</span><span class="token punctuation">,</span> <span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
	<span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token string">'w'</span><span class="token punctuation">,</span> <span class="token function">JSON_OBJECT</span><span class="token punctuation">(</span><span class="token string">'a'</span><span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">,</span> <span class="token string">'b'</span><span class="token punctuation">,</span> <span class="token number">6</span><span class="token punctuation">,</span> <span class="token string">'c'</span><span class="token punctuation">,</span> <span class="token number">7</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
	<span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span> <span class="token string">'v'</span><span class="token punctuation">,</span> <span class="token function">JSON_OBJECT</span><span class="token punctuation">(</span><span class="token string">'a'</span><span class="token punctuation">,</span> <span class="token number">123</span><span class="token punctuation">,</span> <span class="token string">'c'</span><span class="token punctuation">,</span> <span class="token number">1111</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You can then execute joins, such as this one, in which
   <code class="literal">
    JSON_TABLE()
   </code>
   acts as a derived table while at
      the same time it refers to a column in a previously referenced
      table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa52439386"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> c1<span class="token punctuation">,</span> c2<span class="token punctuation">,</span> <span class="token function">JSON_EXTRACT</span><span class="token punctuation">(</span>c3<span class="token punctuation">,</span> <span class="token string">'$.*'</span><span class="token punctuation">)</span> 
<span class="token keyword">FROM</span> t1 <span class="token keyword">AS</span> m 
<span class="token keyword">JOIN</span> 
<span class="token keyword">JSON_TABLE</span><span class="token punctuation">(</span>
  m<span class="token punctuation">.</span>c3<span class="token punctuation">,</span> 
  <span class="token string">'$.*'</span> 
  <span class="token keyword">COLUMNS</span><span class="token punctuation">(</span>
    <span class="token keyword">at</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">PATH</span> <span class="token string">'$.a'</span> <span class="token keyword">DEFAULT</span> <span class="token string">'1'</span> <span class="token keyword">ON</span> <span class="token keyword">EMPTY</span><span class="token punctuation">,</span> 
    bt <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">PATH</span> <span class="token string">'$.b'</span> <span class="token keyword">DEFAULT</span> <span class="token string">'2'</span> <span class="token keyword">ON</span> <span class="token keyword">EMPTY</span><span class="token punctuation">,</span> 
    ct <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">PATH</span> <span class="token string">'$.c'</span> <span class="token keyword">DEFAULT</span> <span class="token string">'3'</span> <span class="token keyword">ON</span> <span class="token keyword">EMPTY</span>
  <span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">AS</span> tt
<span class="token keyword">ON</span> m<span class="token punctuation">.</span>c1 <span class="token operator">&gt;</span> tt<span class="token punctuation">.</span><span class="token keyword">at</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Attempting to use the
   <code class="literal">
    LATERAL
   </code>
   keyword with this
      query raises
   <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_parse_error" target="_top">
    <code class="literal">
     ER_PARSE_ERROR
    </code>
   </a>
   .
  </p>
 </div>
 <br/>
</div>
