本节中的函数对 JSON 值执行搜索或比较操作,以从中提取数据,报告数据是否存在于其内的某个位置,或报告其内数据的路径。 MEMBER OF()
运算符也在此处进行说明。
JSON_CONTAINS(
target
,candidate
[,path
])通过返回 1 或 0 来指示给定的
candidate
JSON 文档是否包含在target
JSON 文档中,或者(如果提供了path
参数)候选者是否在目标的特定路径中找到。 如果任何参数为NULL
,或者路径参数未识别目标文档的一部分,则返回NULL
。 如果target
或candidate
不是有效的 JSON 文档,或者path
参数不是有效的路径表达式或包含*
或**
通配符,则会发生错误。要仅检查路径是否存在任何数据,请使用
JSON_CONTAINS_PATH()
代替。以下规则定义了包含性
如果且仅当候选标量可比较且相等,则候选标量包含在目标标量中。 两个标量值可比较,如果它们具有相同的
JSON_TYPE()
类型,但类型为INTEGER
和DECIMAL
的值也彼此可比较。如果且仅当候选者中的每个元素都包含在目标中的某个元素中,则候选数组包含在目标数组中。
如果且仅当候选非数组包含在目标数组中的某个元素中,则候选非数组包含在目标数组中。
如果且仅当候选者中的每个键在目标中都存在同名键,并且与候选者键关联的值包含在与目标键关联的值中,则候选对象包含在目标对象中。
否则,候选值不包含在目标文档中。
在
InnoDB
表上使用JSON_CONTAINS()
的查询可以使用多值索引进行优化;有关更多信息,请参阅 多值索引。mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}'; mysql> SET @j2 = '1'; mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a'); +-------------------------------+ | JSON_CONTAINS(@j, @j2, '$.a') | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b'); +-------------------------------+ | JSON_CONTAINS(@j, @j2, '$.b') | +-------------------------------+ | 0 | +-------------------------------+ mysql> SET @j2 = '{"d": 4}'; mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a'); +-------------------------------+ | JSON_CONTAINS(@j, @j2, '$.a') | +-------------------------------+ | 0 | +-------------------------------+ mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c'); +-------------------------------+ | JSON_CONTAINS(@j, @j2, '$.c') | +-------------------------------+ | 1 | +-------------------------------+
JSON_CONTAINS_PATH(
json_doc
,one_or_all
,path
[,path
] ...)返回 0 或 1 以指示 JSON 文档是否在给定路径或路径中包含数据。 如果任何参数为
NULL
,则返回NULL
。 如果json_doc
参数不是有效的 JSON 文档,任何path
参数不是有效的路径表达式,或者one_or_all
不是'one'
或'all'
,则会发生错误。要检查路径中的特定值,请使用
JSON_CONTAINS()
代替。如果文档中不存在指定的路径,则返回值为 0。 否则,返回值取决于
one_or_all
参数'one'
: 如果文档中至少存在一条路径,则为 1,否则为 0。'all'
: 如果文档中存在所有路径,则为 1,否则为 0。
mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}'; mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e'); +---------------------------------------------+ | JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') | +---------------------------------------------+ | 1 | +---------------------------------------------+ mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e'); +---------------------------------------------+ | JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') | +---------------------------------------------+ | 0 | +---------------------------------------------+ mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d'); +----------------------------------------+ | JSON_CONTAINS_PATH(@j, 'one', '$.c.d') | +----------------------------------------+ | 1 | +----------------------------------------+ mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d'); +----------------------------------------+ | JSON_CONTAINS_PATH(@j, 'one', '$.a.d') | +----------------------------------------+ | 0 | +----------------------------------------+
JSON_EXTRACT(
json_doc
,path
[,path
] ...)返回 JSON 文档中的数据,这些数据是从由
path
参数匹配的文档部分中选择的。 如果任何参数为NULL
,或者没有路径在文档中定位到值,则返回NULL
。 如果json_doc
参数不是有效的 JSON 文档,或者任何path
参数不是有效的路径表达式,则会发生错误。返回值由与
path
参数匹配的所有值组成。 如果这些参数可能返回多个值,则匹配的值将自动包装为数组,顺序对应于生成它们的路径。 否则,返回值是单个匹配值。mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]'); +--------------------------------------------+ | JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') | +--------------------------------------------+ | 20 | +--------------------------------------------+ mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]'); +----------------------------------------------------+ | JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') | +----------------------------------------------------+ | [20, 10] | +----------------------------------------------------+ mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]'); +-----------------------------------------------+ | JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') | +-----------------------------------------------+ | [30, 40] | +-----------------------------------------------+
MySQL 支持
->
运算符作为此函数的简写,该函数用于使用 2 个参数,其中左侧是JSON
列标识符(而不是表达式),右侧是要在列中匹配的 JSON 路径。->
运算符在使用两个参数时作为JSON_EXTRACT()
函数的别名,左侧是列标识符,右侧是 JSON 路径(字符串文字),该路径针对 JSON 文档(列值)进行评估。 您可以在 SQL 语句中使用此类表达式代替列引用,无论它们出现在何处。此处所示的两个
SELECT
语句生成相同的输出mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g > FROM jemp > WHERE JSON_EXTRACT(c, "$.id") > 1 > ORDER BY JSON_EXTRACT(c, "$.name"); +-------------------------------+-----------+------+ | c | c->"$.id" | g | +-------------------------------+-----------+------+ | {"id": "3", "name": "Barney"} | "3" | 3 | | {"id": "4", "name": "Betty"} | "4" | 4 | | {"id": "2", "name": "Wilma"} | "2" | 2 | +-------------------------------+-----------+------+ 3 rows in set (0.00 sec) mysql> SELECT c, c->"$.id", g > FROM jemp > WHERE c->"$.id" > 1 > ORDER BY c->"$.name"; +-------------------------------+-----------+------+ | c | c->"$.id" | g | +-------------------------------+-----------+------+ | {"id": "3", "name": "Barney"} | "3" | 3 | | {"id": "4", "name": "Betty"} | "4" | 4 | | {"id": "2", "name": "Wilma"} | "2" | 2 | +-------------------------------+-----------+------+ 3 rows in set (0.00 sec)
此功能不仅限于
SELECT
,如这里所示mysql> ALTER TABLE jemp ADD COLUMN n INT; Query OK, 0 rows affected (0.68 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> UPDATE jemp SET n=1 WHERE c->"$.id" = "4"; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT c, c->"$.id", g, n > FROM jemp > WHERE JSON_EXTRACT(c, "$.id") > 1 > ORDER BY c->"$.name"; +-------------------------------+-----------+------+------+ | c | c->"$.id" | g | n | +-------------------------------+-----------+------+------+ | {"id": "3", "name": "Barney"} | "3" | 3 | NULL | | {"id": "4", "name": "Betty"} | "4" | 4 | 1 | | {"id": "2", "name": "Wilma"} | "2" | 2 | NULL | +-------------------------------+-----------+------+------+ 3 rows in set (0.00 sec) mysql> DELETE FROM jemp WHERE c->"$.id" = "4"; Query OK, 1 row affected (0.04 sec) mysql> SELECT c, c->"$.id", g, n > FROM jemp > WHERE JSON_EXTRACT(c, "$.id") > 1 > ORDER BY c->"$.name"; +-------------------------------+-----------+------+------+ | c | c->"$.id" | g | n | +-------------------------------+-----------+------+------+ | {"id": "3", "name": "Barney"} | "3" | 3 | NULL | | {"id": "2", "name": "Wilma"} | "2" | 2 | NULL | +-------------------------------+-----------+------+------+ 2 rows in set (0.00 sec)
(有关用于创建和填充刚刚显示的表的语句,请参阅 索引生成的列以提供 JSON 列索引。)
这同样适用于 JSON 数组值,如下所示
mysql> CREATE TABLE tj10 (a JSON, b INT); Query OK, 0 rows affected (0.26 sec) mysql> INSERT INTO tj10 > VALUES ("[3,10,5,17,44]", 33), ("[3,10,5,17,[22,44,66]]", 0); Query OK, 1 row affected (0.04 sec) mysql> SELECT a->"$[4]" FROM tj10; +--------------+ | a->"$[4]" | +--------------+ | 44 | | [22, 44, 66] | +--------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM tj10 WHERE a->"$[0]" = 3; +------------------------------+------+ | a | b | +------------------------------+------+ | [3, 10, 5, 17, 44] | 33 | | [3, 10, 5, 17, [22, 44, 66]] | 0 | +------------------------------+------+ 2 rows in set (0.00 sec)
支持嵌套数组。如果在目标 JSON 文档中未找到匹配的键,则使用
->
的表达式将计算为NULL
,如下所示mysql> SELECT * FROM tj10 WHERE a->"$[4][1]" IS NOT NULL; +------------------------------+------+ | a | b | +------------------------------+------+ | [3, 10, 5, 17, [22, 44, 66]] | 0 | +------------------------------+------+ mysql> SELECT a->"$[4][1]" FROM tj10; +--------------+ | a->"$[4][1]" | +--------------+ | NULL | | 44 | +--------------+ 2 rows in set (0.00 sec)
这与在使用
JSON_EXTRACT()
时此类情况下看到的行为相同mysql> SELECT JSON_EXTRACT(a, "$[4][1]") FROM tj10; +----------------------------+ | JSON_EXTRACT(a, "$[4][1]") | +----------------------------+ | NULL | | 44 | +----------------------------+ 2 rows in set (0.00 sec)
这是一个改进的、不加引号的提取运算符。虽然
->
运算符只是提取一个值,但->>
运算符还会对提取的结果进行不加引号处理。换句话说,给定一个JSON
列值column
和一个路径表达式path
(一个字符串文字),以下三个表达式返回相同的值JSON_UNQUOTE(
column
->
path
)column
->>path
可以在允许使用
JSON_UNQUOTE(JSON_EXTRACT())
的任何地方使用->>
运算符。这包括(但不限于)SELECT
列表、WHERE
和HAVING
子句以及ORDER BY
和GROUP BY
子句。接下来的几个语句演示了一些
->>
运算符与 mysql 客户端中其他表达式的等价关系mysql> SELECT * FROM jemp WHERE g > 2; +-------------------------------+------+ | c | g | +-------------------------------+------+ | {"id": "3", "name": "Barney"} | 3 | | {"id": "4", "name": "Betty"} | 4 | +-------------------------------+------+ 2 rows in set (0.01 sec) mysql> SELECT c->'$.name' AS name -> FROM jemp WHERE g > 2; +----------+ | name | +----------+ | "Barney" | | "Betty" | +----------+ 2 rows in set (0.00 sec) mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name -> FROM jemp WHERE g > 2; +--------+ | name | +--------+ | Barney | | Betty | +--------+ 2 rows in set (0.00 sec) mysql> SELECT c->>'$.name' AS name -> FROM jemp WHERE g > 2; +--------+ | name | +--------+ | Barney | | Betty | +--------+ 2 rows in set (0.00 sec)
有关用于创建和填充前面示例集中
jemp
表的 SQL 语句,请参阅 为提供 JSON 列索引而对生成的列进行索引。此运算符也可以与 JSON 数组一起使用,如下所示
mysql> CREATE TABLE tj10 (a JSON, b INT); Query OK, 0 rows affected (0.26 sec) mysql> INSERT INTO tj10 VALUES -> ('[3,10,5,"x",44]', 33), -> ('[3,10,5,17,[22,"y",66]]', 0); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT a->"$[3]", a->"$[4][1]" FROM tj10; +-----------+--------------+ | a->"$[3]" | a->"$[4][1]" | +-----------+--------------+ | "x" | NULL | | 17 | "y" | +-----------+--------------+ 2 rows in set (0.00 sec) mysql> SELECT a->>"$[3]", a->>"$[4][1]" FROM tj10; +------------+---------------+ | a->>"$[3]" | a->>"$[4][1]" | +------------+---------------+ | x | NULL | | 17 | y | +------------+---------------+ 2 rows in set (0.00 sec)
与
->
一样,->>
运算符始终在EXPLAIN
的输出中扩展,如下面的示例所示mysql> EXPLAIN SELECT c->>'$.name' AS name -> FROM jemp WHERE g > 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: jemp partitions: NULL type: range possible_keys: i key: i key_len: 5 ref: NULL rows: 2 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select json_unquote(json_extract(`jtest`.`jemp`.`c`,'$.name')) AS `name` from `jtest`.`jemp` where (`jtest`.`jemp`.`g` > 2) 1 row in set (0.00 sec)
这与 MySQL 在相同情况下扩展
->
运算符的方式类似。将 JSON 对象的顶层值的键作为 JSON 数组返回,或者,如果给定了
path
参数,则返回所选路径的顶层键。如果任何参数为NULL
、json_doc
参数不是对象,或者给定了path
但未找到对象,则返回NULL
。如果json_doc
参数不是有效的 JSON 文档,或者path
参数不是有效的路径表达式,或者包含*
或**
通配符,则会发生错误。如果所选对象为空,则结果数组为空。如果顶层值具有嵌套的子对象,则返回值不包括这些子对象的键。
mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}'); +---------------------------------------+ | JSON_KEYS('{"a": 1, "b": {"c": 30}}') | +---------------------------------------+ | ["a", "b"] | +---------------------------------------+ mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b'); +----------------------------------------------+ | JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') | +----------------------------------------------+ | ["c"] | +----------------------------------------------+
JSON_OVERLAPS(
json_doc1
,json_doc2
)比较两个 JSON 文档。如果两个文档具有任何键值对或数组元素相同,则返回 true (1)。如果两个参数都是标量,则函数执行简单的相等性测试。如果任何参数为
NULL
,则函数返回NULL
。此函数充当
JSON_CONTAINS()
的对应函数,后者要求要搜索的数组的所有元素都存在于要搜索的数组中。因此,JSON_CONTAINS()
对搜索键执行AND
操作,而JSON_OVERLAPS()
执行OR
操作。使用
WHERE
子句中的JSON_OVERLAPS()
在InnoDB
表的 JSON 列上执行的查询可以使用多值索引进行优化。 多值索引 提供了详细的信息和示例。当比较两个数组时,如果它们共享一个或多个数组元素,则
JSON_OVERLAPS()
返回 true;如果它们不共享任何元素,则返回 falsemysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]"); +---------------------------------------+ | JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]") | +---------------------------------------+ | 1 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]"); +---------------------------------------+ | JSON_OVERLAPS("[1,3,5,7]", "[2,6,7]") | +---------------------------------------+ | 1 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]"); +---------------------------------------+ | JSON_OVERLAPS("[1,3,5,7]", "[2,6,8]") | +---------------------------------------+ | 0 | +---------------------------------------+ 1 row in set (0.00 sec)
部分匹配将被视为不匹配,如下所示
mysql> SELECT JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]'); +-----------------------------------------------------+ | JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]') | +-----------------------------------------------------+ | 0 | +-----------------------------------------------------+ 1 row in set (0.00 sec)
当比较对象时,如果它们至少共享一个键值对,则结果为 true。
mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}'); +-----------------------------------------------------------------------+ | JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}') | +-----------------------------------------------------------------------+ | 1 | +-----------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}'); +-----------------------------------------------------------------------+ | JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"a":5,"e":10,"f":1,"d":20}') | +-----------------------------------------------------------------------+ | 0 | +-----------------------------------------------------------------------+ 1 row in set (0.00 sec)
如果使用两个标量作为函数的参数,
JSON_OVERLAPS()
将执行简单的相等性测试mysql> SELECT JSON_OVERLAPS('5', '5'); +-------------------------+ | JSON_OVERLAPS('5', '5') | +-------------------------+ | 1 | +-------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_OVERLAPS('5', '6'); +-------------------------+ | JSON_OVERLAPS('5', '6') | +-------------------------+ | 0 | +-------------------------+ 1 row in set (0.00 sec)
当比较标量与数组时,
JSON_OVERLAPS()
尝试将标量视为数组元素。在本例中,第二个参数6
被解释为[6]
,如下所示mysql> SELECT JSON_OVERLAPS('[4,5,6,7]', '6'); +---------------------------------+ | JSON_OVERLAPS('[4,5,6,7]', '6') | +---------------------------------+ | 1 | +---------------------------------+ 1 row in set (0.00 sec)
函数不执行类型转换
mysql> SELECT JSON_OVERLAPS('[4,5,"6",7]', '6'); +-----------------------------------+ | JSON_OVERLAPS('[4,5,"6",7]', '6') | +-----------------------------------+ | 0 | +-----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_OVERLAPS('[4,5,6,7]', '"6"'); +-----------------------------------+ | JSON_OVERLAPS('[4,5,6,7]', '"6"') | +-----------------------------------+ | 0 | +-----------------------------------+ 1 row in set (0.00 sec)
JSON_SEARCH(
json_doc
,one_or_all
,search_str
[,escape_char
[,path
] ...])返回 JSON 文档中给定字符串的路径。如果任何
json_doc
、search_str
或path
参数为NULL
;文档中不存在path
;或者未找到search_str
,则返回NULL
。如果json_doc
参数不是有效的 JSON 文档,任何path
参数不是有效的路径表达式,one_or_all
不是'one'
或'all'
,或者escape_char
不是常量表达式,则会发生错误。one_or_all
参数会影响搜索,如下所示'one'
: 搜索在第一次匹配后终止,并返回一个路径字符串。哪一个匹配被认为是第一个是未定义的。'all'
: 搜索将返回所有匹配的路径字符串,以确保不包含重复的路径。如果有多个字符串,它们将自动包装为数组。数组元素的顺序是未定义的。
在
search_str
搜索字符串参数中,%
和_
字符与LIKE
运算符的作用相同:%
匹配任意数量的字符(包括零个字符),_
匹配正好一个字符。要在搜索字符串中指定文字
%
或_
字符,请在其前面加上转义字符。如果escape_char
参数缺失或为NULL
,则默认值为\
。否则,escape_char
必须是一个为空或为一个字符的常量。有关匹配和转义字符行为的更多信息,请参阅
LIKE
在 第 14.8.1 节,“字符串比较函数和运算符” 中的描述。对于转义字符处理,与LIKE
行为的不同之处在于,JSON_SEARCH()
的转义字符必须在编译时求值为常量,而不仅仅是在执行时求值为常量。例如,如果JSON_SEARCH()
用于预处理语句,并且escape_char
参数使用?
参数提供,则参数值可能在执行时为常量,但在编译时不为常量。mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]'; mysql> SELECT JSON_SEARCH(@j, 'one', 'abc'); +-------------------------------+ | JSON_SEARCH(@j, 'one', 'abc') | +-------------------------------+ | "$[0]" | +-------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', 'abc'); +-------------------------------+ | JSON_SEARCH(@j, 'all', 'abc') | +-------------------------------+ | ["$[0]", "$[2].x"] | +-------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi'); +-------------------------------+ | JSON_SEARCH(@j, 'all', 'ghi') | +-------------------------------+ | NULL | +-------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', '10'); +------------------------------+ | JSON_SEARCH(@j, 'all', '10') | +------------------------------+ | "$[1][0].k" | +------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$'); +-----------------------------------------+ | JSON_SEARCH(@j, 'all', '10', NULL, '$') | +-----------------------------------------+ | "$[1][0].k" | +-----------------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]'); +--------------------------------------------+ | JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') | +--------------------------------------------+ | "$[1][0].k" | +--------------------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k'); +---------------------------------------------+ | JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') | +---------------------------------------------+ | "$[1][0].k" | +---------------------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k'); +-------------------------------------------------+ | JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') | +-------------------------------------------------+ | "$[1][0].k" | +-------------------------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]'); +--------------------------------------------+ | JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') | +--------------------------------------------+ | "$[1][0].k" | +--------------------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]'); +-----------------------------------------------+ | JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') | +-----------------------------------------------+ | "$[1][0].k" | +-----------------------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]'); +---------------------------------------------+ | JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') | +---------------------------------------------+ | "$[2].x" | +---------------------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', '%a%'); +-------------------------------+ | JSON_SEARCH(@j, 'all', '%a%') | +-------------------------------+ | ["$[0]", "$[2].x"] | +-------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', '%b%'); +-------------------------------+ | JSON_SEARCH(@j, 'all', '%b%') | +-------------------------------+ | ["$[0]", "$[2].x", "$[3].y"] | +-------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]'); +---------------------------------------------+ | JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') | +---------------------------------------------+ | "$[0]" | +---------------------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]'); +---------------------------------------------+ | JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') | +---------------------------------------------+ | "$[2].x" | +---------------------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]'); +---------------------------------------------+ | JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') | +---------------------------------------------+ | NULL | +---------------------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]'); +-------------------------------------------+ | JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') | +-------------------------------------------+ | NULL | +-------------------------------------------+ mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]'); +-------------------------------------------+ | JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') | +-------------------------------------------+ | "$[3].y" | +-------------------------------------------+
有关 MySQL 支持的 JSON 路径语法的更多信息,包括控制通配符运算符
*
和**
的规则,请参阅 JSON 路径语法。从指定文档中给定路径的 JSON 文档中提取值,并返回提取的值,可以选择将其转换为所需类型。完整的语法如下所示
JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error]) on_empty: {NULL | ERROR | DEFAULT value} ON EMPTY on_error: {NULL | ERROR | DEFAULT value} ON ERROR
json_doc
是一个有效的 JSON 文档。如果为NULL
,则函数返回NULL
。path
是指向文档中位置的 JSON 路径。这必须是一个字符串文字值。type
是以下数据类型之一这些类型与
CAST()
函数支持的(非数组)类型相同。如果未通过
RETURNING
子句指定,则JSON_VALUE()
函数的返回类型为VARCHAR(512)
。当未为返回类型指定字符集时,JSON_VALUE()
使用具有二进制排序规则的utf8mb4
,它是区分大小写的;如果将utf8mb4
指定为结果的字符集,则服务器将使用此字符集的默认排序规则,它不区分大小写。当指定路径处的数据包含或解析为 JSON null 文字时,函数将返回 SQL
NULL
。on_empty
(如果指定)将确定JSON_VALUE()
在未找到给定路径处的数据时的行为;此子句将采用以下值之一NULL ON EMPTY
: 函数返回NULL
;这是默认的ON EMPTY
行为。DEFAULT
: 返回提供的value
ON EMPTYvalue
。该值的类型必须与返回类型匹配。ERROR ON EMPTY
: 函数将引发错误。
如果使用,
on_error
将采用以下值之一,并具有相应的结果,如下所示NULL ON ERROR
:JSON_VALUE()
返回NULL
;如果未使用ON ERROR
子句,这是默认行为。DEFAULT
: 返回此值;其值必须与返回类型匹配。value
ON ERRORERROR ON ERROR
: 将引发错误。
如果使用,
on_empty
必须位于任何ON ERROR
子句之前。以错误的顺序指定它们会导致语法错误。错误处理。 通常,
JSON_VALUE()
处理错误的方式如下将检查所有 JSON 输入(文档和路径)的有效性。如果任何输入无效,将引发 SQL 错误,而不会触发
ON ERROR
子句。当发生以下事件时,将触发
ON ERROR
尝试提取对象或数组,例如由解析为 JSON 文档中多个位置的路径导致的对象或数组
转换错误,例如尝试将
'asdf'
转换为UNSIGNED
值值截断
即使指定了
NULL ON ERROR
或DEFAULT ... ON ERROR
,转换错误也会始终触发警告。当源 JSON 文档 (
expr
) 在指定位置 (path
) 没有数据时,ON EMPTY
子句将被触发。
示例。 这里显示了两个简单的示例
mysql> SELECT JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname'); +--------------------------------------------------------------+ | JSON_VALUE('{"fname": "Joe", "lname": "Palmer"}', '$.fname') | +--------------------------------------------------------------+ | Joe | +--------------------------------------------------------------+ mysql> SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price' -> RETURNING DECIMAL(4,2)) AS price; +-------+ | price | +-------+ | 49.95 | +-------+
语句
SELECT JSON_VALUE(
等效于以下语句json_doc
,path
RETURNINGtype
)SELECT CAST( JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) ) AS type );
JSON_VALUE()
简化了在 JSON 列上创建索引,在许多情况下,它不需要创建生成的列,然后再在生成的列上创建索引。在创建具有JSON
列的表t1
时,可以通过在使用JSON_VALUE()
操作该列的表达式(其路径与该列中的值匹配)上创建索引来实现此目的,如下所示CREATE TABLE t1( j JSON, INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) ) );
以下
EXPLAIN
输出显示,针对t1
的查询使用WHERE
子句中的索引表达式使用了这样创建的索引mysql> EXPLAIN SELECT * FROM t1 -> WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 123\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ref possible_keys: i1 key: i1 key_len: 9 ref: const rows: 1 filtered: 100.00 Extra: NULL
这与创建具有在生成列上的索引的表
t2
实现了几乎相同的效果(请参阅 为 JSON 列提供索引的生成列索引),如下所示CREATE TABLE t2 ( j JSON, g INT GENERATED ALWAYS AS (j->"$.id"), INDEX i1 (g) );
针对此表的查询的
EXPLAIN
输出,引用生成列,显示索引的使用方式与之前针对表t1
的查询相同mysql> EXPLAIN SELECT * FROM t2 WHERE g = 123\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: NULL type: ref possible_keys: i1 key: i1 key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: NULL
有关在生成列上使用索引进行
JSON
列的间接索引的信息,请参阅 为 JSON 列提供索引的生成列索引。如果
value
是json_array
的元素,则返回 true (1),否则返回 false (0)。value
必须是标量或 JSON 文档;如果是标量,则运算符尝试将其视为 JSON 数组的元素。如果value
或json_array
为NULL
,则函数返回NULL
。在
WHERE
子句中对InnoDB
表的 JSON 列使用MEMBER OF()
的查询可以使用多值索引进行优化。有关详细信息和示例,请参阅 多值索引。简单的标量被视为数组值,如下所示
mysql> SELECT 17 MEMBER OF('[23, "abc", 17, "ab", 10]'); +-------------------------------------------+ | 17 MEMBER OF('[23, "abc", 17, "ab", 10]') | +-------------------------------------------+ | 1 | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]'); +---------------------------------------------+ | 'ab' MEMBER OF('[23, "abc", 17, "ab", 10]') | +---------------------------------------------+ | 1 | +---------------------------------------------+ 1 row in set (0.00 sec)
数组元素值的局部匹配不匹配
mysql> SELECT 7 MEMBER OF('[23, "abc", 17, "ab", 10]'); +------------------------------------------+ | 7 MEMBER OF('[23, "abc", 17, "ab", 10]') | +------------------------------------------+ | 0 | +------------------------------------------+ 1 row in set (0.00 sec)
mysql> SELECT 'a' MEMBER OF('[23, "abc", 17, "ab", 10]'); +--------------------------------------------+ | 'a' MEMBER OF('[23, "abc", 17, "ab", 10]') | +--------------------------------------------+ | 0 | +--------------------------------------------+ 1 row in set (0.00 sec)
不会执行转换为字符串类型和从字符串类型转换
mysql> SELECT -> 17 MEMBER OF('[23, "abc", "17", "ab", 10]'), -> "17" MEMBER OF('[23, "abc", 17, "ab", 10]')\G *************************** 1. row *************************** 17 MEMBER OF('[23, "abc", "17", "ab", 10]'): 0 "17" MEMBER OF('[23, "abc", 17, "ab", 10]'): 0 1 row in set (0.00 sec)
要将此运算符与本身为数组的值一起使用,需要将其显式地转换为 JSON 数组。可以使用
CAST(... AS JSON)
来实现此目的mysql> SELECT CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]'); +--------------------------------------------------+ | CAST('[4,5]' AS JSON) MEMBER OF('[[3,4],[4,5]]') | +--------------------------------------------------+ | 1 | +--------------------------------------------------+ 1 row in set (0.00 sec)
也可以使用
JSON_ARRAY()
函数执行必要的转换,如下所示mysql> SELECT JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]'); +--------------------------------------------+ | JSON_ARRAY(4,5) MEMBER OF('[[3,4],[4,5]]') | +--------------------------------------------+ | 1 | +--------------------------------------------+ 1 row in set (0.00 sec)
用作要测试的值或出现在目标数组中的任何 JSON 对象必须使用
CAST(... AS JSON)
或JSON_OBJECT()
转换为正确的类型。此外,包含 JSON 对象的目标数组本身必须使用JSON_ARRAY
进行转换。以下语句序列演示了这一点mysql> SET @a = CAST('{"a":1}' AS JSON); Query OK, 0 rows affected (0.00 sec) mysql> SET @b = JSON_OBJECT("b", 2); Query OK, 0 rows affected (0.00 sec) mysql> SET @c = JSON_ARRAY(17, @b, "abc", @a, 23); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @a MEMBER OF(@c), @b MEMBER OF(@c); +------------------+------------------+ | @a MEMBER OF(@c) | @b MEMBER OF(@c) | +------------------+------------------+ | 1 | 1 | +------------------+------------------+ 1 row in set (0.00 sec)