mysql json数据表 mysql json格式

转载

IT独行侠客 2024-08-28 18:21:55

文章标签 mysql json数据表 JSON MySQL f5 文章分类 MySQL 数据库

11.6 JSON数据类型

创建JSON值
JSON值的规范化，合并和自动包装
搜索和修改JSON值
JSON路径语法
JSON值的比较和排序
在JSON和非JSON值之间转换
JSON值的聚合

从MySQL 5.7.8开始，MySQL支持RFC 7159JSON 定义的本机数据类型，可以高效访问JSON（JavaScript Object Notation）文档中的数据。与在字符串列中存储JSON格式字符串相比，数据类型具有以下优势： JSON

存储在JSON列中的JSON文档的自动验证。无效的文档会产生错误。
优化的存储格式。存储在JSON列中的JSON文档将转换为内部格式，以允许对文档元素进行快速读取访问。当服务器稍后必须读取以该二进制格式存储的JSON值时，不需要从文本表示中解析该值。二进制格式的结构使服务器能够直接通过键或数组索引查找子对象或嵌套值，而无需在文档之前或之后读取所有值。

注意

此讨论使用JSONmonotype来指定JSON数据类型和常规字体中的“ JSON ”以指示一般的JSON数据。

存储JSON文档所需的空间与LONGBLOB或大致相同LONGTEXT; 有关更多信息，请参见第11.8节“数据类型存储要求”。请务必记住，JSON列中存储的任何JSON文档的大小都限制为max_allowed_packet系统变量的值。（当服务器在内存中内部操作JSON值时，它可能大于此值;当服务器存储时，该限制适用。）

一JSON列不能有非NULL默认值。

除了JSON数据类型之外，还有一组SQL函数可用于对JSON值进行操作，例如创建，操作和搜索。以下讨论显示了这些操作的示例。有关各个函数的详细信息，请参见第12.17节“JSON函数”。

还提供了一组用于操作GeoJSON值的空间函数。请参见第12.16.11节“空间GeoJSON函数”。

JSON列，如其他二进制类型的列，不直接索引; 相反，您可以在生成的列上创建索引，该列从列中提取标量值 JSON。有关详细示例，请参阅索引生成的列以提供JSON列索引。

MySQL优化器还在与JSON表达式匹配的虚拟列上查找兼容索引。

MySQL NDB Cluster 7.5（7.5.2及更高版本）支持 JSON列和MySQL JSON函数，包括在列生成的JSON列上创建索引，作为无法索引JSON列的变通方法。JSON每个NDB表最多支持3 列。

接下来的几节提供了有关JSON值的创建和操作的基本信息。

创建JSON值

JSON数组包含一个由逗号分隔的值列表，并包含在字符[和]字符中：

<span style="color:#555555"><span style="color:black"><code class="language-json"><span style="color:#999999">[</span><span style="color:#669900">"abc"</span><span style="color:#999999">,</span> <span style="color:#990055">10</span><span style="color:#999999">,</span> null<span style="color:#999999">,</span> <span style="color:#990055">true</span><span style="color:#999999">,</span> <span style="color:#990055">false</span><span style="color:#999999">]</span></code></span></span>

JSON对象包含一组由逗号分隔的键值对，并包含在字符{和 }字符中：

<span style="color:#555555"><span style="color:black"><code class="language-json"><span style="color:#999999">{</span><span style="color:#990055">"k1"</span><span style="color:#a67f59">:</span> <span style="color:#669900">"value"</span><span style="color:#999999">,</span> <span style="color:#990055">"k2"</span><span style="color:#a67f59">:</span> <span style="color:#990055">10</span><span style="color:#999999">}</span></code></span></span>

如示例所示，JSON数组和对象可以包含字符串或数字的标量值，JSON空文字或JSON布尔值true或false文字。JSON对象中的键必须是字符串。时间（日期，时间或日期时间）标量值也是允许的：

<span style="color:#555555"><span style="color:black"><code class="language-json"><span style="color:#999999">[</span><span style="color:#669900">"12:18:29.000000"</span><span style="color:#999999">,</span> <span style="color:#669900">"2015-07-29"</span><span style="color:#999999">,</span> <span style="color:#669900">"2015-07-29 12:18:29.000000"</span><span style="color:#999999">]</span></code></span></span>

在JSON数组元素和JSON对象键值中允许嵌套：

<span style="color:#555555"><span style="color:black"><code class="language-json"><span style="color:#999999">[</span><span style="color:#990055">99</span><span style="color:#999999">,</span> <span style="color:#999999">{</span><span style="color:#990055">"id"</span><span style="color:#a67f59">:</span> <span style="color:#669900">"HK500"</span><span style="color:#999999">,</span> <span style="color:#990055">"cost"</span><span style="color:#a67f59">:</span> <span style="color:#990055">75.99</span><span style="color:#999999">}</span><span style="color:#999999">,</span> <span style="color:#999999">[</span><span style="color:#669900">"hot"</span><span style="color:#999999">,</span> <span style="color:#669900">"cold"</span><span style="color:#999999">]</span><span style="color:#999999">]</span>
<span style="color:#999999">{</span><span style="color:#990055">"k1"</span><span style="color:#a67f59">:</span> <span style="color:#669900">"value"</span><span style="color:#999999">,</span> <span style="color:#990055">"k2"</span><span style="color:#a67f59">:</span> <span style="color:#999999">[</span><span style="color:#990055">10</span><span style="color:#999999">,</span> <span style="color:#990055">20</span><span style="color:#999999">]</span><span style="color:#999999">}</span></code></span></span>

