• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    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数组。

    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或参数个数为奇数,则抛错。

    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用"号括起来。

    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)

    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。

    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表示所有的都存在才行。

    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里。

    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+里可以用"->"替代。

    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+,还可以用"->>"表示去掉抽取结果的"号,下面三种效果是一样的:

    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。

    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。

    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"                 |
    +-------------------------------------------+

    3.3 修改函数

    3.3.1 JSON_APPEND/JSON_ARRAY_APPEND

    JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

    在指定path的json array尾部追加val。如果指定path是一个json object,则将其封装成一个json array再追加。如果有参数为NULL,则返回NULL。

    mysql> SET @j = '["a", ["b", "c"], "d"]';
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
    +----------------------------------+
    | JSON_ARRAY_APPEND(@j, '$[1]', 1) |
    +----------------------------------+
    | ["a", ["b", "c", 1], "d"]    |
    +----------------------------------+
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);
    +----------------------------------+
    | JSON_ARRAY_APPEND(@j, '$[0]', 2) |
    +----------------------------------+
    | [["a", 2], ["b", "c"], "d"]   |
    +----------------------------------+
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
    +-------------------------------------+
    | JSON_ARRAY_APPEND(@j, '$[1][0]', 3) |
    +-------------------------------------+
    | ["a", [["b", 3], "c"], "d"]     |
    +-------------------------------------+
     
    mysql> SET @j = '{"a": 1, "b": [2, 3], "c": 4}';
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');
    +------------------------------------+
    | JSON_ARRAY_APPEND(@j, '$.b', 'x') |
    +------------------------------------+
    | {"a": 1, "b": [2, 3, "x"], "c": 4} |
    +------------------------------------+
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');
    +--------------------------------------+
    | JSON_ARRAY_APPEND(@j, '$.c', 'y')  |
    +--------------------------------------+
    | {"a": 1, "b": [2, 3], "c": [4, "y"]} |
    +--------------------------------------+
     
    mysql> SET @j = '{"a": 1}';
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z');
    +---------------------------------+
    | JSON_ARRAY_APPEND(@j, '$', 'z') |
    +---------------------------------+
    | [{"a": 1}, "z"]         |
    +---------------------------------+
    

    3.3.2 JSON_ARRAY_INSERT

    JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

    在path指定的json array元素插入val,原位置及以右的元素顺次右移。如果path指定的数据非json array元素,则略过此val;如果指定的元素下标超过json array的长度,则插入尾部。

    mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
    mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
    +------------------------------------+
    | JSON_ARRAY_INSERT(@j, '$[1]', 'x') |
    +------------------------------------+
    | ["a", "x", {"b": [1, 2]}, [3, 4]] |
    +------------------------------------+
    mysql> SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x');
    +--------------------------------------+
    | JSON_ARRAY_INSERT(@j, '$[100]', 'x') |
    +--------------------------------------+
    | ["a", {"b": [1, 2]}, [3, 4], "x"]  |
    +--------------------------------------+
    mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x');
    +-----------------------------------------+
    | JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x') |
    +-----------------------------------------+
    | ["a", {"b": ["x", 1, 2]}, [3, 4]]    |
    +-----------------------------------------+
    mysql> SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y');
    +---------------------------------------+
    | JSON_ARRAY_INSERT(@j, '$[2][1]', 'y') |
    +---------------------------------------+
    | ["a", {"b": [1, 2]}, [3, "y", 4]]   |
    +---------------------------------------+
    mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y');
    +----------------------------------------------------+
    | JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y') |
    +----------------------------------------------------+
    | ["x", "a", {"b": [1, 2]}, [3, 4]]         |
    +----------------------------------------------------+
    

    3.3.3 JSON_INSERT/JSON_REPLACE/JSON_SET

    JSON_INSERT(json_doc, path, val[, path, val] ...)

    在指定path下插入数据,如果path已存在,则忽略此val(不存在才插入)。

    mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
    mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
    +----------------------------------------------------+
    | JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |
    +----------------------------------------------------+
    | {"a": 1, "b": [2, 3], "c": "[true, false]"}    |
    +----------------------------------------------------+

    JSON_REPLACE(json_doc, path, val[, path, val] ...)

    替换指定路径的数据,如果某个路径不存在则略过(存在才替换)。如果有参数为NULL,则返回NULL。

    mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
    mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
    +-----------------------------------------------------+
    | JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
    +-----------------------------------------------------+
    | {"a": 10, "b": [2, 3]}               |
    +-----------------------------------------------------+
    

    JSON_SET(json_doc, path, val[, path, val] ...)

    设置指定路径的数据(不管是否存在)。如果有参数为NULL,则返回NULL。

    mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
    mysql> SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');
    +-------------------------------------------------+
    | JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') |
    +-------------------------------------------------+
    | {"a": 10, "b": [2, 3], "c": "[true, false]"}  |
    +-------------------------------------------------+
    mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
    +----------------------------------------------------+
    | JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |
    +----------------------------------------------------+
    | {"a": 1, "b": [2, 3], "c": "[true, false]"}    |
    +----------------------------------------------------+
    mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
    +-----------------------------------------------------+
    | JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
    +-----------------------------------------------------+
    | {"a": 10, "b": [2, 3]}               |
    +-----------------------------------------------------+
    

    3.3.4 JSON_MERGE

    JSON_MERGE(json_doc, json_doc[, json_doc] ...)

    merge多个json文档。规则如下:

    mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]');
    +---------------------------------------+
    | JSON_MERGE('[1, 2]', '[true, false]') |
    +---------------------------------------+
    | [1, 2, true, false]          |
    +---------------------------------------+
    mysql> SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}');
    +-------------------------------------------+
    | JSON_MERGE('{"name": "x"}', '{"id": 47}') |
    +-------------------------------------------+
    | {"id": 47, "name": "x"}          |
    +-------------------------------------------+
    mysql> SELECT JSON_MERGE('1', 'true');
    +-------------------------+
    | JSON_MERGE('1', 'true') |
    +-------------------------+
    | [1, true]        |
    +-------------------------+
    mysql> SELECT JSON_MERGE('[1, 2]', '{"id": 47}');
    +------------------------------------+
    | JSON_MERGE('[1, 2]', '{"id": 47}') |
    +------------------------------------+
    | [1, 2, {"id": 47}]         |
    +------------------------------------+
    

    3.3.5 JSON_REMOVE

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

    移除指定路径的数据,如果某个路径不存在则略过此路径。如果有参数为NULL,则返回NULL。

    mysql> SET @j = '["a", ["b", "c"], "d"]';
    mysql> SELECT JSON_REMOVE(@j, '$[1]');
    +-------------------------+
    | JSON_REMOVE(@j, '$[1]') |
    +-------------------------+
    | ["a", "d"]       |
    +-------------------------+
    

    3.3.6 JSON_UNQUOTE

    JSON_UNQUOTE(val)

    去掉val的引号。如果val为NULL,则返回NULL。

    mysql> SET @j = '"abc"';
    mysql> SELECT @j, JSON_UNQUOTE(@j);
    +-------+------------------+
    | @j  | JSON_UNQUOTE(@j) |
    +-------+------------------+
    | "abc" | abc       |
    +-------+------------------+
    mysql> SET @j = '[1, 2, 3]';
    mysql> SELECT @j, JSON_UNQUOTE(@j);
    +-----------+------------------+
    | @j    | JSON_UNQUOTE(@j) |
    +-----------+------------------+
    | [1, 2, 3] | [1, 2, 3]    |
    +-----------+------------------+
    

    3.4 JSON特性查询

    3.4.1 JSON_DEEPTH

    JSON_DEPTH(json_doc)

    获取json文档的深度。如果参数为NULL,则返回NULL。

    空的json array、json object或标量的深度为1。

    mysql> SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');
    +------------------+------------------+--------------------+
    | JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') |
    +------------------+------------------+--------------------+
    |        1 |        1 |         1 |
    +------------------+------------------+--------------------+
    mysql> SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]');
    +------------------------+------------------------+
    | JSON_DEPTH('[10, 20]') | JSON_DEPTH('[[], {}]') |
    +------------------------+------------------------+
    |           2 |           2 |
    +------------------------+------------------------+
    mysql> SELECT JSON_DEPTH('[10, {"a": 20}]');
    +-------------------------------+
    | JSON_DEPTH('[10, {"a": 20}]') |
    +-------------------------------+
    |               3 |
    +-------------------------------+
    

    3.4.2 JSON_LENGTH

    JSON_LENGTH(json_doc[, path])

    获取指定路径下的长度。如果参数为NULL,则返回NULL。 

    长度的计算规则:

    mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
    +---------------------------------+
    | JSON_LENGTH('[1, 2, {"a": 3}]') |
    +---------------------------------+
    |                3 |
    +---------------------------------+
    mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
    +-----------------------------------------+
    | JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
    +-----------------------------------------+
    |                    2 |
    +-----------------------------------------+
    mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');
    +------------------------------------------------+
    | JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |
    +------------------------------------------------+
    |                       1 |
    +------------------------------------------------+
    

    3.4.3 JSON_TYPE

    JSON_TYPE(json_val)

    获取json文档的具体类型。如果参数为NULL,则返回NULL。

    3.4.4 JSON_VALID

    JSON_VALID(val)

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

    mysql> SELECT JSON_VALID('{"a": 1}');
    +------------------------+
    | JSON_VALID('{"a": 1}') |
    +------------------------+
    |           1 |
    +------------------------+
    mysql> SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
    +---------------------+-----------------------+
    | JSON_VALID('hello') | JSON_VALID('"hello"') |
    +---------------------+-----------------------+
    |          0 |           1 |
    +---------------------+-----------------------+

    到此这篇关于MySQL处理JSON常见函数的使用的文章就介绍到这了,更多相关MySQL JSON常见函数内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!

    您可能感兴趣的文章:
    • MySQL空间数据存储及函数
    • MySQL中日期型单行函数代码详解
    • MySql关于null的函数使用分享
    • Mysql基础之常见函数
    • 详解Mysql函数调用优化
    • MySQL中sum函数使用的实例教程
    • MySQL DATE_ADD和ADDDATE函数实现向日期添加指定时间间隔
    • 浅谈MySQL函数
    上一篇:MySQL和Python交互的示例
    下一篇:mysql数据库删除重复数据只保留一条方法实例
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    MySQL处理JSON常见函数的使用 MySQL,处理,JSON,常见,函数,