<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="json">
      </a>
      13.5 The JSON Data Type
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045211477120">
  </a>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="json.html#json-values" title="Creating JSON Values">
       Creating JSON Values
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="json.html#json-normalization" title="Normalization, Merging, and Autowrapping of JSON Values">
       Normalization, Merging, and Autowrapping of JSON Values
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="json.html#json-paths" title="Searching and Modifying JSON Values">
       Searching and Modifying JSON Values
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="json.html#json-path-syntax" title="JSON Path Syntax">
       JSON Path Syntax
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="json.html#json-comparison" title="Comparison and Ordering of JSON Values">
       Comparison and Ordering of JSON Values
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="json.html#json-converting-between-types" title="Converting between JSON and non-JSON values">
       Converting between JSON and non-JSON values
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="json.html#json-aggregation" title="Aggregation of JSON Values">
       Aggregation of JSON Values
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   MySQL supports a native
   <code class="literal">
    JSON
   </code>
   data type defined
      by
   <a class="ulink" href="https://tools.ietf.org/html/rfc7159" target="_blank">
    RFC
      7159
   </a>
   that enables efficient access to data in JSON
      (JavaScript Object Notation) documents. The
   <code class="literal">
    JSON
   </code>
   data type provides these advantages over
      storing JSON-format strings in a string column:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Automatic validation of JSON documents stored in
      <code class="literal">
       JSON
      </code>
      columns. Invalid documents produce an
          error.
     </p>
    </li>
    <li class="listitem">
     <p>
      Optimized storage format. JSON documents stored in
      <code class="literal">
       JSON
      </code>
      columns are converted to an internal
          format that permits quick read access to document elements.
          When the server later must read a JSON value stored in this
          binary format, the value need not be parsed from a text
          representation. The binary format is structured to enable the
          server to look up subobjects or nested values directly by key
          or array index without reading all values before or after them
          in the document.
     </p>
    </li>
   </ul>
  </div>
  <p>
   MySQL 8.4 also supports the
   <span class="emphasis">
    <em>
     JSON Merge
      Patch
    </em>
   </span>
   format defined in
   <a class="ulink" href="https://tools.ietf.org/html/rfc7396" target="_blank">
    RFC 7396
   </a>
   ,
      using the
   <a class="link" href="json-modification-functions.html#function_json-merge-patch">
    <code class="literal">
     JSON_MERGE_PATCH()
    </code>
   </a>
   function. See the description of this function, as well as
   <a class="xref" href="json.html#json-normalization" title="Normalization, Merging, and Autowrapping of JSON Values">
    Normalization, Merging, and Autowrapping of JSON Values
   </a>
   , for examples and further
      information.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    This discussion uses
    <code class="literal">
     JSON
    </code>
    in monotype to
        indicate specifically the JSON data type and
    <span class="quote">
     “
     <span class="quote">
      JSON
     </span>
     ”
    </span>
    in regular font to indicate JSON data in general.
   </p>
  </div>
  <p>
   The space required to store a
   <code class="literal">
    JSON
   </code>
   document is
      roughly the same as for
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     LONGBLOB
    </code>
   </a>
   or
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     LONGTEXT
    </code>
   </a>
   ; see
   <a class="xref" href="storage-requirements.html" title="13.7 Data Type Storage Requirements">
    Section 13.7, “Data Type Storage Requirements”
   </a>
   , for more information. It
      is important to keep in mind that the size of any JSON document
      stored in a
   <code class="literal">
    JSON
   </code>
   column is limited to the value
      of the
   <a class="link" href="server-system-variables.html#sysvar_max_allowed_packet">
    <code class="literal">
     max_allowed_packet
    </code>
   </a>
   system
      variable. (When the server is manipulating a JSON value internally
      in memory, it can be larger than this; the limit applies when the
      server stores it.) You can obtain the amount of space required to
      store a JSON document using the
   <a class="link" href="json-utility-functions.html#function_json-storage-size">
    <code class="literal">
     JSON_STORAGE_SIZE()
    </code>
   </a>
   function; note
      that for a
   <a class="link" href="json.html" title="13.5 The JSON Data Type">
    <code class="literal">
     JSON
    </code>
   </a>
   column, the storage
      size—and thus the value returned by this function—is
      that used by the column prior to any partial updates that may have
      been performed on it (see the discussion of the JSON partial
      update optimization later in this section).
  </p>
  <p>
   Along with the
   <code class="literal">
    JSON
   </code>
   data type, a set of SQL
      functions is available to enable operations on JSON values, such
      as creation, manipulation, and searching. The following discussion
      shows examples of these operations. For details about individual
      functions, see
   <a class="xref" href="json-functions.html" title="14.17 JSON Functions">
    Section 14.17, “JSON Functions”
   </a>
   .
  </p>
  <p>
   A set of spatial functions for operating on GeoJSON values is also
      available. See
   <a class="xref" href="spatial-geojson-functions.html" title="14.16.11 Spatial GeoJSON Functions">
    Section 14.16.11, “Spatial GeoJSON Functions”
   </a>
   .
  </p>
  <p>
   <code class="literal">
    JSON
   </code>
   columns, like columns of other binary
      types, are not indexed directly; instead, you can create an index
      on a generated column that extracts a scalar value from the
   <code class="literal">
    JSON
   </code>
   column. See
   <a class="xref" href="create-table-secondary-indexes.html#json-column-indirect-index" title="Indexing a Generated Column to Provide a JSON Column Index">
    Indexing a Generated Column to Provide a JSON Column Index
   </a>
   , for a detailed
      example.
  </p>
  <p>
   The MySQL optimizer also looks for compatible indexes on virtual
      columns that match JSON expressions.
  </p>
  <p>
   The
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   storage engine supports
      multi-valued indexes on JSON arrays. See
   <a class="xref" href="create-index.html#create-index-multi-valued" title="Multi-Valued Indexes">
    Multi-Valued Indexes
   </a>
   .
  </p>
  <p>
   MySQL NDB Cluster supports
   <code class="literal">
    JSON
   </code>
   columns and
      MySQL JSON functions, including creation of an index on a column
      generated from a
   <code class="literal">
    JSON
   </code>
   column as a workaround
      for being unable to index a
   <code class="literal">
    JSON
   </code>
   column. A
      maximum of 3
   <code class="literal">
    JSON
   </code>
   columns per
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   table is supported.
  </p>
  <h3>
   <a name="json-partial-updates">
   </a>
   Partial Updates of JSON Values
  </h3>
  <p>
   In MySQL 8.4, the optimizer can perform a partial,
      in-place update of a
   <code class="literal">
    JSON
   </code>
   column instead of
      removing the old document and writing the new document in its
      entirety to the column. This optimization can be performed for an
      update that meets the following conditions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The column being updated was declared as
      <code class="literal">
       JSON
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      statement uses any
          of the three functions
      <a class="link" href="json-modification-functions.html#function_json-set">
       <code class="literal">
        JSON_SET()
       </code>
      </a>
      ,
      <a class="link" href="json-modification-functions.html#function_json-replace">
       <code class="literal">
        JSON_REPLACE()
       </code>
      </a>
      , or
      <a class="link" href="json-modification-functions.html#function_json-remove">
       <code class="literal">
        JSON_REMOVE()
       </code>
      </a>
      to update the
          column. A direct assignment of the column value (for example,
      <code class="literal">
       UPDATE mytable SET jcol = '{"a": 10, "b":
          25}'
      </code>
      ) cannot be performed as a partial update.
     </p>
     <p>
      Updates of multiple
      <code class="literal">
       JSON
      </code>
      columns in a
          single
      <code class="literal">
       UPDATE
      </code>
      statement can be optimized in
          this fashion; MySQL can perform partial updates of only those
          columns whose values are updated using the three functions
          just listed.
     </p>
    </li>
    <li class="listitem">
     <p>
      The input column and the target column must be the same
          column; a statement such as
      <code class="literal">
       UPDATE mytable SET jcol1
          = JSON_SET(jcol2, '$.a', 100)
      </code>
      cannot be performed as
          a partial update.
     </p>
     <p>
      The update can use nested calls to any of the functions listed
          in the previous item, in any combination, as long as the input
          and target columns are the same.
     </p>
    </li>
    <li class="listitem">
     <p>
      All changes replace existing array or object values with new
          ones, and do not add any new elements to the parent object or
          array.
     </p>
    </li>
    <li class="listitem">
     <p>
      The value being replaced must be at least as large as the
          replacement value. In other words, the new value cannot be any
          larger than the old one.
     </p>
     <p>
      A possible exception to this requirement occurs when a
          previous partial update has left sufficient space for the
          larger value. You can use the function
      <a class="link" href="json-utility-functions.html#function_json-storage-free">
       <code class="literal">
        JSON_STORAGE_FREE()
       </code>
      </a>
      see how
          much space has been freed by any partial updates of a
      <code class="literal">
       JSON
      </code>
      column.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Such partial updates can be written to the binary log using a
      compact format that saves space; this can be enabled by setting
      the
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_row_value_options">
    <code class="literal">
     binlog_row_value_options
    </code>
   </a>
   system variable to
   <code class="literal">
    PARTIAL_JSON
   </code>
   .
  </p>
  <p>
   It is important to distinguish the partial update of a
   <code class="literal">
    JSON
   </code>
   column value stored in a table from
      writing the partial update of a row to the binary log. It is
      possible for the complete update of a
   <code class="literal">
    JSON
   </code>
   column to be recorded in the binary log as a partial update. This
      can happen when either (or both) of the last two conditions from
      the previous list is not met but the other conditions are
      satisfied.
  </p>
  <p>
   See also the description of
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_row_value_options">
    <code class="literal">
     binlog_row_value_options
    </code>
   </a>
   .
  </p>
  <p>
   The next few sections provide basic information regarding the
      creation and manipulation of JSON values.
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="json-values">
       </a>
       Creating JSON Values
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045211402704">
   </a>
   <a class="indexterm" name="idm46045211401216">
   </a>
   <a class="indexterm" name="idm46045211399728">
   </a>
   <a class="indexterm" name="idm46045211398240">
   </a>
   <a class="indexterm" name="idm46045211396752">
   </a>
   <a class="indexterm" name="idm46045211395264">
   </a>
   <a class="indexterm" name="idm46045211393776">
   </a>
   <a class="indexterm" name="idm46045211392288">
   </a>
   <a class="indexterm" name="idm46045211390800">
   </a>
   <a class="indexterm" name="idm46045211389312">
   </a>
   <a class="indexterm" name="idm46045211387824">
   </a>
   <a class="indexterm" name="idm46045211386336">
   </a>
   <a class="indexterm" name="idm46045211384848">
   </a>
   <a class="indexterm" name="idm46045211383360">
   </a>
   <a class="indexterm" name="idm46045211381872">
   </a>
   <a class="indexterm" name="idm46045211380384">
   </a>
   <p>
    A JSON array contains a list of values separated by commas and
        enclosed within
    <code class="literal">
     [
    </code>
    and
    <code class="literal">
     ]
    </code>
    characters:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-json"><div class="docs-select-all right" id="sa91705086"><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-json"><span class="token punctuation">[</span><span class="token string">"abc"</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">,</span> <span class="token null">null</span><span class="token punctuation">,</span> <span class="token boolean">true</span><span class="token punctuation">,</span> <span class="token boolean">false</span><span class="token punctuation">]</span></code></pre>
   </div>
   <p>
    A JSON object contains a set of key-value pairs separated by
        commas and enclosed within
    <code class="literal">
     {
    </code>
    and
    <code class="literal">
     }
    </code>
    characters:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-json"><div class="docs-select-all right" id="sa29829402"><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-json"><span class="token punctuation">{</span><span class="token property">"k1"</span><span class="token operator">:</span> <span class="token string">"value"</span><span class="token punctuation">,</span> <span class="token property">"k2"</span><span class="token operator">:</span> <span class="token number">10</span><span class="token punctuation">}</span></code></pre>
   </div>
   <p>
    As the examples illustrate, JSON arrays and objects can contain
        scalar values that are strings or numbers, the JSON null
        literal, or the JSON boolean true or false literals. Keys in
        JSON objects must be strings. Temporal (date, time, or datetime)
        scalar values are also permitted:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-json"><div class="docs-select-all right" id="sa63375450"><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-json"><span class="token punctuation">[</span><span class="token string">"12:18:29.000000"</span><span class="token punctuation">,</span> <span class="token string">"2015-07-29"</span><span class="token punctuation">,</span> <span class="token string">"2015-07-29 12:18:29.000000"</span><span class="token punctuation">]</span></code></pre>
   </div>
   <p>
    Nesting is permitted within JSON array elements and JSON object
        key values:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-json"><div class="docs-select-all right" id="sa83875838"><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-json"><span class="token punctuation">[</span><span class="token number">99</span><span class="token punctuation">,</span> <span class="token punctuation">{</span><span class="token property">"id"</span><span class="token operator">:</span> <span class="token string">"HK500"</span><span class="token punctuation">,</span> <span class="token property">"cost"</span><span class="token operator">:</span> <span class="token number">75.99</span><span class="token punctuation">}</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string">"hot"</span><span class="token punctuation">,</span> <span class="token string">"cold"</span><span class="token punctuation">]</span><span class="token punctuation">]</span>
<span class="token punctuation">{</span><span class="token property">"k1"</span><span class="token operator">:</span> <span class="token string">"value"</span><span class="token punctuation">,</span> <span class="token property">"k2"</span><span class="token operator">:</span> <span class="token punctuation">[</span><span class="token number">10</span><span class="token punctuation">,</span> <span class="token number">20</span><span class="token punctuation">]</span><span class="token punctuation">}</span></code></pre>
   </div>
   <p>
    You can also obtain JSON values from a number of functions
        supplied by MySQL for this purpose (see
    <a class="xref" href="json-creation-functions.html" title="14.17.2 Functions That Create JSON Values">
     Section 14.17.2, “Functions That Create JSON Values”
    </a>
    ) as well as by casting
        values of other types to the
    <code class="literal">
     JSON
    </code>
    type using
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST(
      <em class="replaceable">
       <code>
        value
       </code>
      </em>
      AS
        JSON)
     </code>
    </a>
    (see
    <a class="xref" href="json.html#json-converting-between-types" title="Converting between JSON and non-JSON values">
     Converting between JSON and non-JSON values
    </a>
    ). The next
        several paragraphs describe how MySQL handles JSON values
        provided as input.
   </p>
   <a class="indexterm" name="idm46045211365232">
   </a>
   <a class="indexterm" name="idm46045211364160">
   </a>
   <p>
    In MySQL, JSON values are written as strings. MySQL parses any
        string used in a context that requires a JSON value, and
        produces an error if it is not valid as JSON. These contexts
        include inserting a value into a column that has the
    <code class="literal">
     JSON
    </code>
    data type and passing an argument to a
        function that expects a JSON value (usually shown as
    <em class="replaceable">
     <code>
      json_doc
     </code>
    </em>
    or
    <em class="replaceable">
     <code>
      json_val
     </code>
    </em>
    in the documentation for
        MySQL JSON functions), as the following examples demonstrate:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Attempting to insert a value into a
       <code class="literal">
        JSON
       </code>
       column succeeds if the value is a valid JSON value, but
            fails if it is not:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa35706153"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>jdoc <span class="token datatype">JSON</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.20 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'{"key1": "value1", "key2": "value2"}'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.01 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'[1, 2,'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 3140 (22032) at line 2<span class="token punctuation">:</span> Invalid JSON text<span class="token punctuation">:</span>
"Invalid value." at position 6 in value (or column) '[1, 2,'.</span></code></pre>
      </div>
      <p>
       Positions for
       <span class="quote">
        “
        <span class="quote">
         at position
         <em class="replaceable">
          <code>
           N
          </code>
         </em>
        </span>
        ”
       </span>
       in such error messages
            are 0-based, but should be considered rough indications of
            where the problem in a value actually occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <a class="link" href="json-attribute-functions.html#function_json-type">
        <code class="literal">
         JSON_TYPE()
        </code>
       </a>
       function
            expects a JSON argument and attempts to parse it into a JSON
            value. It returns the value's JSON type if it is valid
            and produces an error otherwise:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa83089559"><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 function">JSON_TYPE</span><span class="token punctuation">(</span><span class="token string">'["a", "b", 1]'</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_TYPE('["a", "b", 1]') <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>
<span class="token output"><span class="token punctuation">|</span> ARRAY                      <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>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">JSON_TYPE</span><span class="token punctuation">(</span><span class="token string">'"hello"'</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_TYPE('"hello"') <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>
<span class="token output"><span class="token punctuation">|</span> STRING               <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>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">JSON_TYPE</span><span class="token punctuation">(</span><span class="token string">'hello'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 3146 (22032)<span class="token punctuation">:</span> Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.</span></code></pre>
      </div>
     </li>
    </ul>
   </div>
   <p>
    MySQL handles strings used in JSON context using the
    <code class="literal">
     utf8mb4
    </code>
    character set and
    <code class="literal">
     utf8mb4_bin
    </code>
    collation. Strings in other
        character sets are converted to
    <code class="literal">
     utf8mb4
    </code>
    as
        necessary. (For strings in the
    <code class="literal">
     ascii
    </code>
    or
    <code class="literal">
     utf8mb3
    </code>
    character sets, no conversion is
        needed because
    <code class="literal">
     ascii
    </code>
    and
    <code class="literal">
     utf8mb3
    </code>
    are subsets of
    <code class="literal">
     utf8mb4
    </code>
    .)
   </p>
   <p>
    As an alternative to writing JSON values using literal strings,
        functions exist for composing JSON values from component
        elements.
    <a class="link" href="json-creation-functions.html#function_json-array">
     <code class="literal">
      JSON_ARRAY()
     </code>
    </a>
    takes a
        (possibly empty) list of values and returns a JSON array
        containing those values:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa97797173"><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 function">JSON_ARRAY</span><span class="token punctuation">(</span><span class="token string">'a'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_ARRAY('a', 1, NOW())              <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>
<span class="token output"><span class="token punctuation">|</span> ["a", 1, "2015<span class="token punctuation">-</span>07<span class="token punctuation">-</span>27 09:43:47.000000"] <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></code></pre>
   </div>
   <p>
    <a class="link" href="json-creation-functions.html#function_json-object">
     <code class="literal">
      JSON_OBJECT()
     </code>
    </a>
    takes a (possibly
        empty) list of key-value pairs and returns a JSON object
        containing those pairs:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa62602742"><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 function">JSON_OBJECT</span><span class="token punctuation">(</span><span class="token string">'key1'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'key2'</span><span class="token punctuation">,</span> <span class="token string">'abc'</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_OBJECT('key1', 1, 'key2', 'abc') <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>
<span class="token output"><span class="token punctuation">|</span> {"key1": 1, "key2": "abc"}            <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></code></pre>
   </div>
   <p>
    <a class="link" href="json-modification-functions.html#function_json-merge-preserve">
     <code class="literal">
      JSON_MERGE_PRESERVE()
     </code>
    </a>
    takes two
        or more JSON documents and returns the combined result:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa68870711"><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 function">JSON_MERGE_PRESERVE</span><span class="token punctuation">(</span><span class="token string">'["a", 1]'</span><span class="token punctuation">,</span> <span class="token string">'{"key": "value"}'</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}') <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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", 1, {"key": "value"}]                          <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>
    For information about the merging rules, see
    <a class="xref" href="json.html#json-normalization" title="Normalization, Merging, and Autowrapping of JSON Values">
     Normalization, Merging, and Autowrapping of JSON Values
    </a>
    .
   </p>
   <p>
    (MySQL also supports
    <a class="link" href="json-modification-functions.html#function_json-merge-patch">
     <code class="literal">
      JSON_MERGE_PATCH()
     </code>
    </a>
    , which has
        somewhat different behavior. See
    <a class="xref" href="json-modification-functions.html#json-merge-patch-json-merge-preserve-compared" title="JSON_MERGE_PATCH() compared with JSON_MERGE_PRESERVE()">
     JSON_MERGE_PATCH() compared with JSON_MERGE_PRESERVE()
    </a>
    ,
        for information about the differences between these two
        functions.)
   </p>
   <p>
    JSON values can be assigned to user-defined variables:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13219347"><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">SET</span> <span class="token variable">@j</span> <span class="token operator">=</span> <span class="token function">JSON_OBJECT</span><span class="token punctuation">(</span><span class="token string">'key'</span><span class="token punctuation">,</span> <span class="token string">'value'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@j</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 class="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> @j               <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>
<span class="token output"><span class="token punctuation">|</span> {"key": "value"} <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></code></pre>
   </div>
   <p>
    However, user-defined variables cannot be of
    <code class="literal">
     JSON
    </code>
    data type, so although
    <code class="literal">
     @j
    </code>
    in the preceding example looks like a JSON
        value and has the same character set and collation as a JSON
        value, it does
    <span class="emphasis">
     <em>
      not
     </em>
    </span>
    have the
    <code class="literal">
     JSON
    </code>
    data type. Instead, the result from
    <a class="link" href="json-creation-functions.html#function_json-object">
     <code class="literal">
      JSON_OBJECT()
     </code>
    </a>
    is converted to a
        string when assigned to the variable.
   </p>
   <p>
    Strings produced by converting JSON values have a character set
        of
    <code class="literal">
     utf8mb4
    </code>
    and a collation of
    <code class="literal">
     utf8mb4_bin
    </code>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa38756740"><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 function">CHARSET</span><span class="token punctuation">(</span><span class="token variable">@j</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">COLLATION</span><span class="token punctuation">(</span><span class="token variable">@j</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> CHARSET(@j) <span class="token punctuation">|</span> COLLATION(@j) <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>
<span class="token output"><span class="token punctuation">|</span> utf8mb4     <span class="token punctuation">|</span> utf8mb4_bin   <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></code></pre>
   </div>
   <p>
    Because
    <code class="literal">
     utf8mb4_bin
    </code>
    is a binary collation,
        comparison of JSON values is case-sensitive.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa16032522"><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 function">JSON_ARRAY</span><span class="token punctuation">(</span><span class="token string">'x'</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token function">JSON_ARRAY</span><span class="token punctuation">(</span><span class="token string">'X'</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_ARRAY('x') = JSON_ARRAY('X') <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>
<span class="token output"><span class="token punctuation">|</span>                                 0 <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></code></pre>
   </div>
   <a class="indexterm" name="idm46045211311200">
   </a>
   <p>
    Case sensitivity also applies to the JSON
    <code class="literal">
     null
    </code>
    ,
    <code class="literal">
     true
    </code>
    , and
    <code class="literal">
     false
    </code>
    literals, which always must be written
        in lowercase:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa93824205"><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 function">JSON_VALID</span><span class="token punctuation">(</span><span class="token string">'null'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">JSON_VALID</span><span class="token punctuation">(</span><span class="token string">'Null'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">JSON_VALID</span><span class="token punctuation">(</span><span class="token string">'NULL'</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_VALID('null') <span class="token punctuation">|</span> JSON_VALID('Null') <span class="token punctuation">|</span> JSON_VALID('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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>                  0 <span class="token punctuation">|</span>                  0 <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token string">'null'</span> <span class="token keyword">AS</span> <span class="token datatype">JSON</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> CAST('null' AS JSON) <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>
<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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token string">'NULL'</span> <span class="token keyword">AS</span> <span class="token datatype">JSON</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 3141 (22032)<span class="token punctuation">:</span> Invalid JSON text in argument 1 to function cast_as_json<span class="token punctuation">:</span>
"Invalid value." at position 0 in 'NULL'.</span></code></pre>
   </div>
   <p>
    Case sensitivity of the JSON literals differs from that of the
        SQL
    <code class="literal">
     NULL
    </code>
    ,
    <code class="literal">
     TRUE
    </code>
    , and
    <code class="literal">
     FALSE
    </code>
    literals, which can be written in any
        lettercase:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa74763494"><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 function">ISNULL</span><span class="token punctuation">(</span><span class="token boolean">null</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">ISNULL</span><span class="token punctuation">(</span><span class="token boolean">Null</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">ISNULL</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</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 class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> ISNULL(null) <span class="token punctuation">|</span> ISNULL(Null) <span class="token punctuation">|</span> ISNULL(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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>            1 <span class="token punctuation">|</span>            1 <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>
   <a class="indexterm" name="idm46045211298256">
   </a>
   <p>
    Sometimes it may be necessary or desirable to insert quote
        characters (
    <code class="literal">
     "
    </code>
    or
    <code class="literal">
     '
    </code>
    ) into a
        JSON document. Assume for this example that you want to insert
        some JSON objects containing strings representing sentences that
        state some facts about MySQL, each paired with an appropriate
        keyword, into a table created using the SQL statement shown
        here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa64337527"><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">CREATE</span> <span class="token keyword">TABLE</span> facts <span class="token punctuation">(</span>sentence <span class="token datatype">JSON</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Among these keyword-sentence pairs is this one:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-simple"><div class="docs-select-all right" id="sa87809980"><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-simple">mascot<span class="token operator">:</span> The MySQL mascot is a dolphin named "Sakila"<span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    One way to insert this as a JSON object into the
    <code class="literal">
     facts
    </code>
    table is to use the MySQL
    <a class="link" href="json-creation-functions.html#function_json-object">
     <code class="literal">
      JSON_OBJECT()
     </code>
    </a>
    function. In this
        case, you must escape each quote character using a backslash, as
        shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa62648737"><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">INSERT</span> <span class="token keyword">INTO</span> facts <span class="token keyword">VALUES</span>
     <span class="token operator">&gt;</span>   <span class="token punctuation">(</span><span class="token function">JSON_OBJECT</span><span class="token punctuation">(</span><span class="token string">"mascot"</span><span class="token punctuation">,</span> <span class="token string">"Our mascot is a dolphin named \"Sakila\"."</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    This does not work in the same way if you insert the value as a
        JSON object literal, in which case, you must use the double
        backslash escape sequence, like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa97328696"><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">INSERT</span> <span class="token keyword">INTO</span> facts <span class="token keyword">VALUES</span>
     <span class="token operator">&gt;</span>   <span class="token punctuation">(</span><span class="token string">'{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Using the double backslash keeps MySQL from performing escape
        sequence processing, and instead causes it to pass the string
        literal to the storage engine for processing. After inserting
        the JSON object in either of the ways just shown, you can see
        that the backslashes are present in the JSON column value by
        doing a simple
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    , like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa37482119"><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> sentence <span class="token keyword">FROM</span> facts<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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> sentence                                                <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> {"mascot": "Our mascot is a dolphin named \"Sakila\"."} <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>
    To look up this particular sentence employing
    <code class="literal">
     mascot
    </code>
    as the key, you can use the
        column-path operator
    <a class="link" href="json-search-functions.html#operator_json-column-path">
     <code class="literal">
      -&gt;
     </code>
    </a>
    ,
        as shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30887842"><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> col<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">"$.mascot"</span> <span class="token keyword">FROM</span> qtest<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 class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> col<span class="token punctuation">-</span>&gt;"$.mascot"                             <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 class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> "Our mascot is a dolphin named \"Sakila\"." <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 class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
   </div>
   <p>
    This leaves the backslashes intact, along with the surrounding
        quote marks. To display the desired value using
    <code class="literal">
     mascot
    </code>
    as the key, but without including the
        surrounding quote marks or any escapes, use the inline path
        operator
    <a class="link" href="json-search-functions.html#operator_json-inline-path">
     <code class="literal">
      -&gt;&gt;
     </code>
    </a>
    ,
        like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51819213"><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> sentence<span class="token operator">-</span><span class="token operator">&gt;&gt;</span><span class="token string">"$.mascot"</span> <span class="token keyword">FROM</span> facts<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>
<span class="token output"><span class="token punctuation">|</span> sentence<span class="token punctuation">-</span>&gt;&gt;"$.mascot"                   <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>
<span class="token output"><span class="token punctuation">|</span> Our mascot is a dolphin named "Sakila". <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></code></pre>
   </div>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     The previous example does not work as shown if the
     <a class="link" href="sql-mode.html#sqlmode_no_backslash_escapes">
      <code class="literal">
       NO_BACKSLASH_ESCAPES
      </code>
     </a>
     server
          SQL mode is enabled. If this mode is set, a single backslash
          instead of double backslashes can be used to insert the JSON
          object literal, and the backslashes are preserved. If you use
          the
     <code class="literal">
      JSON_OBJECT()
     </code>
     function when performing
          the insert and this mode is set, you must alternate single and
          double quotes, like this:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa84813147"><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">INSERT</span> <span class="token keyword">INTO</span> facts <span class="token keyword">VALUES</span>
     <span class="token operator">&gt;</span> <span class="token punctuation">(</span><span class="token function">JSON_OBJECT</span><span class="token punctuation">(</span><span class="token string">'mascot'</span><span class="token punctuation">,</span> <span class="token string">'Our mascot is a dolphin named "Sakila".'</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
    </div>
    <p>
     See the description of the
     <a class="link" href="json-modification-functions.html#function_json-unquote">
      <code class="literal">
       JSON_UNQUOTE()
      </code>
     </a>
     function for
          more information about the effects of this mode on escaped
          characters in JSON values.
    </p>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="json-normalization">
       </a>
       Normalization, Merging, and Autowrapping of JSON Values
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045211261312">
   </a>
   <a class="indexterm" name="idm46045211259856">
   </a>
   <a class="indexterm" name="idm46045211258784">
   </a>
   <a class="indexterm" name="idm46045211257296">
   </a>
   <a class="indexterm" name="idm46045211256224">
   </a>
   <a class="indexterm" name="idm46045211254736">
   </a>
   <p>
    When a string is parsed and found to be a valid JSON document,
        it is also normalized. This means that members with keys that
        duplicate a key found later in the document, reading from left
        to right, are discarded. The object value produced by the
        following
    <a class="link" href="json-creation-functions.html#function_json-object">
     <code class="literal">
      JSON_OBJECT()
     </code>
    </a>
    call
        includes only the second
    <code class="literal">
     key1
    </code>
    element because
        that key name occurs earlier in the value, as shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40851271"><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 function">JSON_OBJECT</span><span class="token punctuation">(</span><span class="token string">'key1'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'key2'</span><span class="token punctuation">,</span> <span class="token string">'abc'</span><span class="token punctuation">,</span> <span class="token string">'key1'</span><span class="token punctuation">,</span> <span class="token string">'def'</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> {"key1": "def", "key2": "abc"}                       <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>
    Normalization is also performed when values are inserted into
        JSON columns, as shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa49953411"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">JSON</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span>
     <span class="token operator">&gt;</span>     <span class="token punctuation">(</span><span class="token string">'{"x": 17, "x": "red"}'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
     <span class="token operator">&gt;</span>     <span class="token punctuation">(</span><span class="token string">'{"x": 17, "x": "red", "x": [3, 5, 7]}'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> c1 <span class="token keyword">FROM</span> t1<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>
<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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> {"x": "red"}     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> {"x": [3, 5, 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 class="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>
    This
    <span class="quote">
     “
     <span class="quote">
      last duplicate key wins
     </span>
     ”
    </span>
    behavior is
        suggested by
    <a class="ulink" href="https://tools.ietf.org/html/rfc7159" target="_blank">
     RFC
        7159
    </a>
    and is implemented by most JavaScript parsers. (Bug
        #86866, Bug #26369555)
   </p>
   <p>
    MySQL discards extra whitespace between keys, values, or
        elements in the original JSON document, and leaves (or inserts,
        when necessary) a single space following each comma
        (
    <code class="literal">
     ,
    </code>
    ) or colon (
    <code class="literal">
     :
    </code>
    ) when
        displaying it. This is done to enhance readability.
   </p>
   <p>
    MySQL functions that produce JSON values (see
    <a class="xref" href="json-creation-functions.html" title="14.17.2 Functions That Create JSON Values">
     Section 14.17.2, “Functions That Create JSON Values”
    </a>
    ) always return
        normalized values.
   </p>
   <p>
    To make lookups more efficient, MySQL also sorts the keys of a
        JSON object.
    <span class="emphasis">
     <em>
      You should be aware that the result of
        this ordering is subject to change and not guaranteed to be
        consistent across releases
     </em>
    </span>
    .
   </p>
   <h4>
    <a name="json-merging">
    </a>
    Merging JSON Values
   </h4>
   <a class="indexterm" name="idm46045211235824">
   </a>
   <a class="indexterm" name="idm46045211234752">
   </a>
   <a class="indexterm" name="idm46045211233680">
   </a>
   <a class="indexterm" name="idm46045211232608">
   </a>
   <p>
    Two merging algorithms are supported, implemented by the
        functions
    <a class="link" href="json-modification-functions.html#function_json-merge-preserve">
     <code class="literal">
      JSON_MERGE_PRESERVE()
     </code>
    </a>
    and
    <a class="link" href="json-modification-functions.html#function_json-merge-patch">
     <code class="literal">
      JSON_MERGE_PATCH()
     </code>
    </a>
    . These
        differ in how they handle duplicate keys:
    <a class="link" href="json-modification-functions.html#function_json-merge-preserve">
     <code class="literal">
      JSON_MERGE_PRESERVE()
     </code>
    </a>
    retains
        values for duplicate keys, while
    <a class="link" href="json-modification-functions.html#function_json-merge-patch">
     <code class="literal">
      JSON_MERGE_PATCH()
     </code>
    </a>
    discards all
        but the last value. The next few paragraphs explain how each of
        these two functions handles the merging of different
        combinations of JSON documents (that is, of objects and arrays).
   </p>
   <p>
    <b>
     Merging arrays.
    </b>
    In contexts that combine multiple arrays, the arrays are
          merged into a single array.
    <code class="literal">
     JSON_MERGE_PRESERVE()
    </code>
    does this by
          concatenating arrays named later to the end of the first
          array.
    <code class="literal">
     JSON_MERGE_PATCH()
    </code>
    considers each
          argument as an array consisting of a single element (thus
          having 0 as its index) and then applies
    <span class="quote">
     “
     <span class="quote">
      last duplicate
          key wins
     </span>
     ”
    </span>
    logic to select only the last argument. You
          can compare the results shown by this query:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa47027224"><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 prompt">    -&gt;</span>   <span class="token function">JSON_MERGE_PRESERVE</span><span class="token punctuation">(</span><span class="token string">'[1, 2]'</span><span class="token punctuation">,</span> <span class="token string">'["a", "b", "c"]'</span><span class="token punctuation">,</span> <span class="token string">'[true, false]'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">Preserve</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>   <span class="token function">JSON_MERGE_PATCH</span><span class="token punctuation">(</span><span class="token string">'[1, 2]'</span><span class="token punctuation">,</span> <span class="token string">'["a", "b", "c"]'</span><span class="token punctuation">,</span> <span class="token string">'[true, false]'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> Patch\G
<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> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
Preserve<span class="token punctuation">:</span> [1, 2, "a", "b", "c", true, false]
   Patch<span class="token punctuation">:</span> [true, false]</span></code></pre>
   </div>
   <p>
    Multiple objects when merged produce a single object.
    <code class="literal">
     JSON_MERGE_PRESERVE()
    </code>
    handles multiple
        objects having the same key by combining all unique values for
        that key in an array; this array is then used as the value for
        that key in the result.
    <code class="literal">
     JSON_MERGE_PATCH()
    </code>
    discards values for which duplicate keys are found, working from
        left to right, so that the result contains only the last value
        for that key. The following query illustrates the difference in
        the results for the duplicate key
    <code class="literal">
     a
    </code>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa41568547"><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 prompt">    -&gt;</span>   <span class="token function">JSON_MERGE_PRESERVE</span><span class="token punctuation">(</span><span class="token string">'{"a": 1, "b": 2}'</span><span class="token punctuation">,</span> <span class="token string">'{"c": 3, "a": 4}'</span><span class="token punctuation">,</span> <span class="token string">'{"c": 5, "d": 3}'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">Preserve</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>   <span class="token function">JSON_MERGE_PATCH</span><span class="token punctuation">(</span><span class="token string">'{"a": 3, "b": 2}'</span><span class="token punctuation">,</span> <span class="token string">'{"c": 3, "a": 4}'</span><span class="token punctuation">,</span> <span class="token string">'{"c": 5, "d": 3}'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> Patch\G
<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> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
Preserve<span class="token punctuation">:</span> {"a"<span class="token punctuation">:</span> [1, 4], "b"<span class="token punctuation">:</span> 2, "c"<span class="token punctuation">:</span> [3, 5], "d"<span class="token punctuation">:</span> 3}
   Patch<span class="token punctuation">:</span> {"a"<span class="token punctuation">:</span> 4, "b"<span class="token punctuation">:</span> 2, "c"<span class="token punctuation">:</span> 5, "d"<span class="token punctuation">:</span> 3}</span></code></pre>
   </div>
   <p>
    Nonarray values used in a context that requires an array value
        are autowrapped: The value is surrounded by
    <code class="literal">
     [
    </code>
    and
    <code class="literal">
     ]
    </code>
    characters to convert it to an array.
        In the following statement, each argument is autowrapped as an
        array (
    <code class="literal">
     [1]
    </code>
    ,
    <code class="literal">
     [2]
    </code>
    ). These
        are then merged to produce a single result array; as in the
        previous two cases,
    <code class="literal">
     JSON_MERGE_PRESERVE()
    </code>
    combines values having the same key while
    <code class="literal">
     JSON_MERGE_PATCH()
    </code>
    discards values for all
        duplicate keys except the last, as shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa61842770"><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 prompt">	  -&gt;</span>   <span class="token function">JSON_MERGE_PRESERVE</span><span class="token punctuation">(</span><span class="token string">'1'</span><span class="token punctuation">,</span> <span class="token string">'2'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">Preserve</span><span class="token punctuation">,</span>
<span class="token prompt">	  -&gt;</span>   <span class="token function">JSON_MERGE_PATCH</span><span class="token punctuation">(</span><span class="token string">'1'</span><span class="token punctuation">,</span> <span class="token string">'2'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> Patch\G
<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> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
Preserve<span class="token punctuation">:</span> [1, 2]
   Patch<span class="token punctuation">:</span> 2</span></code></pre>
   </div>
   <p>
    Array and object values are merged by autowrapping the object as
        an array and merging the arrays by combining values or by
    <span class="quote">
     “
     <span class="quote">
      last duplicate key wins
     </span>
     ”
    </span>
    according to the choice
        of merging function (
    <code class="literal">
     JSON_MERGE_PRESERVE()
    </code>
    or
    <code class="literal">
     JSON_MERGE_PATCH()
    </code>
    , respectively), as can be
        seen in this example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa44999392"><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 prompt">	  -&gt;</span>   <span class="token function">JSON_MERGE_PRESERVE</span><span class="token punctuation">(</span><span class="token string">'[10, 20]'</span><span class="token punctuation">,</span> <span class="token string">'{"a": "x", "b": "y"}'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">Preserve</span><span class="token punctuation">,</span>
<span class="token prompt">	  -&gt;</span>   <span class="token function">JSON_MERGE_PATCH</span><span class="token punctuation">(</span><span class="token string">'[10, 20]'</span><span class="token punctuation">,</span> <span class="token string">'{"a": "x", "b": "y"}'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> Patch\G
<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> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
Preserve<span class="token punctuation">:</span> [10, 20, {"a"<span class="token punctuation">:</span> "x", "b"<span class="token punctuation">:</span> "y"}]
   Patch<span class="token punctuation">:</span> {"a"<span class="token punctuation">:</span> "x", "b"<span class="token punctuation">:</span> "y"}</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="json-paths">
       </a>
       Searching and Modifying JSON Values
      </h3>
     </div>
    </div>
   </div>
   <p>
    A JSON path expression selects a value within a JSON document.
   </p>
   <p>
    Path expressions are useful with functions that extract parts of
        or modify a JSON document, to specify where within that document
        to operate. For example, the following query extracts from a
        JSON document the value of the member with the
    <code class="literal">
     name
    </code>
    key:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40792412"><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 function">JSON_EXTRACT</span><span class="token punctuation">(</span><span class="token string">'{"id": 14, "name": "Aztalan"}'</span><span class="token punctuation">,</span> <span class="token string">'$.name'</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> "Aztalan"                                               <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>
    Path syntax uses a leading
    <code class="literal">
     $
    </code>
    character to
        represent the JSON document under consideration, optionally
        followed by selectors that indicate successively more specific
        parts of the document:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       A period followed by a key name names the member in an
            object with the given key. The key name must be specified
            within double quotation marks if the name without quotes is
            not legal within path expressions (for example, if it
            contains a space).
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        [
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
        ]
       </code>
       appended
            to a
       <em class="replaceable">
        <code>
         path
        </code>
       </em>
       that selects an array
            names the value at position
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       within the array. Array positions are integers beginning
            with zero. If
       <em class="replaceable">
        <code>
         path
        </code>
       </em>
       does not
            select an array value,
       <em class="replaceable">
        <code>
         path
        </code>
       </em>
       [0]
            evaluates to the same value as
       <em class="replaceable">
        <code>
         path
        </code>
       </em>
       :
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14715258"><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 function">JSON_SET</span><span class="token punctuation">(</span><span class="token string">'"x"'</span><span class="token punctuation">,</span> <span class="token string">'$[0]'</span><span class="token punctuation">,</span> <span class="token string">'a'</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_SET('"x"', '$[0]', 'a') <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>
<span class="token output"><span class="token punctuation">|</span> "a"                          <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>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        [
        <em class="replaceable">
         <code>
          M
         </code>
        </em>
        to
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
        ]
       </code>
       specifies a subset
            or range of array values starting with the value at position
       <em class="replaceable">
        <code>
         M
        </code>
       </em>
       , and ending with the value at
            position
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       .
      </p>
      <p>
       <code class="literal">
        last
       </code>
       is supported as a synonym for the
            index of the rightmost array element. Relative addressing of
            array elements is also supported. If
       <em class="replaceable">
        <code>
         path
        </code>
       </em>
       does not select an array
            value,
       <em class="replaceable">
        <code>
         path
        </code>
       </em>
       [last] evaluates to
            the same value as
       <em class="replaceable">
        <code>
         path
        </code>
       </em>
       , as shown
            later in this section (see
       <a class="xref" href="json.html#json-paths-last" title="Rightmost array element">
        Rightmost array element
       </a>
       ).
      </p>
     </li>
     <li class="listitem">
      <p>
       Paths can contain
       <code class="literal">
        *
       </code>
       or
       <code class="literal">
        **
       </code>
       wildcards:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <code class="literal">
           .[*]
          </code>
          evaluates to the values of all
                members in a JSON object.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           [*]
          </code>
          evaluates to the values of all
                elements in a JSON array.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           <em class="replaceable">
            <code>
             prefix
            </code>
           </em>
           **
           <em class="replaceable">
            <code>
             suffix
            </code>
           </em>
          </code>
          evaluates to all paths that begin with the named prefix
                and end with the named suffix.
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       A path that does not exist in the document (evaluates to
            nonexistent data) evaluates to
       <code class="literal">
        NULL
       </code>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    Let
    <code class="literal">
     $
    </code>
    refer to this JSON array with three
        elements:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-json"><div class="docs-select-all right" id="sa67381214"><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-json"><span class="token punctuation">[</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token punctuation">{</span><span class="token property">"a"</span><span class="token operator">:</span> <span class="token punctuation">[</span><span class="token number">5</span><span class="token punctuation">,</span> <span class="token number">6</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token property">"b"</span><span class="token operator">:</span> <span class="token number">10</span><span class="token punctuation">}</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">99</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">]</span><span class="token punctuation">]</span></code></pre>
   </div>
   <p>
    Then:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        $[0]
       </code>
       evaluates to
       <code class="literal">
        3
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        $[1]
       </code>
       evaluates to
       <code class="literal">
        {"a": [5, 6],
            "b": 10}
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        $[2]
       </code>
       evaluates to
       <code class="literal">
        [99,
            100]
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        $[3]
       </code>
       evaluates to
       <code class="literal">
        NULL
       </code>
       (it refers to the fourth array element, which does not
            exist).
      </p>
     </li>
    </ul>
   </div>
   <p>
    Because
    <code class="literal">
     $[1]
    </code>
    and
    <code class="literal">
     $[2]
    </code>
    evaluate to nonscalar values, they can be used as the basis for
        more-specific path expressions that select nested values.
        Examples:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        $[1].a
       </code>
       evaluates to
       <code class="literal">
        [5,
            6]
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        $[1].a[1]
       </code>
       evaluates to
       <code class="literal">
        6
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        $[1].b
       </code>
       evaluates to
       <code class="literal">
        10
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        $[2][0]
       </code>
       evaluates to
       <code class="literal">
        99
       </code>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    As mentioned previously, path components that name keys must be
        quoted if the unquoted key name is not legal in path
        expressions. Let
    <code class="literal">
     $
    </code>
    refer to this value:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-json"><div class="docs-select-all right" id="sa14100894"><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-json"><span class="token punctuation">{</span><span class="token property">"a fish"</span><span class="token operator">:</span> <span class="token string">"shark"</span><span class="token punctuation">,</span> <span class="token property">"a bird"</span><span class="token operator">:</span> <span class="token string">"sparrow"</span><span class="token punctuation">}</span></code></pre>
   </div>
   <p>
    The keys both contain a space and must be quoted:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        $."a fish"
       </code>
       evaluates to
       <code class="literal">
        shark
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        $."a bird"
       </code>
       evaluates to
       <code class="literal">
        sparrow
       </code>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    Paths that use wildcards evaluate to an array that can contain
        multiple values:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa21049783"><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 function">JSON_EXTRACT</span><span class="token punctuation">(</span><span class="token string">'{"a": 1, "b": 2, "c": [3, 4, 5]}'</span><span class="token punctuation">,</span> <span class="token string">'$.*'</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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, 2, [3, 4, 5]]                                       <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">JSON_EXTRACT</span><span class="token punctuation">(</span><span class="token string">'{"a": 1, "b": 2, "c": [3, 4, 5]}'</span><span class="token punctuation">,</span> <span class="token string">'$.c[*]'</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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, 4, 5]                                                  <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>
    In the following example, the path
    <code class="literal">
     $**.b
    </code>
    evaluates to multiple paths (
    <code class="literal">
     $.a.b
    </code>
    and
    <code class="literal">
     $.c.b
    </code>
    ) and produces an array of the matching
        path values:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42658586"><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 function">JSON_EXTRACT</span><span class="token punctuation">(</span><span class="token string">'{"a": {"b": 1}, "c": {"b": 2}}'</span><span class="token punctuation">,</span> <span class="token string">'$**.b'</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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, 2]                                                  <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 name="json-paths-ranges">
    </a>
    <b>
     Ranges from JSON arrays.
    </b>
    You can use ranges with the
    <code class="literal">
     to
    </code>
    keyword to
          specify subsets of JSON arrays. For example,
    <code class="literal">
     $[1 to
          3]
    </code>
    includes the second, third, and fourth elements
          of an array, as shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71426916"><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 function">JSON_EXTRACT</span><span class="token punctuation">(</span><span class="token string">'[1, 2, 3, 4, 5]'</span><span class="token punctuation">,</span> <span class="token string">'$[1 to 3]'</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') <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 class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> [2, 3, 4]                                    <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 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 syntax is
    <code class="literal">
     <em class="replaceable">
      <code>
       M
      </code>
     </em>
     to
     <em class="replaceable">
      <code>
       N
      </code>
     </em>
    </code>
    , where
    <em class="replaceable">
     <code>
      M
     </code>
    </em>
    and
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    are, respectively, the first and last indexes of a range of
        elements from a JSON array.
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    must be
        greater than
    <em class="replaceable">
     <code>
      M
     </code>
    </em>
    ;
    <em class="replaceable">
     <code>
      M
     </code>
    </em>
    must be greater than or equal to 0.
        Array elements are indexed beginning with 0.
   </p>
   <p>
    You can use ranges in contexts where wildcards are supported.
   </p>
   <p>
    <a name="json-paths-last">
    </a>
    <b>
     Rightmost array element.
    </b>
    The
    <code class="literal">
     last
    </code>
    keyword is supported as a synonym
          for the index of the last element in an array. Expressions of
          the form
    <code class="literal">
     last -
     <em class="replaceable">
      <code>
       N
      </code>
     </em>
    </code>
    can be used for
          relative addressing, and within range definitions, like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa20377254"><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 function">JSON_EXTRACT</span><span class="token punctuation">(</span><span class="token string">'[1, 2, 3, 4, 5]'</span><span class="token punctuation">,</span> <span class="token string">'$[last-3 to last-1]'</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last<span class="token punctuation">-</span>3 to last<span class="token punctuation">-</span>1]') <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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, 3, 4]                                              <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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.01 sec)</span></code></pre>
   </div>
   <p>
    If the path is evaluated against a value that is not an array,
        the result of the evaluation is the same as if the value had
        been wrapped in a single-element array:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa59248973"><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 function">JSON_REPLACE</span><span class="token punctuation">(</span><span class="token string">'"Sakila"'</span><span class="token punctuation">,</span> <span class="token string">'$[last]'</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_REPLACE('"Sakila"', '$[last]', 10) <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>
<span class="token output"><span class="token punctuation">|</span> 10                                      <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>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
   </div>
   <p>
    You can use
    <a class="link" href="json-search-functions.html#operator_json-column-path">
     <code class="literal">
      <em class="replaceable">
       <code>
        column
       </code>
      </em>
      -&gt;
      <em class="replaceable">
       <code>
        path
       </code>
      </em>
     </code>
    </a>
    with a JSON column identifier and JSON path expression as a
        synonym for
    <a class="link" href="json-search-functions.html#function_json-extract">
     <code class="literal">
      JSON_EXTRACT(
      <em class="replaceable">
       <code>
        column
       </code>
      </em>
      ,
      <em class="replaceable">
       <code>
        path
       </code>
      </em>
      )
     </code>
    </a>
    . See
    <a class="xref" href="json-search-functions.html" title="14.17.3 Functions That Search JSON Values">
     Section 14.17.3, “Functions That Search JSON Values”
    </a>
    , for more information.
        See also
    <a class="xref" href="create-table-secondary-indexes.html#json-column-indirect-index" title="Indexing a Generated Column to Provide a JSON Column Index">
     Indexing a Generated Column to Provide a JSON Column Index
    </a>
    .
   </p>
   <p>
    Some functions take an existing JSON document, modify it in some
        way, and return the resulting modified document. Path
        expressions indicate where in the document to make changes. For
        example, the
    <a class="link" href="json-modification-functions.html#function_json-set">
     <code class="literal">
      JSON_SET()
     </code>
    </a>
    ,
    <a class="link" href="json-modification-functions.html#function_json-insert">
     <code class="literal">
      JSON_INSERT()
     </code>
    </a>
    , and
    <a class="link" href="json-modification-functions.html#function_json-replace">
     <code class="literal">
      JSON_REPLACE()
     </code>
    </a>
    functions each
        take a JSON document, plus one or more path-value pairs that
        describe where to modify the document and the values to use. The
        functions differ in how they handle existing and nonexisting
        values within the document.
   </p>
   <p>
    Consider this document:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa47099315"><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">SET</span> <span class="token variable">@j</span> <span class="token operator">=</span> <span class="token string">'["a", {"b": [true, false]}, [10, 20]]'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    <a class="link" href="json-modification-functions.html#function_json-set">
     <code class="literal">
      JSON_SET()
     </code>
    </a>
    replaces values for
        paths that exist and adds values for paths that do not exist:.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa19157409"><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 function">JSON_SET</span><span class="token punctuation">(</span><span class="token variable">@j</span><span class="token punctuation">,</span> <span class="token string">'$[1].b[0]'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'$[2][2]'</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 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> JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) <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> ["a", {"b": [1, false]}, [10, 20, 2]]      <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>
    In this case, the path
    <code class="literal">
     $[1].b[0]
    </code>
    selects an
        existing value (
    <code class="literal">
     true
    </code>
    ), which is replaced with
        the value following the path argument (
    <code class="literal">
     1
    </code>
    ).
        The path
    <code class="literal">
     $[2][2]
    </code>
    does not exist, so the
        corresponding value (
    <code class="literal">
     2
    </code>
    ) is added to the value
        selected by
    <code class="literal">
     $[2]
    </code>
    .
   </p>
   <p>
    <a class="link" href="json-modification-functions.html#function_json-insert">
     <code class="literal">
      JSON_INSERT()
     </code>
    </a>
    adds new values but
        does not replace existing values:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa53410349"><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 function">JSON_INSERT</span><span class="token punctuation">(</span><span class="token variable">@j</span><span class="token punctuation">,</span> <span class="token string">'$[1].b[0]'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'$[2][2]'</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 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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) <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 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", {"b": [true, false]}, [10, 20, 2]]      <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
   <p>
    <a class="link" href="json-modification-functions.html#function_json-replace">
     <code class="literal">
      JSON_REPLACE()
     </code>
    </a>
    replaces existing
        values and ignores new values:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17668207"><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 function">JSON_REPLACE</span><span class="token punctuation">(</span><span class="token variable">@j</span><span class="token punctuation">,</span> <span class="token string">'$[1].b[0]'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'$[2][2]'</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 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 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> JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) <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 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", {"b": [1, false]}, [10, 20]]             <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 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 path-value pairs are evaluated left to right. The document
        produced by evaluating one pair becomes the new value against
        which the next pair is evaluated.
   </p>
   <p>
    <code class="literal">
     JSON_REMOVE()
    </code>
    takes a JSON document and one
        or more paths that specify values to be removed from the
        document. The return value is the original document minus the
        values selected by paths that exist within the document:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa32757688"><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 function">JSON_REMOVE</span><span class="token punctuation">(</span><span class="token variable">@j</span><span class="token punctuation">,</span> <span class="token string">'$[2]'</span><span class="token punctuation">,</span> <span class="token string">'$[1].b[1]'</span><span class="token punctuation">,</span> <span class="token string">'$[1].b[1]'</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="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", {"b": [true]}]                              <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 paths have these effects:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        $[2]
       </code>
       matches
       <code class="literal">
        [10, 20]
       </code>
       and removes it.
      </p>
     </li>
     <li class="listitem">
      <p>
       The first instance of
       <code class="literal">
        $[1].b[1]
       </code>
       matches
       <code class="literal">
        false
       </code>
       in the
       <code class="literal">
        b
       </code>
       element
            and removes it.
      </p>
     </li>
     <li class="listitem">
      <p>
       The second instance of
       <code class="literal">
        $[1].b[1]
       </code>
       matches
            nothing: That element has already been removed, the path no
            longer exists, and has no effect.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="json-path-syntax">
       </a>
       JSON Path Syntax
      </h3>
     </div>
    </div>
   </div>
   <p>
    Many of the JSON functions supported by MySQL and described
        elsewhere in this Manual (see
    <a class="xref" href="json-functions.html" title="14.17 JSON Functions">
     Section 14.17, “JSON Functions”
    </a>
    )
        require a path expression in order to identify a specific
        element in a JSON document. A path consists of the path's
        scope followed by one or more path legs. For paths used in MySQL
        JSON functions, the scope is always the document being searched
        or otherwise operated on, represented by a leading
    <code class="literal">
     $
    </code>
    character. Path legs are separated by
        period characters (
    <code class="literal">
     .
    </code>
    ). Cells in arrays are
        represented by
    <code class="literal">
     [
     <em class="replaceable">
      <code>
       N
      </code>
     </em>
     ]
    </code>
    , where
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    is a non-negative integer. Names of
        keys must be double-quoted strings or valid ECMAScript
        identifiers (see
    <a class="ulink" href="http://www.ecma-international.org/ecma-262/5.1/#sec-7.6" target="_blank">
     <em class="citetitle">
      Identifier
        Names and Identifiers
     </em>
    </a>
    , in the
    <em class="citetitle">
     ECMAScript Language Specification
    </em>
    ). Path
        expressions, like JSON text, should be encoded using the
    <code class="literal">
     ascii
    </code>
    ,
    <code class="literal">
     utf8mb3
    </code>
    , or
    <code class="literal">
     utf8mb4
    </code>
    character set. Other character
        encodings are implicitly coerced to
    <code class="literal">
     utf8mb4
    </code>
    .
        The complete syntax is shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-clike"><div class="docs-select-all right" id="sa97235376"><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"><em class="replaceable">pathExpression</em><span class="token punctuation">:</span>
    <em class="replaceable">scope</em><span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">pathLeg</em><span class="token punctuation">)</span><span class="token operator">*</span><span class="token punctuation">]</span>

<em class="replaceable">pathLeg</em><span class="token punctuation">:</span>
    <em class="replaceable">member</em> <span class="token operator">|</span> <em class="replaceable">arrayLocation</em> <span class="token operator">|</span> <em class="replaceable">doubleAsterisk</em>

<em class="replaceable">member</em><span class="token punctuation">:</span>
    <em class="replaceable">period</em> <span class="token punctuation">(</span> <em class="replaceable">keyName</em> <span class="token operator">|</span> <em class="replaceable">asterisk</em> <span class="token punctuation">)</span>

<em class="replaceable">arrayLocation</em><span class="token punctuation">:</span>
    <em class="replaceable">leftBracket</em> <span class="token punctuation">(</span> <em class="replaceable">nonNegativeInteger</em> <span class="token operator">|</span> <em class="replaceable">asterisk</em> <span class="token punctuation">)</span> <em class="replaceable">rightBracket</em>

<em class="replaceable">keyName</em><span class="token punctuation">:</span>
    <em class="replaceable">ESIdentifier</em> <span class="token operator">|</span> <em class="replaceable">doubleQuotedString</em>

<em class="replaceable">doubleAsterisk</em><span class="token punctuation">:</span>
    <span class="token string">'**'</span>

<em class="replaceable">period</em><span class="token punctuation">:</span>
    <span class="token string">'.'</span>

<em class="replaceable">asterisk</em><span class="token punctuation">:</span>
    <span class="token string">'*'</span>

<em class="replaceable">leftBracket</em><span class="token punctuation">:</span>
    <span class="token string">'['</span>

<em class="replaceable">rightBracket</em><span class="token punctuation">:</span>
    <span class="token string">']'</span></code></pre>
   </div>
   <p>
    As noted previously, in MySQL, the scope of the path is always
        the document being operated on, represented as
    <code class="literal">
     $
    </code>
    . You can use
    <code class="literal">
     '$'
    </code>
    as a
        synonym for the document in JSON path expressions.
   </p>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     Some implementations support column references for scopes of
          JSON paths; MySQL 8.4 does not support these.
    </p>
   </div>
   <p>
    The wildcard
    <code class="literal">
     *
    </code>
    and
    <code class="literal">
     **
    </code>
    tokens are used as follows:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        .*
       </code>
       represents the values of all members
            in the object.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        [*]
       </code>
       represents the values of all cells in
            the array.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        [
        <em class="replaceable">
         <code>
          prefix
         </code>
        </em>
        ]**
        <em class="replaceable">
         <code>
          suffix
         </code>
        </em>
       </code>
       represents all paths beginning with
       <em class="replaceable">
        <code>
         prefix
        </code>
       </em>
       and ending with
       <em class="replaceable">
        <code>
         suffix
        </code>
       </em>
       .
       <em class="replaceable">
        <code>
         prefix
        </code>
       </em>
       is optional, while
       <em class="replaceable">
        <code>
         suffix
        </code>
       </em>
       is required; in other
            words, a path may not end in
       <code class="literal">
        **
       </code>
       .
      </p>
      <p>
       In addition, a path may not contain the sequence
       <code class="literal">
        ***
       </code>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    For path syntax examples, see the descriptions of the various
        JSON functions that take paths as arguments, such as
    <a class="link" href="json-search-functions.html#function_json-contains-path">
     <code class="literal">
      JSON_CONTAINS_PATH()
     </code>
    </a>
    ,
    <a class="link" href="json-modification-functions.html#function_json-set">
     <code class="literal">
      JSON_SET()
     </code>
    </a>
    , and
    <a class="link" href="json-modification-functions.html#function_json-replace">
     <code class="literal">
      JSON_REPLACE()
     </code>
    </a>
    . For examples
        which include the use of the
    <code class="literal">
     *
    </code>
    and
    <code class="literal">
     **
    </code>
    wildcards, see the description of the
    <a class="link" href="json-search-functions.html#function_json-search">
     <code class="literal">
      JSON_SEARCH()
     </code>
    </a>
    function.
   </p>
   <p>
    MySQL also supports range notation for subsets of JSON arrays
        using the
    <code class="literal">
     to
    </code>
    keyword (such as
    <code class="literal">
     $[2 to
        10]
    </code>
    ), as well as the
    <code class="literal">
     last
    </code>
    keyword
        as a synonym for the rightmost element of an array. See
    <a class="xref" href="json.html#json-paths" title="Searching and Modifying JSON Values">
     Searching and Modifying JSON Values
    </a>
    , for more information and examples.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="json-comparison">
       </a>
       Comparison and Ordering of JSON Values
      </h3>
     </div>
    </div>
   </div>
   <p>
    JSON values can be compared using the
    <a class="link" href="comparison-operators.html#operator_equal">
     <code class="literal">
      =
     </code>
    </a>
    ,
    <a class="link" href="comparison-operators.html#operator_less-than">
     <code class="literal">
      &lt;
     </code>
    </a>
    ,
    <a class="link" href="comparison-operators.html#operator_less-than-or-equal">
     <code class="literal">
      &lt;=
     </code>
    </a>
    ,
    <a class="link" href="comparison-operators.html#operator_greater-than">
     <code class="literal">
      &gt;
     </code>
    </a>
    ,
    <a class="link" href="comparison-operators.html#operator_greater-than-or-equal">
     <code class="literal">
      &gt;=
     </code>
    </a>
    ,
    <a class="link" href="comparison-operators.html#operator_not-equal">
     <code class="literal">
      &lt;&gt;
     </code>
    </a>
    ,
    <a class="link" href="comparison-operators.html#operator_not-equal">
     <code class="literal">
      !=
     </code>
    </a>
    , and
    <a class="link" href="comparison-operators.html#operator_equal-to">
     <code class="literal">
      &lt;=&gt;
     </code>
    </a>
    operators.
   </p>
   <p>
    The following comparison operators and functions are not yet
        supported with JSON values:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="link" href="comparison-operators.html#operator_between">
        <code class="literal">
         BETWEEN
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="comparison-operators.html#operator_in">
        <code class="literal">
         IN()
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="comparison-operators.html#function_greatest">
        <code class="literal">
         GREATEST()
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="comparison-operators.html#function_least">
        <code class="literal">
         LEAST()
        </code>
       </a>
      </p>
     </li>
    </ul>
   </div>
   <p>
    A workaround for the comparison operators and functions just
        listed is to cast JSON values to a native MySQL numeric or
        string data type so they have a consistent non-JSON scalar type.
   </p>
   <p>
    Comparison of JSON values takes place at two levels. The first
        level of comparison is based on the JSON types of the compared
        values. If the types differ, the comparison result is determined
        solely by which type has higher precedence. If the two values
        have the same JSON type, a second level of comparison occurs
        using type-specific rules.
   </p>
   <p>
    The following list shows the precedences of JSON types, from
        highest precedence to the lowest. (The type names are those
        returned by the
    <a class="link" href="json-attribute-functions.html#function_json-type">
     <code class="literal">
      JSON_TYPE()
     </code>
    </a>
    function.) Types shown together on a line have the same
        precedence. Any value having a JSON type listed earlier in the
        list compares greater than any value having a JSON type listed
        later in the list.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa61545935"><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-simple">BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER<span class="token punctuation">,</span> DOUBLE
NULL</code></pre>
   </div>
   <p>
    For JSON values of the same precedence, the comparison rules are
        type specific:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        BLOB
       </code>
      </p>
      <p>
       The first
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       bytes of the two
            values are compared, where
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       is
            the number of bytes in the shorter value. If the first
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       bytes of the two values are
            identical, the shorter value is ordered before the longer
            value.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        BIT
       </code>
      </p>
      <p>
       Same rules as for
       <code class="literal">
        BLOB
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        OPAQUE
       </code>
      </p>
      <p>
       Same rules as for
       <code class="literal">
        BLOB
       </code>
       .
       <code class="literal">
        OPAQUE
       </code>
       values are values that are not
            classified as one of the other types.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        DATETIME
       </code>
      </p>
      <p>
       A value that represents an earlier point in time is ordered
            before a value that represents a later point in time. If two
            values originally come from the MySQL
       <code class="literal">
        DATETIME
       </code>
       and
       <code class="literal">
        TIMESTAMP
       </code>
       types, respectively, they are equal if they represent the
            same point in time.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        TIME
       </code>
      </p>
      <p>
       The smaller of two time values is ordered before the larger
            one.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        DATE
       </code>
      </p>
      <p>
       The earlier date is ordered before the more recent date.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        ARRAY
       </code>
      </p>
      <p>
       Two JSON arrays are equal if they have the same length and
            values in corresponding positions in the arrays are equal.
      </p>
      <p>
       If the arrays are not equal, their order is determined by
            the elements in the first position where there is a
            difference. The array with the smaller value in that
            position is ordered first. If all values of the shorter
            array are equal to the corresponding values in the longer
            array, the shorter array is ordered first.
      </p>
      <p>
       Example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-simple"><div class="docs-select-all right" id="sa31843701"><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-simple"><span class="token punctuation">[</span><span class="token punctuation">]</span> <span class="token operator">&lt;</span> <span class="token punctuation">[</span>"a"<span class="token punctuation">]</span> <span class="token operator">&lt;</span> <span class="token punctuation">[</span>"ab"<span class="token punctuation">]</span> <span class="token operator">&lt;</span> <span class="token punctuation">[</span>"ab"<span class="token punctuation">,</span> "cd"<span class="token punctuation">,</span> "ef"<span class="token punctuation">]</span> <span class="token operator">&lt;</span> <span class="token punctuation">[</span>"ab"<span class="token punctuation">,</span> "ef"<span class="token punctuation">]</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        BOOLEAN
       </code>
      </p>
      <p>
       The JSON false literal is less than the JSON true literal.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        OBJECT
       </code>
      </p>
      <p>
       Two JSON objects are equal if they have the same set of
            keys, and each key has the same value in both objects.
      </p>
      <p>
       Example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-simple"><div class="docs-select-all right" id="sa36024542"><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-simple"><span class="token punctuation">{</span>"a"<span class="token operator">:</span> 1<span class="token punctuation">,</span> "b"<span class="token operator">:</span> 2<span class="token punctuation">}</span> <span class="token operator">=</span> <span class="token punctuation">{</span>"b"<span class="token operator">:</span> 2<span class="token punctuation">,</span> "a"<span class="token operator">:</span> 1<span class="token punctuation">}</span></code></pre>
      </div>
      <p>
       The order of two objects that are not equal is unspecified
            but deterministic.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        STRING
       </code>
      </p>
      <p>
       Strings are ordered lexically on the first
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       bytes of the
       <code class="literal">
        utf8mb4
       </code>
       representation of the two strings
            being compared, where
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       is the
            length of the shorter string. If the first
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       bytes of the two strings are
            identical, the shorter string is considered smaller than the
            longer string.
      </p>
      <p>
       Example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-simple"><div class="docs-select-all right" id="sa24660352"><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-simple">"a" <span class="token operator">&lt;</span> "ab" <span class="token operator">&lt;</span> "b" <span class="token operator">&lt;</span> "bc"</code></pre>
      </div>
      <p>
       This ordering is equivalent to the ordering of SQL strings
            with collation
       <code class="literal">
        utf8mb4_bin
       </code>
       . Because
       <code class="literal">
        utf8mb4_bin
       </code>
       is a binary collation,
            comparison of JSON values is case-sensitive:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-simple"><div class="docs-select-all right" id="sa22557406"><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-simple">"A" <span class="token operator">&lt;</span> "a"</code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        INTEGER
       </code>
       ,
       <code class="literal">
        DOUBLE
       </code>
      </p>
      <p>
       JSON values can contain exact-value numbers and
            approximate-value numbers. For a general discussion of these
            types of numbers, see
       <a class="xref" href="number-literals.html" title="11.1.2 Numeric Literals">
        Section 11.1.2, “Numeric Literals”
       </a>
       .
      </p>
      <p>
       The rules for comparing native MySQL numeric types are
            discussed in
       <a class="xref" href="type-conversion.html" title="14.3 Type Conversion in Expression Evaluation">
        Section 14.3, “Type Conversion in Expression Evaluation”
       </a>
       , but the
            rules for comparing numbers within JSON values differ
            somewhat:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          In a comparison between two columns that use the native
                MySQL
          <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>
          and
          <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
           <code class="literal">
            DOUBLE
           </code>
          </a>
          numeric types,
                respectively, it is known that all comparisons involve
                an integer and a double, so the integer is converted to
                double for all rows. That is, exact-value numbers are
                converted to approximate-value numbers.
         </p>
        </li>
        <li class="listitem">
         <p>
          On the other hand, if the query compares two JSON
                columns containing numbers, it cannot be known in
                advance whether numbers are integer or double. To
                provide the most consistent behavior across all rows,
                MySQL converts approximate-value numbers to exact-value
                numbers. The resulting ordering is consistent and does
                not lose precision for the exact-value numbers. For
                example, given the scalars 9223372036854775805,
                9223372036854775806, 9223372036854775807 and
                9.223372036854776e18, the order is such as this:
         </p>
         <div class="copytoclipboard-wrapper">
          <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa65680751"><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 number">9223372036854775805</span> <span class="token operator">&lt;</span> <span class="token number">9223372036854775806</span> <span class="token operator">&lt;</span> <span class="token number">9223372036854775807</span>
<span class="token operator">&lt;</span> <span class="token number">9.223372036854776e18</span> <span class="token operator">=</span> <span class="token number">9223372036854776000</span> <span class="token operator">&lt;</span> <span class="token number">9223372036854776001</span></code></pre>
         </div>
        </li>
       </ul>
      </div>
      <p>
       Were JSON comparisons to use the non-JSON numeric comparison
            rules, inconsistent ordering could occur. The usual MySQL
            comparison rules for numbers yield these orderings:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          Integer comparison:
         </p>
         <div class="copytoclipboard-wrapper">
          <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa14710781"><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 number">9223372036854775805</span> <span class="token operator">&lt;</span> <span class="token number">9223372036854775806</span> <span class="token operator">&lt;</span> <span class="token number">9223372036854775807</span></code></pre>
         </div>
         <p>
          (not defined for 9.223372036854776e18)
         </p>
        </li>
        <li class="listitem">
         <p>
          Double comparison:
         </p>
         <div class="copytoclipboard-wrapper">
          <pre class="programlisting one-line language-simple"><div class="docs-select-all right" id="sa36601697"><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-simple">9223372036854775805 <span class="token operator">=</span> 9223372036854775806 <span class="token operator">=</span> 9223372036854775807 <span class="token operator">=</span> 9<span class="token punctuation">.</span>223372036854776e18</code></pre>
         </div>
        </li>
       </ul>
      </div>
     </li>
    </ul>
   </div>
   <p>
    For comparison of any JSON value to SQL
    <code class="literal">
     NULL
    </code>
    ,
        the result is
    <code class="literal">
     UNKNOWN
    </code>
    .
   </p>
   <p>
    For comparison of JSON and non-JSON values, the non-JSON value
        is converted to JSON according to the rules in the following
        table, then the values compared as described previously.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="json-converting-between-types">
       </a>
       Converting between JSON and non-JSON values
      </h3>
     </div>
    </div>
   </div>
   <p>
    The following table provides a summary of the rules that MySQL
        follows when casting between JSON values and values of other
        types:
   </p>
   <div class="oracle-all">
    <a name="json-conversion-rules">
    </a>
    <p class="title">
     <b>
      Table 13.3 JSON Conversion Rules
     </b>
    </p>
    <div class="oracle-all-contents">
     <table summary="Conversion rules for the JSON data type">
      <colgroup>
       <col style="width: 20%"/>
       <col style="width: 40%"/>
       <col style="width: 40%"/>
      </colgroup>
      <thead>
       <tr>
        <th scope="col">
         other type
        </th>
        <th scope="col">
         CAST(other type AS JSON)
        </th>
        <th scope="col">
         CAST(JSON AS other type)
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <th scope="row">
         JSON
        </th>
        <td>
         No change
        </td>
        <td>
         No change
        </td>
       </tr>
       <tr>
        <th scope="row">
         utf8 character type (
         <code class="literal">
          utf8mb4
         </code>
         ,
         <code class="literal">
          utf8mb3
         </code>
         ,
         <code class="literal">
          ascii
         </code>
         )
        </th>
        <td>
         The string is parsed into a JSON value.
        </td>
        <td>
         The JSON value is serialized into a
         <code class="literal">
          utf8mb4
         </code>
         string.
        </td>
       </tr>
       <tr>
        <th scope="row">
         Other character types
        </th>
        <td>
         Other character encodings are implicitly converted to
         <code class="literal">
          utf8mb4
         </code>
         and treated as described for
              this character type.
        </td>
        <td>
         The JSON value is serialized into a
         <code class="literal">
          utf8mb4
         </code>
         string,
              then cast to the other character encoding. The result may
              not be meaningful.
        </td>
       </tr>
       <tr>
        <th scope="row">
         <code class="literal">
          NULL
         </code>
        </th>
        <td>
         Results in a
         <code class="literal">
          NULL
         </code>
         value of type JSON.
        </td>
        <td>
         Not applicable.
        </td>
       </tr>
       <tr>
        <th scope="row">
         Geometry types
        </th>
        <td>
         The geometry value is converted into a JSON document by calling
         <a class="link" href="spatial-geojson-functions.html#function_st-asgeojson">
          <code class="literal">
           ST_AsGeoJSON()
          </code>
         </a>
         .
        </td>
        <td>
         Illegal operation. Workaround: Pass the result of
         <a class="link" href="cast-functions.html#function_cast">
          <code class="literal">
           CAST(
           <em class="replaceable">
            <code>
             json_val
            </code>
           </em>
           AS CHAR)
          </code>
         </a>
         to
         <a class="link" href="spatial-geojson-functions.html#function_st-geomfromgeojson">
          <code class="literal">
           ST_GeomFromGeoJSON()
          </code>
         </a>
         .
        </td>
       </tr>
       <tr>
        <th scope="row">
         All other types
        </th>
        <td>
         Results in a JSON document consisting of a single scalar value.
        </td>
        <td>
         Succeeds if the JSON document consists of a single scalar value of the
              target type and that scalar value can be cast to the
              target type. Otherwise, returns
         <code class="literal">
          NULL
         </code>
         and produces a warning.
        </td>
       </tr>
      </tbody>
     </table>
    </div>
   </div>
   <br class="oracle-all-break"/>
   <p>
    <code class="literal">
     ORDER BY
    </code>
    and
    <code class="literal">
     GROUP BY
    </code>
    for
        JSON values works according to these principles:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Ordering of scalar JSON values uses the same rules as in the
            preceding discussion.
      </p>
     </li>
     <li class="listitem">
      <p>
       For ascending sorts, SQL
       <code class="literal">
        NULL
       </code>
       orders
            before all JSON values, including the JSON null literal; for
            descending sorts, SQL
       <code class="literal">
        NULL
       </code>
       orders after
            all JSON values, including the JSON null literal.
      </p>
     </li>
     <li class="listitem">
      <p>
       Sort keys for JSON values are bound by the value of the
       <a class="link" href="server-system-variables.html#sysvar_max_sort_length">
        <code class="literal">
         max_sort_length
        </code>
       </a>
       system
            variable, so keys that differ only after the first
       <a class="link" href="server-system-variables.html#sysvar_max_sort_length">
        <code class="literal">
         max_sort_length
        </code>
       </a>
       bytes
            compare as equal.
      </p>
     </li>
     <li class="listitem">
      <p>
       Sorting of nonscalar values is not currently supported and a
            warning occurs.
      </p>
     </li>
    </ul>
   </div>
   <p>
    For sorting, it can be beneficial to cast a JSON scalar to some
        other native MySQL type. For example, if a column named
    <code class="literal">
     jdoc
    </code>
    contains JSON objects having a member
        consisting of an
    <code class="literal">
     id
    </code>
    key and a nonnegative
        value, use this expression to sort by
    <code class="literal">
     id
    </code>
    values:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa25768355"><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">ORDER</span> <span class="token keyword">BY</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token function">JSON_EXTRACT</span><span class="token punctuation">(</span>jdoc<span class="token punctuation">,</span> <span class="token string">'$.id'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">UNSIGNED</span><span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    If there happens to be a generated column defined to use the
        same expression as in the
    <code class="literal">
     ORDER BY
    </code>
    , the MySQL
        optimizer recognizes that and considers using the index for the
        query execution plan. See
    <a class="xref" href="generated-column-index-optimizations.html" title="10.3.11 Optimizer Use of Generated Column Indexes">
     Section 10.3.11, “Optimizer Use of Generated Column Indexes”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="json-aggregation">
       </a>
       Aggregation of JSON Values
      </h3>
     </div>
    </div>
   </div>
   <p>
    For aggregation of JSON values, SQL
    <code class="literal">
     NULL
    </code>
    values are ignored as for other data types.
        Non-
    <code class="literal">
     NULL
    </code>
    values are converted to a numeric
        type and aggregated, except for
    <a class="link" href="aggregate-functions.html#function_min">
     <code class="literal">
      MIN()
     </code>
    </a>
    ,
    <a class="link" href="aggregate-functions.html#function_max">
     <code class="literal">
      MAX()
     </code>
    </a>
    , and
    <a class="link" href="aggregate-functions.html#function_group-concat">
     <code class="literal">
      GROUP_CONCAT()
     </code>
    </a>
    . The conversion to
        number should produce a meaningful result for JSON values that
        are numeric scalars, although (depending on the values)
        truncation and loss of precision may occur. Conversion to number
        of other JSON values may not produce a meaningful result.
   </p>
  </div>
 </div>
 <br/>
</div>