您还可以为此目的从MySQL提供的许多函数中获取JSON值（请参见第12.17.2节“创建JSON值的函数”）以及JSON使用其他类型的值转换为类型（请参阅在JSON之间转换）和非JSON值）。接下来的几段描述了MySQL如何处理作为输入提供的JSON值。 CAST(value AS JSON)

在MySQL中，JSON值被写为字符串。MySQL解析在需要JSON值的上下文中使用的任何字符串，如果它作为JSON无效则会产生错误。这些上下文包括将值插入到具有JSON数据类型的列中，并将参数传递给期望JSON值的函数（通常显示为 json_doc或 json_val在MySQL JSON函数的文档中），如以下示例所示：

JSON 如果值是有效的JSON值，则尝试将值插入列成功，但如果不是，则尝试失败：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">CREATE</span> <span style="color:#0077aa">TABLE</span> t1 <span style="color:#999999">(</span>jdoc <span style="color:#834689">JSON</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555">Query OK, 0 rows affected (0.20 sec)</span>

<span style="color:#a67f59">mysql></span> <span style="color:#0077aa">INSERT</span> <span style="color:#0077aa">INTO</span> t1 <span style="color:#0077aa">VALUES</span><span style="color:#999999">(</span><span style="color:#669900">'{"key1": "value1", "key2": "value2"}'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555">Query OK, 1 row affected (0.01 sec)</span>

<span style="color:#a67f59">mysql></span> <span style="color:#0077aa">INSERT</span> <span style="color:#0077aa">INTO</span> t1 <span style="color:#0077aa">VALUES</span><span style="color:#999999">(</span><span style="color:#669900">'[1, 2,'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555">ERROR 3140 (22032) at line 2<span style="color:#999999">:</span> Invalid JSON text<span style="color:#999999">:</span>
"Invalid value." at position 6 in value (or column) '[1, 2,'.</span></code></span></span>

为位置“ 在位置 N

该JSON_TYPE()函数需要一个JSON参数并尝试将其解析为JSON值。如果值有效，则返回值的JSON类型，否则产生错误：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_TYPE</span><span style="color:#999999">(</span><span style="color:#669900">'["a", "b", 1]'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_TYPE('["a", "b", 1]') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> ARRAY                      <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>

<span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_TYPE</span><span style="color:#999999">(</span><span style="color:#669900">'"hello"'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_TYPE('"hello"') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> STRING               <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>

<span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_TYPE</span><span style="color:#999999">(</span><span style="color:#669900">'hello'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555">ERROR 3146 (22032)<span style="color:#999999">:</span> Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.</span></code></span></span>

MySQL使用utf8mb4字符集和utf8mb4_bin排序规则处理JSON上下文中使用的字符串。其他字符集中的字符串将utf8mb4根据需要进行转换。（对于在串ascii或 utf8字符集，则不需要转换，因为ascii和utf8都是的子集utf8mb4。）

作为使用文字字符串编写JSON值的替代方法，存在用于从组件元素组成JSON值的函数。JSON_ARRAY()获取（可能为空）值列表并返回包含这些值的JSON数组：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_ARRAY</span><span style="color:#999999">(</span><span style="color:#669900">'a'</span><span style="color:#999999">,</span> <span style="color:#990055">1</span><span style="color:#999999">,</span> <span style="color:#dd4a68">NOW</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_ARRAY('a', 1, NOW())              <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> ["a", 1, "2015<span style="color:#999999">-</span>07<span style="color:#999999">-</span>27 09:43:47.000000"] <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

JSON_OBJECT() 获取（可能为空）键值对列表并返回包含这些对的JSON对象：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_OBJECT</span><span style="color:#999999">(</span><span style="color:#669900">'key1'</span><span style="color:#999999">,</span> <span style="color:#990055">1</span><span style="color:#999999">,</span> <span style="color:#669900">'key2'</span><span style="color:#999999">,</span> <span style="color:#669900">'abc'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_OBJECT('key1', 1, 'key2', 'abc') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> {"key1": 1, "key2": "abc"}            <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

JSON_MERGE() 获取两个或多个JSON文档并返回组合结果：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_MERGE</span><span style="color:#999999">(</span><span style="color:#669900">'["a", 1]'</span><span style="color:#999999">,</span> <span style="color:#669900">'{"key": "value"}'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_MERGE('["a", 1]', '{"key": "value"}') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> ["a", 1, {"key": "value"}]                 <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

有关合并规则的信息，请参阅 JSON值的规范化，合并和自动包装。

可以将JSON值分配给用户定义的变量：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SET</span> <span style="color:#ee9900">@j</span> <span style="color:#a67f59">=</span> <span style="color:#dd4a68">JSON_OBJECT</span><span style="color:#999999">(</span><span style="color:#669900">'key'</span><span style="color:#999999">,</span> <span style="color:#669900">'value'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#ee9900">@j</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> @j               <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> {"key": "value"} <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

但是，用户定义的变量不能是 JSON数据类型，所以虽然 @j在前面的例子中看起来像一个JSON值并且具有相同的字符集和归类为JSON值，但它不具有 JSON数据类型。相反，结果from JSON_OBJECT()在分配给变量时会转换为字符串。

通过转换JSON值生成的字符串具有以下字符集utf8mb4和排序规则 utf8mb4_bin：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">CHARSET</span><span style="color:#999999">(</span><span style="color:#ee9900">@j</span><span style="color:#999999">)</span><span style="color:#999999">,</span> <span style="color:#dd4a68">COLLATION</span><span style="color:#999999">(</span><span style="color:#ee9900">@j</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> CHARSET(@j) <span style="color:#999999">|</span> COLLATION(@j) <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> utf8mb4     <span style="color:#999999">|</span> utf8mb4_bin   <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

因为utf8mb4_bin是二进制排序规则，所以JSON值的比较区分大小写。

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_ARRAY</span><span style="color:#999999">(</span><span style="color:#669900">'x'</span><span style="color:#999999">)</span> <span style="color:#a67f59">=</span> <span style="color:#dd4a68">JSON_ARRAY</span><span style="color:#999999">(</span><span style="color:#669900">'X'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_ARRAY('x') = JSON_ARRAY('X') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span>                                 0 <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

区分大小写也适用于JSON null，true和 false文字，它们必须始终以小写形式编写：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_VALID</span><span style="color:#999999">(</span><span style="color:#669900">'null'</span><span style="color:#999999">)</span><span style="color:#999999">,</span> <span style="color:#dd4a68">JSON_VALID</span><span style="color:#999999">(</span><span style="color:#669900">'Null'</span><span style="color:#999999">)</span><span style="color:#999999">,</span> <span style="color:#dd4a68">JSON_VALID</span><span style="color:#999999">(</span><span style="color:#669900">'NULL'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_VALID('null') <span style="color:#999999">|</span> JSON_VALID('Null') <span style="color:#999999">|</span> JSON_VALID('NULL') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span>                  1 <span style="color:#999999">|</span>                  0 <span style="color:#999999">|</span>                  0 <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>

<span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">CAST</span><span style="color:#999999">(</span><span style="color:#669900">'null'</span> <span style="color:#0077aa">AS</span> <span style="color:#834689">JSON</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> CAST('null' AS JSON) <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> null                 <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555">1 row in set (0.00 sec)</span>

<span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">CAST</span><span style="color:#999999">(</span><span style="color:#669900">'NULL'</span> <span style="color:#0077aa">AS</span> <span style="color:#834689">JSON</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555">ERROR 3141 (22032)<span style="color:#999999">:</span> Invalid JSON text in argument 1 to function cast_as_json<span style="color:#999999">:</span>
"Invalid value." at position 0 in 'NULL'.</span></code></span></span>

的字面JSON的情况下，灵敏度不同于所述SQL的NULL，TRUE和 FALSE文字，它可以在任何大小写被写成：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">ISNULL</span><span style="color:#999999">(</span><span style="color:#990055">null</span><span style="color:#999999">)</span><span style="color:#999999">,</span> <span style="color:#dd4a68">ISNULL</span><span style="color:#999999">(</span><span style="color:#990055">Null</span><span style="color:#999999">)</span><span style="color:#999999">,</span> <span style="color:#dd4a68">ISNULL</span><span style="color:#999999">(</span><span style="color:#990055">NULL</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> ISNULL(null) <span style="color:#999999">|</span> ISNULL(Null) <span style="color:#999999">|</span> ISNULL(NULL) <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span>            1 <span style="color:#999999">|</span>            1 <span style="color:#999999">|</span>            1 <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

有时可能需要或希望将引号字符（"或'）插入JSON文档中。假设您希望在此示例中插入一些JSON对象，这些对象包含表示有关MySQL的一些事实的句子的句子，每个句子都与适当的关键字配对，使用此处显示的SQL语句创建的表：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">CREATE</span> <span style="color:#0077aa">TABLE</span> facts <span style="color:#999999">(</span>sentence <span style="color:#834689">JSON</span><span style="color:#999999">)</span><span style="color:#999999">;</span></code></span></span>

这些关键词 - 句子对中有这一个：

<span style="color:#555555"><span style="color:black"><code class="language-simple">mascot<span style="color:#a67f59">:</span> The MySQL mascot is a dolphin named "Sakila"<span style="color:#999999">.</span></code></span></span>

将此作为JSON对象插入facts表中的一种方法是使用MySQL JSON_OBJECT()函数。在这种情况下，您必须使用反斜杠转义每个引号字符，如下所示：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">INSERT</span> <span style="color:#0077aa">INTO</span> facts <span style="color:#0077aa">VALUES</span>
     <span style="color:#a67f59">></span>   <span style="color:#999999">(</span><span style="color:#dd4a68">JSON_OBJECT</span><span style="color:#999999">(</span><span style="color:#669900">"mascot"</span><span style="color:#999999">,</span> <span style="color:#669900">"Our mascot is a dolphin named \"Sakila\"."</span><span style="color:#999999">)</span><span style="color:#999999">)</span><span style="color:#999999">;</span></code></span></span>

如果将值作为JSON对象文字插入，则此方法不起作用，在这种情况下，必须使用双反斜杠转义序列，如下所示：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">INSERT</span> <span style="color:#0077aa">INTO</span> facts <span style="color:#0077aa">VALUES</span>
     <span style="color:#a67f59">></span>   <span style="color:#999999">(</span><span style="color:#669900">'{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}'</span><span style="color:#999999">)</span><span style="color:#999999">;</span></code></span></span>

使用双反斜杠可防止MySQL执行转义序列处理，而是使其将字符串文字传递给存储引擎进行处理。以刚才显示的方式插入JSON对象后，您可以通过执行简单操作看到反斜杠存在于JSON列值中SELECT，如下所示：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> sentence <span style="color:#0077aa">FROM</span> facts<span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> sentence                                                <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> {"mascot": "Our mascot is a dolphin named \"Sakila\"."} <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

要使用mascot键作为查找此特定句子，可以使用column-path运算符 ->，如下所示：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> col<span style="color:#a67f59">-</span><span style="color:#a67f59">></span><span style="color:#669900">"$.mascot"</span> <span style="color:#0077aa">FROM</span> qtest<span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> col<span style="color:#999999">-</span>>"$.mascot"                             <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> "Our mascot is a dolphin named \"Sakila\"." <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555">1 row in set (0.00 sec)</span></code></span></span>

这样就可以保留反斜杠以及周围的引号。要使用mascot键作为键显示所需的值，但不包括周围的引号或任何转义，请使用内联路径运算符 ->>，如下所示：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> sentence<span style="color:#a67f59">-</span><span style="color:#a67f59">>></span><span style="color:#669900">"$.mascot"</span> <span style="color:#0077aa">FROM</span> facts<span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> sentence<span style="color:#999999">-</span>>>"$.mascot"                   <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> Our mascot is a dolphin named "Sakila". <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

注意

如果NO_BACKSLASH_ESCAPES启用了服务器SQL模式，则前面的示例无法正常工作。如果设置了此模式，则可以使用单个反斜杠而不是双反斜杠来插入JSON对象文字，并保留反斜杠。如果JSON_OBJECT()在执行插入时使用该功能并且设置了此模式，则必须替换单引号和双引号，如下所示：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">INSERT</span> <span style="color:#0077aa">INTO</span> facts <span style="color:#0077aa">VALUES</span>
     <span style="color:#a67f59">></span> <span style="color:#999999">(</span><span style="color:#dd4a68">JSON_OBJECT</span><span style="color:#999999">(</span><span style="color:#669900">'mascot'</span><span style="color:#999999">,</span> <span style="color:#669900">'Our mascot is a dolphin named "Sakila".'</span><span style="color:#999999">)</span><span style="color:#999999">)</span><span style="color:#999999">;</span></code></span></span>

有关JSON_UNQUOTE()此模式对JSON值中的转义字符的影响的详细信息，请参阅该函数的说明。

JSON值的规范化，合并和自动包装

当解析一个字符串并发现它是一个有效的JSON文档时，它也会被标准化：具有复制在文档中较早找到的键的键的成员将被丢弃（即使值不同）。以下JSON_OBJECT()调用生成的对象值不包括第二个key1元素，因为该键名称出现在值的前面：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_OBJECT</span><span style="color:#999999">(</span><span style="color:#669900">'key1'</span><span style="color:#999999">,</span> <span style="color:#990055">1</span><span style="color:#999999">,</span> <span style="color:#669900">'key2'</span><span style="color:#999999">,</span> <span style="color:#669900">'abc'</span><span style="color:#999999">,</span> <span style="color:#669900">'key1'</span><span style="color:#999999">,</span> <span style="color:#669900">'def'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> {"key1": 1, "key2": "abc"}                           <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

注意

重复密钥的这种“ 第一关键胜利 ”处理与RFC 7159不一致。这是MySQL 5.7中的已知问题，已在MySQL 8.0中修复。（Bug＃86866，Bug＃26369555）

MySQL还会丢弃原始JSON文档中的键，值或元素之间的额外空格。为了使查找更有效，它还对JSON对象的键进行排序。 您应该知道此排序的结果可能会发生变化，并且不保证在各个版本中保持一致。

生成JSON值的MySQL函数（请参见第12.17.2节“创建JSON值的函数”）始终返回规范化值。

合并JSON值

在组合多个数组的上下文中，通过将稍后命名的数组连接到第一个数组的末尾，将数组合并为单个数组。在以下示例中， JSON_MERGE()将其参数合并为单个数组：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_MERGE</span><span style="color:#999999">(</span><span style="color:#669900">'[1, 2]'</span><span style="color:#999999">,</span> <span style="color:#669900">'["a", "b"]'</span><span style="color:#999999">,</span> <span style="color:#669900">'[true, false]'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> [1, 2, "a", "b", true, false]                       <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

将值插入JSON列时也会执行规范化，如下所示：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">CREATE</span> <span style="color:#0077aa">TABLE</span> t1 <span style="color:#999999">(</span>c1 <span style="color:#834689">JSON</span><span style="color:#999999">)</span><span style="color:#999999">;</span>

<span style="color:#a67f59">mysql></span> <span style="color:#0077aa">INSERT</span> <span style="color:#0077aa">INTO</span> t1 <span style="color:#0077aa">VALUES</span>
     <span style="color:#a67f59">></span>     <span style="color:#999999">(</span><span style="color:#669900">'{"x": 17, "x": "red"}'</span><span style="color:#999999">)</span><span style="color:#999999">,</span>
     <span style="color:#a67f59">></span>     <span style="color:#999999">(</span><span style="color:#669900">'{"x": 17, "x": "red", "x": [3, 5, 7]}'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>

<span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> c1 <span style="color:#0077aa">FROM</span> t1<span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> c1        <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> {"x": 17} <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> {"x": 17} <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

合并时的多个对象生成单个对象。如果多个对象具有相同的键，则生成的合并对象中该键的值是包含键值的数组：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_MERGE</span><span style="color:#999999">(</span><span style="color:#669900">'{"a": 1, "b": 2}'</span><span style="color:#999999">,</span> <span style="color:#669900">'{"c": 3, "a": 4}'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> {"a": [1, 4], "b": 2, "c": 3}                      <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

在需要数组值的上下文中使用的非阵列值是自动包装的：该值由[ 和]字符包围以将其转换为数组。在以下语句中，每个参数都自动包装为数组（[1]，[2]）。然后将它们合并以生成单个结果数组：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_MERGE</span><span style="color:#999999">(</span><span style="color:#669900">'1'</span><span style="color:#999999">,</span> <span style="color:#669900">'2'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_MERGE('1', '2') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> [1, 2]               <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

通过将对象自动包装为数组并合并两个数组来合并数组和对象值：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_MERGE</span><span style="color:#999999">(</span><span style="color:#669900">'[10, 20]'</span><span style="color:#999999">,</span> <span style="color:#669900">'{"a": "x", "b": "y"}'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> [10, 20, {"a": "x", "b": "y"}]                 <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

搜索和修改JSON值

JSON路径表达式选择JSON文档中的值。

路径表达式对于提取JSON文档的一部分或修改JSON文档的函数很有用，以指定该文档中的操作位置。例如，以下查询从JSON文档中提取具有name键的成员的值：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_EXTRACT</span><span style="color:#999999">(</span><span style="color:#669900">'{"id": 14, "name": "Aztalan"}'</span><span style="color:#999999">,</span> <span style="color:#669900">'$.name'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> "Aztalan"                                               <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

路径语法使用前导$字符来表示正在考虑的JSON文档，可选地后跟选择器，它们连续指示文档的更多特定部分：

后跟密钥名称的句点用具有给定键的对象命名成员。如果没有引号的名称在路径表达式中不合法（例如，如果它包含空格），则必须在双引号内指定键名。
[N]附加到a path，选择一个数组命名数组中位置的值N 。数组位置是从零开始的整数。如果path未选择数组值，path[0]将计算为与以下值相同的值 path：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_SET</span><span style="color:#999999">(</span><span style="color:#669900">'"x"'</span><span style="color:#999999">,</span> <span style="color:#669900">'$[0]'</span><span style="color:#999999">,</span> <span style="color:#669900">'a'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_SET('"x"', '$[0]', 'a') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> "a"                          <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555">1 row in set (0.00 sec)</span></code></span></span>

路径可以包含*或 **通配符：

.[*] 计算JSON对象中所有成员的值。
[*] 计算JSON数组中所有元素的值。
prefix**suffix 计算所有以命名前缀开头并以命名后缀结尾的路径。

文档中不存在的路径（评估为不存在的数据）的计算结果为NULL。

让我们$用三个元素来引用这个JSON数组：

<span style="color:#555555"><span style="color:black"><code class="language-json"><span style="color:#999999">[</span><span style="color:#990055">3</span><span style="color:#999999">,</span> <span style="color:#999999">{</span><span style="color:#990055">"a"</span><span style="color:#a67f59">:</span> <span style="color:#999999">[</span><span style="color:#990055">5</span><span style="color:#999999">,</span> <span style="color:#990055">6</span><span style="color:#999999">]</span><span style="color:#999999">,</span> <span style="color:#990055">"b"</span><span style="color:#a67f59">:</span> <span style="color:#990055">10</span><span style="color:#999999">}</span><span style="color:#999999">,</span> <span style="color:#999999">[</span><span style="color:#990055">99</span><span style="color:#999999">,</span> <span style="color:#990055">100</span><span style="color:#999999">]</span><span style="color:#999999">]</span></code></span></span>

然后：

$[0]评估为3。
$[1]评估为{"a": [5, 6], "b": 10}。
$[2]评估为[99, 100]。
$[3]求值为NULL （它指的是第四个不存在的数组元素）。

因为$[1]并且$[2] 评估为非标量值，所以它们可以用作选择嵌套值的更具体路径表达式的基础。例子：

$[1].a评估为[5, 6]。
$[1].a[1]评估为 6。
$[1].b评估为 10。
$[2][0]评估为 99。

如前所述，如果路径表达式中的未加引号的键名称不合法，则必须引用命名键的路径组件。让我们$参考这个值：

<span style="color:#555555"><span style="color:black"><code class="language-json"><span style="color:#999999">{</span><span style="color:#990055">"a fish"</span><span style="color:#a67f59">:</span> <span style="color:#669900">"shark"</span><span style="color:#999999">,</span> <span style="color:#990055">"a bird"</span><span style="color:#a67f59">:</span> <span style="color:#669900">"sparrow"</span><span style="color:#999999">}</span></code></span></span>

密钥都包含空格，必须引用：

$."a fish"评估为 shark。
$."a bird"评估为 sparrow。

使用通配符的路径计算为可包含多个值的数组：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_EXTRACT</span><span style="color:#999999">(</span><span style="color:#669900">'{"a": 1, "b": 2, "c": [3, 4, 5]}'</span><span style="color:#999999">,</span> <span style="color:#669900">'$.*'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> [1, 2, [3, 4, 5]]                                       <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_EXTRACT</span><span style="color:#999999">(</span><span style="color:#669900">'{"a": 1, "b": 2, "c": [3, 4, 5]}'</span><span style="color:#999999">,</span> <span style="color:#669900">'$.c[*]'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> [3, 4, 5]                                                  <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

在以下示例中，路径$**.b 计算为多个路径（$.a.b和 $.c.b）并生成匹配路径值的数组：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_EXTRACT</span><span style="color:#999999">(</span><span style="color:#669900">'{"a": {"b": 1}, "c": {"b": 2}}'</span><span style="color:#999999">,</span> <span style="color:#669900">'$**.b'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> [1, 2]                                                  <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

在MySQL 5.7.9及更高版本中，您可以使用 JSON列标识符和JSON路径表达式作为同义词。有关更多信息，请参见第12.17.3节“搜索JSON值的函数”。另请参见索引生成的列以提供JSON列索引。 column->pathJSON_EXTRACT(column, path)

某些函数采用现有的JSON文档，以某种方式对其进行修改，并返回生成的修改后的文档。路径表达式指示文档中的更改位置。例如，JSON_SET()， JSON_INSERT()，和 JSON_REPLACE()功能各采取JSON文档，再加上描述其中修改文档和使用的值的一个或多个路径/值对。这些函数在处理文档中的现有值和不存在值方面有所不同。

考虑这个文件：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SET</span> <span style="color:#ee9900">@j</span> <span style="color:#a67f59">=</span> <span style="color:#669900">'["a", {"b": [true, false]}, [10, 20]]'</span><span style="color:#999999">;</span></code></span></span>

JSON_SET() 替换存在的路径的值，并为不存在的路径添加值：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_SET</span><span style="color:#999999">(</span><span style="color:#ee9900">@j</span><span style="color:#999999">,</span> <span style="color:#669900">'$[1].b[0]'</span><span style="color:#999999">,</span> <span style="color:#990055">1</span><span style="color:#999999">,</span> <span style="color:#669900">'$[2][2]'</span><span style="color:#999999">,</span> <span style="color:#990055">2</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> ["a", {"b": [1, false]}, [10, 20, 2]]      <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

在这种情况下，路径$[1].b[0]选择一个现有值（true），该值将替换为path参数（1）后面的值。该路径$[2][2]不存在，因此相应的值（2）将添加到所选的值$[2]。

JSON_INSERT() 添加新值但不替换现有值：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_INSERT</span><span style="color:#999999">(</span><span style="color:#ee9900">@j</span><span style="color:#999999">,</span> <span style="color:#669900">'$[1].b[0]'</span><span style="color:#999999">,</span> <span style="color:#990055">1</span><span style="color:#999999">,</span> <span style="color:#669900">'$[2][2]'</span><span style="color:#999999">,</span> <span style="color:#990055">2</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> ["a", {"b": [true, false]}, [10, 20, 2]]      <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

JSON_REPLACE() 替换现有值并忽略新值：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_REPLACE</span><span style="color:#999999">(</span><span style="color:#ee9900">@j</span><span style="color:#999999">,</span> <span style="color:#669900">'$[1].b[0]'</span><span style="color:#999999">,</span> <span style="color:#990055">1</span><span style="color:#999999">,</span> <span style="color:#669900">'$[2][2]'</span><span style="color:#999999">,</span> <span style="color:#990055">2</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> ["a", {"b": [1, false]}, [10, 20]]             <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

路径/值对从左到右进行评估。通过评估一对产生的文档成为评估下一对的新值。

JSON_REMOVE()获取JSON文档和一个或多个指定要从文档中删除的值的路径。返回值是原始文档减去文档中存在的路径选择的值：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">JSON_REMOVE</span><span style="color:#999999">(</span><span style="color:#ee9900">@j</span><span style="color:#999999">,</span> <span style="color:#669900">'$[2]'</span><span style="color:#999999">,</span> <span style="color:#669900">'$[1].b[1]'</span><span style="color:#999999">,</span> <span style="color:#669900">'$[1].b[1]'</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span>
<span style="color:#555555"><span style="color:#999999">|</span> ["a", {"b": [true]}]                              <span style="color:#999999">|</span></span>
<span style="color:#555555"><span style="color:#999999">+</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">-</span><span style="color:#999999">+</span></span></code></span></span>

路径有这些影响：

$[2]匹配[10, 20] 并删除它。
的第一个实例$[1].b[1]相匹配 false的b元素并将其删除。
$[1].b[1]匹配的第二个实例没有：该元素已被删除，路径不再存在，并且无效。

JSON路径语法

MySQL支持并在本手册中其他地方描述的许多JSON函数（请参见第12.17节“JSON函数”）需要路径表达式，以便识别JSON文档中的特定元素。路径由路径范围后跟一个或多个路径支路组成。对于MySQL JSON函数中使用的路径，范围始终是正在搜索或以其他方式操作的文档，由前导$字符表示。路径腿由句点字符（.）分隔。数组中的单元格表示为，其中是非负整数。密钥的名称必须是双引号字符串或有效的ECMAScript标识符（请参阅 [N]Nhttp://www.ecma-international.org/ecma-262/5.1/#sec-7.6）。路径表达式，如JSON文本，应该使用的编码 ascii，utf8或 utf8mb4字符集。其他字符编码被隐式强制执行utf8mb4。完整语法如下所示：

<span style="color:#555555"><span style="color:black"><code class="language-clike"><em>pathExpression</em><span style="color:#999999">:</span>
    <em>scope</em><span style="color:#999999">[</span><span style="color:#999999">(</span><em>pathLeg</em><span style="color:#999999">)</span><span style="color:#a67f59">*</span><span style="color:#999999">]</span>

<em>pathLeg</em><span style="color:#999999">:</span>
    <em>member</em> <span style="color:#a67f59">|</span> <em>arrayLocation</em> <span style="color:#a67f59">|</span> <em>doubleAsterisk</em>

<em>member</em><span style="color:#999999">:</span>
    <em>period</em> <span style="color:#999999">(</span> <em>keyName</em> <span style="color:#a67f59">|</span> <em>asterisk</em> <span style="color:#999999">)</span>

<em>arrayLocation</em><span style="color:#999999">:</span>
    <em>leftBracket</em> <span style="color:#999999">(</span> <em>nonNegativeInteger</em> <span style="color:#a67f59">|</span> <em>asterisk</em> <span style="color:#999999">)</span> <em>rightBracket</em>

<em>keyName</em><span style="color:#999999">:</span>
    <em>ESIdentifier</em> <span style="color:#a67f59">|</span> <em>doubleQuotedString</em>

<em>doubleAsterisk</em><span style="color:#999999">:</span>
    <span style="color:#669900">'**'</span>

<em>period</em><span style="color:#999999">:</span>
    <span style="color:#669900">'.'</span>

<em>asterisk</em><span style="color:#999999">:</span>
    <span style="color:#669900">'*'</span>

<em>leftBracket</em><span style="color:#999999">:</span>
    <span style="color:#669900">'['</span>

<em>rightBracket</em><span style="color:#999999">:</span>
    <span style="color:#669900">']'</span></code></span></span>

如前所述，在MySQL中，路径的范围始终是正在操作的文档，表示为 $。您可以'$'在JSON路径表达式中用作文档的synonynm。

注意

某些实现支持JSON路径范围的列引用; 目前，MySQL不支持这些。

通配符*和** 令牌使用如下：

.* 表示对象中所有成员的值。
[*] 表示数组中所有单元格的值。
[prefix]**suffix 表示以...开头prefix和结尾的所有路径 suffix。 prefix是可选的，虽然 suffix是必需的; 换句话说，路径可能不会结束**。
另外，路径可能不包含序列 ***。

对于路径语法的例子，见该采取的路径作为参数，例如各种JSON功能的说明 JSON_CONTAINS_PATH()， JSON_SET()和 JSON_REPLACE()。有关使用*和 **通配符的示例，请参阅该JSON_SEARCH()函数的说明。

JSON值的比较和排序

JSON值可以使用进行比较 =， <， <=， >， >=， <>， !=，和 <=> 运营商。

JSON值尚不支持以下比较运算符和函数：

刚才列出的比较运算符和函数的解决方法是将JSON值转换为本机MySQL数值或字符串数据类型，以便它们具有一致的非JSON标量类型。

JSON值的比较发生在两个级别。第一级比较基于比较值的JSON类型。如果类型不同，则比较结果仅由哪种类型具有更高优先级来确定。如果这两个值具有相同的JSON类型，则使用特定于类型的规则进行第二级比较。

以下列表显示了JSON类型的优先级，从最高优先级到最低优先级。（类型名称是JSON_TYPE() 函数返回的类型名称。）一行显示的类型具有相同的优先级。列表中前面列出的任何具有JSON类型的值都比列表中稍后列出的具有JSON类型的任何值都要大。

<span style="color:#555555"><span style="color:black"><code class="language-simple">BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER<span style="color:#999999">,</span> DOUBLE
NULL</code></span></span>

对于具有相同优先级的JSON值，比较规则是特定于类型的：

BLOBN比较两个值的第一个字节，其中N是较短值中的字节数。如果N两个值的第一个字节相同，则在较长值之前排序较短的值。
BIT同样的规则BLOB。
OPAQUE同样的规则BLOB。 OPAQUE值是未归类为其他类型之一的值。
DATETIME表示较早时间点的值在表示稍后时间点的值之前排序。如果两个值最初分别来自MySQL DATETIME和TIMESTAMP类型，则它们相等，如果它们代表相同的时间点。
TIME两个时间值中较小的一个在较大的值之前排序。
DATE较早的日期是在最近的日期之前订购的。
ARRAY如果两个JSON数组具有相同的长度并且数组中相应位置的值相等，则它们是相等的。
如果数组不相等，则它们的顺序由第一个位置中存在差异的元素确定。首先排序在该位置具有较小值的数组。如果较短数组的所有值都等于较长数组中的相应值，则首先排序较短的数组。
例：

<span style="color:#555555"><span style="color:black"><code class="language-simple"><span style="color:#999999">[</span><span style="color:#999999">]</span> <span style="color:#a67f59"><</span> <span style="color:#999999">[</span>"a"<span style="color:#999999">]</span> <span style="color:#a67f59"><</span> <span style="color:#999999">[</span>"ab"<span style="color:#999999">]</span> <span style="color:#a67f59"><</span> <span style="color:#999999">[</span>"ab"<span style="color:#999999">,</span> "cd"<span style="color:#999999">,</span> "ef"<span style="color:#999999">]</span> <span style="color:#a67f59"><</span> <span style="color:#999999">[</span>"ab"<span style="color:#999999">,</span> "ef"<span style="color:#999999">]</span></code></span></span>

BOOLEANJSON false literal小于JSON true literal。
OBJECT如果两个JSON对象具有相同的键集，则它们是相等的，并且每个键在两个对象中具有相同的值。
例：

<span style="color:#555555"><span style="color:black"><code class="language-simple"><span style="color:#999999">{</span>"a"<span style="color:#a67f59">:</span> 1<span style="color:#999999">,</span> "b"<span style="color:#a67f59">:</span> 2<span style="color:#999999">}</span> <span style="color:#a67f59">=</span> <span style="color:#999999">{</span>"b"<span style="color:#a67f59">:</span> 2<span style="color:#999999">,</span> "a"<span style="color:#a67f59">:</span> 1<span style="color:#999999">}</span></code></span></span>

两个不相等的对象的顺序是未指定的但是确定性的。

STRING字符串在被比较的两个字符串N的utf8mb4表示的第一个字节上以词法顺序排序，其中N是较短字符串的长度。如果N两个字符串的第一个字节相同，则认为较短的字符串小于较长的字符串。
例：

<span style="color:#555555"><span style="color:black"><code class="language-simple">"a" <span style="color:#a67f59"><</span> "ab" <span style="color:#a67f59"><</span> "b" <span style="color:#a67f59"><</span> "bc"</code></span></span>

此排序等同于具有排序规则的SQL字符串的排序utf8mb4_bin。因为 utf8mb4_bin是二进制排序规则，JSON值的比较区分大小写：

<span style="color:#555555"><span style="color:black"><code class="language-simple">"A" <span style="color:#a67f59"><</span> "a"</code></span></span>

INTEGER， DOUBLEJSON值可以包含精确值数字和近似值数字。有关这些类型数字的一般讨论，请参见第9.1.2节“数字文字”。
第12.2节“表达式求值中的类型转换” 中讨论了比较本机MySQL数值类型的规则，但是比较JSON值中的数字的规则有所不同：

在分别使用本机MySQL INT和 DOUBLE数字类型的两列之间的比较中，已知所有比较都涉及整数和双精度，因此对于所有行，整数被转换为双精度。也就是说，精确值数字被转换为近似值数字。
另一方面，如果查询比较包含数字的两个JSON列，则无法预先知道数字是整数还是双数。为了在所有行中提供最一致的行为，MySQL将近似值数字转换为精确值数字。生成的顺序是一致的，并且不会丢失精确值数字的精度。例如，给定标量9223372036854775805,9223372036854775806,9223372036854775807和9.223372036854776e18，顺序如下：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#990055">9223372036854775805</span> <span style="color:#a67f59"><</span> <span style="color:#990055">9223372036854775806</span> <span style="color:#a67f59"><</span> <span style="color:#990055">9223372036854775807</span>
<span style="color:#a67f59"><</span> <span style="color:#990055">9.223372036854776e18</span> <span style="color:#a67f59">=</span> <span style="color:#990055">9223372036854776000</span> <span style="color:#a67f59"><</span> <span style="color:#990055">9223372036854776001</span></code></span></span>

如果JSON比较使用非JSON数字比较规则，则可能发生不一致的排序。通常的数字MySQL比较规则产生这些排序：

整数比较：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#990055">9223372036854775805</span> <span style="color:#a67f59"><</span> <span style="color:#990055">9223372036854775806</span> <span style="color:#a67f59"><</span> <span style="color:#990055">9223372036854775807</span></code></span></span>

（未定义为9.223372036854776e18）

双重比较：

<span style="color:#555555"><span style="color:black"><code class="language-simple">9223372036854775805 <span style="color:#a67f59">=</span> 9223372036854775806 <span style="color:#a67f59">=</span> 9223372036854775807 <span style="color:#a67f59">=</span> 9<span style="color:#999999">.</span>223372036854776e18</code></span></span>

为了将任何JSON值与SQL进行比较NULL，结果是UNKNOWN。

为了比较JSON和非JSON值，根据下表中的规则将非JSON值转换为JSON，然后如前所述比较值。

在JSON和非JSON值之间转换

下表提供了MySQL在JSON值和其他类型的值之间进行转换时遵循的规则的摘要：

表11.3 JSON转换规则

其他类型	CAST（其他类型AS JSON）	CAST（JSON AS其他类型）
JSON	没变	没变
UTF8字符类型（`utf8mb4`， `utf8`，`ascii`）	该字符串被解析为JSON值。	JSON值被序列化为`utf8mb4`字符串。
其他字符类型	其他字符编码被隐式转换 `utf8mb4`为utf8字符类型所描述和处理。	JSON值被序列化为`utf8mb4`字符串，然后转换为其他字符编码。结果可能没有意义。
`NULL`	结果`NULL`为JSON类型的值。	不适用。
几何类型	几何值通过调用转换为JSON文档ST_AsGeoJSON()。	非法操作。解决方法：将结果传递给。CAST(`json_val` AS CHAR)ST_GeomFromGeoJSON()
所有其他类型	结果是由单个标量值组成的JSON文档。	如果JSON文档由目标类型的单个标量值组成，并且标量值可以强制转换为目标类型，则成功。否则，返回`NULL` 并发出警告。

ORDER BY并且GROUP BY对于JSON值，根据以下原则工作：

标量JSON值的排序使用与前面讨论中相同的规则。
对于升序排序，SQL NULL订单在所有JSON值之前，包括JSON null文字; 对于降序排序，NULL所有JSON值之后的SQL 顺序，包括JSON空文字。
JSON值的排序键由max_sort_length系统变量的值绑定，因此仅在第一个max_sort_length字节比较之后才相同的键相等。
目前不支持对非标量值进行排序，并发出警告。

对于排序，将JSON标量转换为其他一些本机MySQL类型可能是有益的。例如，如果名为jdoc包含JSON对象的列具有由id键和非负值组成的成员，请使用此表达式按id 值排序：

<span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#0077aa">ORDER</span> <span style="color:#0077aa">BY</span> <span style="color:#dd4a68">CAST</span><span style="color:#999999">(</span><span style="color:#dd4a68">JSON_EXTRACT</span><span style="color:#999999">(</span>jdoc<span style="color:#999999">,</span> <span style="color:#669900">'$.id'</span><span style="color:#999999">)</span> <span style="color:#0077aa">AS</span> <span style="color:#0077aa">UNSIGNED</span><span style="color:#999999">)</span></code></span></span>

如果确定生成的列定义为使用与该表达式相同的表达式ORDER BY，则MySQL优化器会识别该列并考虑使用索引执行查询执行计划。请参见第8.3.10节“生成列索引的优化程序使用”。

JSON值的聚合

对于JSON值的聚合，SQL NULL 值将与其他数据类型一样被忽略。非NULL值被转换为数字类型和聚合，除 MIN()， MAX()和GROUP_CONCAT()。转换为数字应该为数字标量的JSON值产生有意义的结果，尽管（取决于值）可能会发生截断和精度损失。转换为其他JSON值的数量可能不会产生有意义的结果。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。