MySQL 5.7 JSON函数学习

概述

MySQL在5.7版本中引入JSON支持，并在后续版本中持续增强。
官方文档：
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

上述所有函数，可以作用于json、text、varchar等类型的字段。$表示整个JSON对象，在索引数据时用下标（对于JSON array，从0开始）或键值（对于JSON object，含有特殊字符的key要用"括起来，比如$.“my name”）。

操作符

-> ->>操作符，按照key找值；
区别：->>会去除包裹的"及转义符号，其等价Function是JSON_EXTRACT()

-- {"mascot": "Our mascot is a dolphin named \"Sakila\"."}
SELECT col->"$.mascot" FROM qtest;
-- | "Our mascot is a dolphin named \"Sakila\"." |
SELECT sentence->>"$.mascot" FROM facts;
-- | Our mascot is a dolphin named "Sakila". |

JSON Path expression

即->>后双引号中的内容，语法是ECMAScript规范的一部分。支持语法$[n to m]

• .代表所有的members in object
• []代表所有的cells in array
• [prefix] ** suffix代表以prefix开始，以suffix为结束的所有路径

优先级

JSON中的数据可以用=, <, <=, >, >=, <>, !=, and <=>进行比较。但JSON里的数据类型可以是多样的，不同类型之间进行比较时，有优先级概念，高优先级的要大于低优先级的（可用JSON_TYPE()函数查看类型）。优先级从高到低如下：
BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL

函数分类

创建函数(Functions That Create JSON Values)

1. JSON_ARRAY
2. JSON_OBJECT
3. JSON_QUOTE
4. CONVERT

查询函数(Functions That Search JSON Values)

1. JSON_CONTAINS
2. JSON_CONTAINS_PATH
3. JSON_EXTRACT
   json_extract(<field>,'$.name');
4. JSON_KEYS
5. JSON_SEARCH

修改函数(Functions That Modify JSON Values)

1. JSON_APPEND

2. JSON_ARRAY_APPEND

3. JSON_ARRAY_INSERT

4. JSON_INSERT

5. JSON_REPLACE

6. JSON_SET

7. JSON_MERGE
   JSON_MERGE(json_doc, json_doc[, json_doc] ...)
   merge多个json文档。规则：
   如果都是json array，则结果自动merge为一个json array；
   如果都是json object，则结果自动merge为一个json object；
   如果有多种类型，则将非json array的元素封装成json array再按照规则一进行mege。

8. JSON_REMOVE

9. JSON_UNQUOTE

JSON属性查询函数(Functions That Return JSON Value Attributes)

1. JSON_DEPTH
   获取json文档的深度。如果参数为NULL，则返回NULL。空json array、json object或标量的深度为1。
   SELECT JSON_DEPTH('[10, {"a": 20}]');
   输出：3

2. JSON_LENGTH
   JSON_LENGTH(json_doc[, path])
   获取指定路径下的长度。如果参数为NULL，则返回NULL。长度的计算规则：
   标量的长度为1；
   json array的长度为元素的个数；
   json object的长度为key的个数。

SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b'); output: 1

3. JSON_TYPE
   获取json文档的具体类型

4. JSON_VALID
   判断val是否为有效的json格式，是为1，不是为0。如果参数为NUL，则返回NULL。

$.* 返回全部json
$.title 返回key='title’的数据
$**.text 返回所有最底层key='text’的数据
$.content[*].item1[*] 返回key=content的list的key=item1的list的所有内容

json：

1. json类型字段可以为NULL
2. 插入数据时，json类型的字段必须时一个有效的json字符串
3. 可以使用JSON_OBJECT()函数构造json对象
4. 使用JSON_ARRAY()函数构造json数组

实例

JSON_MERGE with group by

参考MySQL json_merge with group by

有如下JSON数据：

{
    "warnData": {
        "warnList": [
            {
                "phone": false,
                "wechat": true,
                "name": "zhangsan",
                "mobileNo": false,
                "email": false
            },
            {
                "phone": false,
                "wechat": true,
                "name": "lisi",
                "mobileNo": false,
                "email": false
            }
        ]
    }
}

期望获取的数据，即输出如下：zhangsan,lisi
SQL如下：

select json_extract(datajson, '$.warnData.warnList[*].name') as receiver

其他

-- JSON_SET JSON_INSERT JSON_REPLACE JSON_REMOVE
SET @j = '["a", {"b": [true, false]}, [10, 20]]';
SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
-- | ["a", {"b": [1, false]}, [10, 20, 2]]    

SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
-- ["a", {"b": [true, false]}, [10, 20, 2]]

SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
-- ["a", {"b": [1, false]}, [10, 20]]

SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
-- ["a", {"b": [true]}]

参考

MySQL常用Json函数
mysql5.7之json格式与json_extract方法使用
MySQL5.7的JSON函数介绍—搜索