您的位置:首页 > 数据库 > > 正文

mysql 查询json(MySQL处理JSON常见函数的使用)

更多 时间:2021-10-19 06:45:34 类别:数据库 浏览量:1230

mysql 查询json

MySQL处理JSON常见函数的使用

官方文档:JSON Functions

Name Description
JSON_APPEND() Append data to JSON document
JSON_ARRAY() Create JSON array
JSON_ARRAY_APPEND() Append data to JSON document
JSON_ARRAY_INSERT() Insert into JSON array
-> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
JSON_CONTAINS() Whether JSON document contains specific object at path
JSON_CONTAINS_PATH() Whether JSON document contains any data at path
JSON_DEPTH() Maximum depth of JSON document
JSON_EXTRACT() Return data from JSON document
->> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
JSON_INSERT() Insert data into JSON document
JSON_KEYS() Array of keys from JSON document
JSON_LENGTH() Number of elements in JSON document
JSON_MERGE() Merge JSON documents
JSON_OBJECT() Create JSON object
JSON_QUOTE() Quote JSON document
JSON_REMOVE() Remove data from JSON document
JSON_REPLACE() Replace values in JSON document
JSON_SEARCH() Path to value within JSON document
JSON_SET() Insert data into JSON document
JSON_TYPE() Type of JSON value
JSON_UNQUOTE() Unquote JSON value
JSON_VALID() Whether JSON value is valid

1. 概述

MySQL里的json分为json array和json object。 $表示整个json对象,在索引数据时用下标(对于json array,从0开始)或键值(对于json object,含有特殊字符的key要用"括起来,比如$."my name")。

例如:[3, {"a": [5, 6], "b": 10}, [99, 100]],那么:

$[0]:3

$[1]: {"a": [5, 6], "b": 10}

$[2] :[99, 100]

$[3] : NULL

$[1].a:[5, 6]

$[1].a[1]:6

$[1].b:10

$[2][0]:99

2. 比较规则

json中的数据可以用 =, <, <=, >, >=, <>, !=, and <=> 进行比较。但json里的数据类型可以是多样的,那么在不同类型之间进行比较时,就有优先级了,高优先级的要大于低优先级的(可以用JSON_TYPE()函数查看类型)。优先级从高到低如下:

 BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL 

3. 常用函数

3.1 创建函数

3.1.1 JSON_ARRAY

JSON_ARRAY(val1,val2,val3...)

生成一个包含指定元素的json数组。

  •  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
  • +---------------------------------------------+
  • | JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
  • +---------------------------------------------+
  • | [1, "abc", null, true, "11:30:24.000000"]  |
  • +---------------------------------------------+
  • 3.1.2 JSON_OBJECT

    JSON_OBJECT(key1,val1,key2,val2...)

    生成一个包含指定K-V对的json object。如果有key为NULL或参数个数为奇数,则抛错。

  •  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
  • +-----------------------------------------+
  • | JSON_OBJECT('id', 87, 'name', 'carrot') |
  • +-----------------------------------------+
  • | {"id": 87, "name": "carrot"}      |
  • +-----------------------------------------+
  • 3.1.3 JSON_QUOTE

    JSON_QUOTE(json_val)

    将json_val用"号括起来。

  •  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
  • +--------------------+----------------------+
  • | JSON_QUOTE('null') | JSON_QUOTE('"null"') |
  • +--------------------+----------------------+
  • | "null"       | ""null""      |
  • +--------------------+----------------------+
  • mysql> SELECT JSON_QUOTE('[1, 2, 3]');
  • +-------------------------+
  • | JSON_QUOTE('[1, 2, 3]') |
  • +-------------------------+
  • | "[1, 2, 3]"       |
  • +-------------------------+
  • 3.1.4 CONVERT

    CONVERT(json_string,JSON)

  •  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • mysql> select CONVERT('{"mail": "amy@gmail.com", "name": "Amy"}',JSON);
  • +----------------------------------------------------------+
  • | CONVERT('{"mail": "amy@gmail.com", "name": "Amy"}',JSON) |
  • +----------------------------------------------------------+
  • | {"mail": "amy@gmail.com", "name": "Amy"}         |
  • +----------------------------------------------------------+
  • 3.2 查询函数  

    3.2.1 JSON_CONTAINS

    JSON_CONTAINS(json_doc, val[, path])

    查询json文档是否在指定path包含指定的数据,包含则返回1,否则返回0。如果有参数为NULL或path不存在,则返回NULL。

  •  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 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 |
  • +-------------------------------+
  • 3.2.2 JSON_CONTAINS_PATH

    JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

    查询是否存在指定路径,存在则返回1,否则返回0。如果有参数为NULL,则返回NULL。

    one_or_all只能取值"one"或"all",one表示只要有一个存在即可;all表示所有的都存在才行。

  •  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 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 |
  • +----------------------------------------+
  • 3.2.3 JSON_EXTRACT

    JSON_EXTRACT(json_doc, path[, path] ...)

    从json文档里抽取数据。如果有参数有NULL或path不存在,则返回NULL。如果抽取出多个path,则返回的数据封闭在一个json array里。

  •  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 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 5.7.9+里可以用"->"替代。

  •  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 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)
  • 在MySQL 5.7.13+,还可以用"->>"表示去掉抽取结果的"号,下面三种效果是一样的:

    • JSON_UNQUOTE( JSON_EXTRACT(column, path) )
    • JSON_UNQUOTE(column -> path)
    • column->>path
  •  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 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)
  • 3.2.4 JSON_KEYS

    JSON_KEYS(json_doc[, path])

    获取json文档在指定路径下的所有键值,返回一个json array。如果有参数为NULL或path不存在,则返回NULL。

  •  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 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"]                    |
  • +----------------------------------------------+
  • 3.2.5 JSON_SEARCH

    JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

    查询包含指定字符串的paths,并作为一个json array返回。如果有参数为NUL或path不存在,则返回NULL。

    • one_or_all:"one"表示查询到一个即返回;"all"表示查询所有。
    • search_str:要查询的字符串。 可以用LIKE里的'%'或‘_'匹配。
    • path:在指定path下查。
  •  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 标签:mysql JSON
  • 上一篇:常见的php五大运行模式详解(php设计模式之职责链模式定义与用法经典示例)
  • 下一篇:国内云主机哪家靠谱(云主机哪家比较便宜)
  • 您可能感兴趣