MariaDB 操作 JSON 数据的正确打开方式

作者: ___n | 来源:发表于2022-04-07 11:20 被阅读0次

以前，在某些应用场景，当我们需要将整个JSON数据结构存储到数据库时，大部份人的做法，是建立一个大字段，然后把整个JSON数据结构以文本的形式储存到字段里。

当需要使用的时候，从数据库里读取这个字段的内容，再通过代码转换成对象或数组使用，如果需要用到搜索，那么一般的做法是直接LIKE这一个字段或者在代码里面执行查找或循环匹配。

现在 MariaDB 10.2.3+ 已经开始支持 JSON 数据类型了（赶紧升级吧），可以像类似于操作noSQL那样操作JSON数据。

目前 MariaDB 只支持 lax 模式，用 $ 符号表示上下文项 , 而且功能在不断的完善，所以命令也是在不断的迭代增加，在执行的时候要注意安装的版本是否支持命令的使用，以下说明没有标明版本号的，表示 10.2.3+ 版本的 MariaDB 都支持。

函数说明

JSON_QUERY 、JSON_VALUE

这两个函数之间的主要区别是，JSON_QUERY 返回一个对象或数组，而JSON_VALUE返回一个值。

SET @json='{ "x": [0,1], "y": "[0,1]", "z": "Monty" }';
SELECT JSON_QUERY(@json,'$'), JSON_VALUE(@json,'$');
+--------------------------------------------+-----------------------+
| JSON_QUERY(@json,'$')                      | JSON_VALUE(@json,'$') |
+--------------------------------------------+-----------------------+
| { "x": [0,1], "y": "[0,1]", "z": "Monty" } | NULL                  |
+--------------------------------------------+-----------------------+

SELECT JSON_QUERY(@json,'$.x'), JSON_VALUE(@json,'$.x');
+-------------------------+-------------------------+
| JSON_QUERY(@json,'$.x') | JSON_VALUE(@json,'$.x') |
+-------------------------+-------------------------+
| [0,1]                   | NULL                    |
+-------------------------+-------------------------+


SELECT JSON_QUERY(@json,'$.y'), JSON_VALUE(@json,'$.y');
+-------------------------+-------------------------+
| JSON_QUERY(@json,'$.y') | JSON_VALUE(@json,'$.y') |
+-------------------------+-------------------------+
| NULL                    | [0,1]                   |
+-------------------------+-------------------------+

# 注意 x  和 y 一个是有双引号括起来的

SELECT JSON_QUERY(@json,'$.z'), JSON_VALUE(@json,'$.z');
+-------------------------+-------------------------+
| JSON_QUERY(@json,'$.z') | JSON_VALUE(@json,'$.z') |
+-------------------------+-------------------------+
| NULL                    | Monty                   |
+-------------------------+-------------------------+

SELECT JSON_QUERY(@json,'$.x[0]'), JSON_VALUE(@json,'$.x[0]');
+----------------------------+----------------------------+
| JSON_QUERY(@json,'$.x[0]') | JSON_VALUE(@json,'$.x[0]') |
+----------------------------+----------------------------+
| NULL                       | 0                          |
+----------------------------+----------------------------+

JSON_ARRAY

JSON_ARRAY([value[, value2] ...])
返回包含列出值的JSON数组

SELECT Json_Array(56, 3.1416, 'My name is "Foo"', NULL);
+--------------------------------------------------+
| Json_Array(56, 3.1416, 'My name is "Foo"', NULL) |
+--------------------------------------------------+
| [56, 3.1416, "My name is \"Foo\"", null]         |
+--------------------------------------------------+

JSON_ARRAYAGG

JSON_ARRAYAGG(column_or_expression)
JSON_ARRAYAGG 返回一个JSON数组，其中包含给定JSON或SQL值集合中每个值的元素, 这个函数需要 10.5.0+ 的版本才有

CREATE TABLE t1 (a INT, b INT);

INSERT INTO t1 VALUES (1, 1),(2, 1), (1, 1),(2, 1), (3, 2),(2, 2),(2, 2),(2, 2);

SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1;
+-------------------+-------------------+
| JSON_ARRAYAGG(a)  | JSON_ARRAYAGG(b)  |
+-------------------+-------------------+
| [1,2,1,2,3,2,2,2] | [1,1,1,1,2,2,2,2] |
+-------------------+-------------------+

SELECT JSON_ARRAYAGG(a), JSON_ARRAYAGG(b) FROM t1 GROUP BY b;
+------------------+------------------+
| JSON_ARRAYAGG(a) | JSON_ARRAYAGG(b) |
+------------------+------------------+
| [1,2,1,2]        | [1,1,1,1]        |
| [3,2,2,2]        | [2,2,2,2]        |
+------------------+------------------+

JSON_ARRAY_APPEND

JSON_ARRAY_APPEND(json_doc, path, value[, path, value] ...)
将值追加到JSON中指定数组的末尾，并返回结果

SET @json = '[1, 2, [3, 4]]';

SELECT JSON_ARRAY_APPEND(@json, '$[1]', 6, '$[2]', 7);
+------------------------------------------------+
| JSON_ARRAY_APPEND(@json, '$[1]', 6, '$[2]', 7) |
+------------------------------------------------+
| [1, [2, 6], [3, 4, 7]]                         |
+------------------------------------------------+

SELECT JSON_ARRAY_APPEND(@json, '$', 5);
+----------------------------------+
| JSON_ARRAY_APPEND(@json, '$', 5) |
+----------------------------------+
| [1, 2, [3, 4], 5]                |
+----------------------------------+

SET @json = '{"A": 1, "B": [2], "C": [3, 4]}';

SELECT JSON_ARRAY_APPEND(@json, '$.B', 5);
+------------------------------------+
| JSON_ARRAY_APPEND(@json, '$.B', 5) |
+------------------------------------+
| {"A": 1, "B": [2, 5], "C": [3, 4]} |
+------------------------------------+

JSON_ARRAY_INSERT

JSON_ARRAY_INSERT(json_doc, path, value[, path, value] ...)
在JSON中指定位置插入一个值，返回修改后的结果。

SET @json = '[1, 2, [3, 4]]';

SELECT JSON_ARRAY_INSERT(@json, '$[0]', 5);
+-------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[0]', 5) |
+-------------------------------------+
| [5, 1, 2, [3, 4]]                   |
+-------------------------------------+

SELECT JSON_ARRAY_INSERT(@json, '$[1]', 6);
+-------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[1]', 6) |
+-------------------------------------+
| [1, 6, 2, [3, 4]]                   |
+-------------------------------------+

SELECT JSON_ARRAY_INSERT(@json, '$[1]', 6, '$[2]', 7);
+------------------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[1]', 6, '$[2]', 7) |
+------------------------------------------------+
| [1, 6, 7, 2, [3, 4]]                           |
+------------------------------------------------+

JSON_COMPACT

JSON_COMPACT(json_doc)
压缩JSON，删除多余的空格 , 10.2.4+ 版本支持

SET @j = '{ "A": 1, "B": [2, 3]}';

SELECT JSON_COMPACT(@j), @j;
+-------------------+------------------------+
| JSON_COMPACT(@j)  | @j                     |
+-------------------+------------------------+
| {"A":1,"B":[2,3]} | { "A": 1, "B": [2, 3]} |
+-------------------+------------------------+

JSON_CONTAINS

JSON_CONTAINS(json_doc, val[, path])
在JSON中查找指定值，如果找到了返1，没找到返回0

SET @json = '{"A": 0, "B": {"C": 1}, "D": 2}';

SELECT JSON_CONTAINS(@json, '2', '$.A');
+----------------------------------+
| JSON_CONTAINS(@json, '2', '$.A') |
+----------------------------------+
|                                0 |
+----------------------------------+

SELECT JSON_CONTAINS(@json, '2', '$.D');
+----------------------------------+
| JSON_CONTAINS(@json, '2', '$.D') |
+----------------------------------+
|                                1 |
+----------------------------------+

SELECT JSON_CONTAINS(@json, '{"C": 1}', '$.A');
+-----------------------------------------+
| JSON_CONTAINS(@json, '{"C": 1}', '$.A') |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+

SELECT JSON_CONTAINS(@json, '{"C": 1}', '$.B');
+-----------------------------------------+
| JSON_CONTAINS(@json, '{"C": 1}', '$.B') |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

JSON_CONTAINS_PATH

JSON_CONTAINS_PATH(json_doc, return_arg, path[, path] ...)
JSON 是否包含指定的键值。如果有，则返回1;如果没有，则返回0
return_arg 可以是一个匹配或全部匹配:
one - 如果JSON只要匹配到一个就返回1
all - JSON 所有都匹配到时返回1

SET @json = '{"A": 1, "B": [2], "C": [3, 4]}';

SELECT JSON_CONTAINS_PATH(@json, 'one', '$.A', '$.D');
+------------------------------------------------+
| JSON_CONTAINS_PATH(@json, 'one', '$.A', '$.D') |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+

SELECT JSON_CONTAINS_PATH(@json, 'all', '$.A', '$.D');
+------------------------------------------------+
| JSON_CONTAINS_PATH(@json, 'all', '$.A', '$.D') |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+

JSON_DEPTH

JSON_DEPTH(json_doc)
返回 json 数据的层数,注意这里空数组是返回1

ELECT JSON_DEPTH('[]'), JSON_DEPTH('true'), JSON_DEPTH('{}');
+------------------+--------------------+------------------+
| JSON_DEPTH('[]') | JSON_DEPTH('true') | JSON_DEPTH('{}') |
+------------------+--------------------+------------------+
|                1 |                  1 |                1 |
+------------------+--------------------+------------------+

SELECT JSON_DEPTH('[1, 2, 3]'), JSON_DEPTH('[[], {}, []]');
+-------------------------+----------------------------+
| JSON_DEPTH('[1, 2, 3]') | JSON_DEPTH('[[], {}, []]') |
+-------------------------+----------------------------+
|                       2 |                          2 |
+-------------------------+----------------------------+

SELECT JSON_DEPTH('[1, 2, [3, 4, 5, 6], 7]');
+---------------------------------------+
| JSON_DEPTH('[1, 2, [3, 4, 5, 6], 7]') |
+---------------------------------------+
|                                     3 |
+---------------------------------------+

JSON_DETAILED

JSON_DETAILED(json_doc[, tab_size])
JSON 格式化 , 10.2.4 支持

SET @j = '{ "A":1,"B":[2,3]}';

SELECT @j;
+--------------------+
| @j                 |
+--------------------+
| { "A":1,"B":[2,3]} |
+--------------------+

SELECT JSON_DETAILED(@j);
+------------------------------------------------------------+
| JSON_DETAILED(@j)                                          |
+------------------------------------------------------------+
| {
    "A": 1,
    "B": 
    [
        2,
        3
    ]
} |
+------------------------------------------------------------+

JSON_EQUALS

JSON_EQUALS(json1, json2)
检查两个 json 对象是否相等。相等返回1，不想等返回0 ，10.7.0+支持

SELECT JSON_EQUALS('{"a"   :[1, 2, 3],"b":[4]}', '{"b":[4],"a":[1, 2, 3.0]}');
+------------------------------------------------------------------------+
| JSON_EQUALS('{"a"   :[1, 2, 3],"b":[4]}', '{"b":[4],"a":[1, 2, 3.0]}') |
+------------------------------------------------------------------------+
|                                                                      1 |
+------------------------------------------------------------------------+

SELECT JSON_EQUALS('{"a":[1, 2, 3]}', '{"a":[1, 2, 3.01]}');
+------------------------------------------------------+
| JSON_EQUALS('{"a":[1, 2, 3]}', '{"a":[1, 2, 3.01]}') |
+------------------------------------------------------+
|                                                    0 |
+------------------------------------------------------+

JSON_EXISTS

检测JSON的KEY是否存在值。如果找到则返回1，否则返回 0

SELECT JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2");
+------------------------------------------------------------+
| JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2") |
+------------------------------------------------------------+
|                                                          1 |
+------------------------------------------------------------+

SELECT JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key3");
+------------------------------------------------------------+
| JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key3") |
+------------------------------------------------------------+
|                                                          0 |
+------------------------------------------------------------+

SELECT JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]");
+---------------------------------------------------------------+
| JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[1]") |
+---------------------------------------------------------------+
|                                                             1 |
+---------------------------------------------------------------+

SELECT JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[10]");
+----------------------------------------------------------------+
| JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2[10]") |
+----------------------------------------------------------------+
|                                                              0 |
+----------------------------------------------------------------+

JSON_EXTRACT

JSON_EXTRACT(json_doc, path[, path] ...)
返回指定的JSON数据，如果返回的结果存在多个，会自动变为数组

SET @json = '[1, 2, [3, 4]]';

SELECT JSON_EXTRACT(@json, '$[1]');
+-----------------------------+
| JSON_EXTRACT(@json, '$[1]') |
+-----------------------------+
| 2                           |
+-----------------------------+

SELECT JSON_EXTRACT(@json, '$[2]');
+-----------------------------+
| JSON_EXTRACT(@json, '$[2]') |
+-----------------------------+
| [3, 4]                      |
+-----------------------------+

SELECT JSON_EXTRACT(@json, '$[2][1]');
+--------------------------------+
| JSON_EXTRACT(@json, '$[2][1]') |
+--------------------------------+
| 4                              |
+--------------------------------+

JSON_INSERT

JSON_INSERT(json_doc, path, val[, path, val] ...)
将数据插入到JSON中

SET @json = '{ "A": 0, "B": [1, 2]}';

SELECT JSON_INSERT(@json, '$.C', '[3, 4]');
+--------------------------------------+
| JSON_INSERT(@json, '$.C', '[3, 4]')  |
+--------------------------------------+
| { "A": 0, "B": [1, 2], "C":"[3, 4]"} |
+--------------------------------------+

JSON_KEYS

JSON_KEYS(json_doc[, path])
返回JSON的所有键值或指定的键值

SELECT JSON_KEYS('{"A": 1, "B": {"C": 2}}');
+--------------------------------------+
| JSON_KEYS('{"A": 1, "B": {"C": 2}}') |
+--------------------------------------+
| ["A", "B"]                           |
+--------------------------------------+

SELECT JSON_KEYS('{"A": 1, "B": 2, "C": {"D": 3}}', '$.C');
+-----------------------------------------------------+
| JSON_KEYS('{"A": 1, "B": 2, "C": {"D": 3}}', '$.C') |
+-----------------------------------------------------+
| ["D"]                                               |
+-----------------------------------------------------+

JSON_LENGTH

JSON_LENGTH(json_doc[, path])
返回JSON长度,只返回第一层或指定层

JSON_LOOSE

JSON_LOOSE(json_doc)
自动添加空格，格式化 JSON

SET @j = '{ "A":1,"B":[2,3]}';

SELECT JSON_LOOSE(@j), @j;
+-----------------------+--------------------+
| JSON_LOOSE(@j)        | @j                 |
+-----------------------+--------------------+
| {"A": 1, "B": [2, 3]} | { "A":1,"B":[2,3]} |
+-----------------------+--------------------+

JSON_MERGE_PATCH

JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...) , 10.2.25 、 10.3.16 、10.4.5+ 支持
JSON 去重合并 , 并返回合并后的结果

SET @json1 = '[1, 2]';
SET @json2 = '[2, 3]';
SELECT JSON_MERGE_PATCH(@json1,@json2);
+---------------------------------+
| JSON_MERGE_PATCH(@json1,@json2) |
+---------------------------------+
| [2, 3]                          | 
+---------------------------------+

JSON_MERGE_PRESERVE

JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...), 10.2.25 、 10.3.16 、10.4.5+ 支持，其它版本可以使用 JSON_MERGE
JSON 追加合并, 并返回合并后的结果

SET @json1 = '[1, 2]';
SET @json2 = '[2, 3]';
SELECT JSON_MERGE_PRESERVE(@json1,@json2);
+------------------------------------+
| JSON_MERGE_PRESERVE(@json1,@json2) |
+------------------------------------+
| [1, 2, 2, 3]                       |
+------------------------------------+

JSON_NORMALIZE

JSON_NORMALIZE(json1, json2)
数据库创建JSON数据的唯一索引 , 10.0.7+支持

CREATE TABLE t1 (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 val JSON,
 /* 其它字段 */
 PRIMARY KEY (id)
);
#使用JSON_NORMALIZE添加一个唯一的索引

ALTER TABLE t1
   ADD COLUMN jnorm JSON AS (JSON_NORMALIZE(val)) VIRTUAL,
   ADD UNIQUE KEY (jnorm);

#插入测试数据
INSERT INTO t1 (val) VALUES ('{"name":"alice","color":"blue"}');
#插入测试数据
INSERT INTO t1 (val) VALUES ('{ "color": "blue", "name": "alice" }');
#存在相同的数据
ERROR 1062 (23000): Duplicate entry '{"color":"blue","name":"alice"}' for key 'jnorm'

JSON_OBJECT

JSON_OBJECT([key, value[, key, value] ...])
返回一个JSON对象，包含给定的键/值对,注意要一一对应，少一个就会报错

SELECT JSON_OBJECT("id", 1, "name", "Monty");
+---------------------------------------+
| JSON_OBJECT("id", 1, "name", "Monty") |
+---------------------------------------+
| {"id": 1, "name": "Monty"}            |
+---------------------------------------+

JSON_OBJECTAGG

JSON_OBJECTAGG(key, value)
JSON_OBJECTAGG 把键和值转成JSON返回

select * from t1;
+------+-------+
| a    | b     |
+------+-------+
|    1 | Hello |
|    1 | World |
|    2 | This  |
+------+-------+

SELECT JSON_OBJECTAGG(a, b) FROM t1;
+----------------------------------------+
| JSON_OBJECTAGG(a, b)                   |
+----------------------------------------+
| {"1":"Hello", "1":"World", "2":"This"} |
+----------------------------------------+

JSON_OVERLAPS

JSON_OVERLAPS(json_doc1, json_doc2) , 10.9.0+ 支持
如果两个json至少有一个共同的键或数组元素，则返回true。

select * from t1;
+------+-------+
| a    | b     |
+------+-------+
|    1 | Hello |
|    1 | World |
|    2 | This  |
+------+-------+

SELECT JSON_OBJECTAGG(a, b) FROM t1;
+----------------------------------------+
| JSON_OBJECTAGG(a, b)                   |
+----------------------------------------+
| {"1":"Hello", "1":"World", "2":"This"} |
+----------------------------------------+

JSON_QUERY

JSON_QUERY(json_doc, path)
返回一个指定的对象或数组。

select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1');
+-----------------------------------------------------+
| json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1') |
+-----------------------------------------------------+
| {"a":1, "b":[1,2]}                                  |
+-----------------------------------------------------+

select json_query('{"key1":123, "key1": [1,2,3]}', '$.key1');
+-------------------------------------------------------+
| json_query('{"key1":123, "key1": [1,2,3]}', '$.key1') |
+-------------------------------------------------------+
| [1,2,3]                                               |
+-------------------------------------------------------+

JSON_QUOTE

JSON_QUOTE(json_value)
将字符串用双引号括起来，并转义内部引号和其他特殊字符，返回utf8mb4字符串

SELECT JSON_QUOTE('A'), JSON_QUOTE("B"), JSON_QUOTE('"C"');
+-----------------+-----------------+-------------------+
| JSON_QUOTE('A') | JSON_QUOTE("B") | JSON_QUOTE('"C"') |
+-----------------+-----------------+-------------------+
| "A"             | "B"             | "\"C\""           |
+-----------------+-----------------+-------------------+

JSON_REMOVE

JSON_REMOVE(json_doc, path[, path] ...)
删除指定的键并返回结果

SELECT JSON_REMOVE('{"A": 1, "B": 2, "C": {"D": 3}}', '$.C');
+-------------------------------------------------------+
| JSON_REMOVE('{"A": 1, "B": 2, "C": {"D": 3}}', '$.C') |
+-------------------------------------------------------+
| {"A": 1, "B": 2}                                      |
+-------------------------------------------------------+

SELECT JSON_REMOVE('["A", "B", ["C", "D"], "E"]', '$[1]');
+----------------------------------------------------+
| JSON_REMOVE('["A", "B", ["C", "D"], "E"]', '$[1]') |
+----------------------------------------------------+
| ["A", ["C", "D"], "E"]                             |
+----------------------------------------------------+

JSON_REPLACE

JSON_REPLACE(json_doc, path, val[, path, val] ...)
替换更新JSON中的指定值

SELECT JSON_REPLACE('{ "A": 1, "B": [2, 3]}', '$.B[1]', 4);
+-----------------------------------------------------+
| JSON_REPLACE('{ "A": 1, "B": [2, 3]}', '$.B[1]', 4) |
+-----------------------------------------------------+
| { "A": 1, "B": [2, 4]}                              |
+-----------------------------------------------------+

JSON_SEARCH

JSON_SEARCH(json_doc, return_arg, search_str[, escape_char[, path] ...])
根据值查找JSON的键（包含上级），可以使用的参数有：
one ：找到一个就返回
all：返回所有匹配的键

SET @json = '["A", [{"B": "1"}], {"C":"AB"}, {"D":"BC"}]';

SELECT JSON_SEARCH(@json, 'one', 'AB');
+---------------------------------+
| JSON_SEARCH(@json, 'one', 'AB') |
+---------------------------------+
| "$[2].C"                        |
+---------------------------------+

JSON_SET

JSON_SET(json_doc, path, val[, path, val] ...)
更新或插入数据,返回执行后的结果

SELECT JSON_SET(Priv, '$.locked', 'true') FROM mysql.global_priv

JSON_TABLE

将JSON数据转为关系形数据 , 可以像写MYSQL命令一样执行 SELECT UPDATE DELETE , 10.0.6+支持

set @json='
[
  {"name":"Laptop", "color":"black", "price":"1000"},
  {"name":"Jeans",  "color":"blue"}
]';

select * from json_table(@json, '$[*]' 
  columns(
   name  varchar(10) path '$.name', 
   color varchar(10) path '$.color',
   price decimal(8,2) path '$.price' ) 
) as jt;
+--------+-------+---------+
| name   | color | price   |
+--------+-------+---------+
| Laptop | black | 1000.00 |
| Jeans  | blue  |    NULL |
+--------+-------+---------+

#自动ID
set @json='
[
  {"name":"Laptop", "color":"black"},
  {"name":"Jeans",  "color":"blue"}
]';

select * from json_table(@json, '$[*]' 
  columns(
   id for ordinality, 
   name  varchar(10) path '$.name')
) as jt;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Laptop |
|    2 | Jeans  |
+------+--------+

#判断是否存在
set @json='
[
  {"name":"Laptop", "color":"black", "price":1000},
  {"name":"Jeans",  "color":"blue"}
]';

select * from json_table(@json, '$[*]' 
  columns(
   name  varchar(10) path '$.name',
   has_price integer exists path '$.price')
) as jt;
+--------+-----------+
| name   | has_price |
+--------+-----------+
| Laptop |         1 |
| Jeans  |         0 |
+--------+-----------+

#生成单独的行
set @json='
[
  {"name":"Jeans",  "sizes": [32, 34, 36]},
  {"name":"T-Shirt", "sizes":["Medium", "Large"]},
  {"name":"Cellphone"}
]';
select * from json_table(@json, '$[*]' 
  columns(
    name  varchar(10) path '$.name', 
    nested path '$.sizes[*]' columns (
      size varchar(32) path '$'
    )
  )
) as jt;
+-----------+--------+
| name      | size   |
+-----------+--------+
| Jeans     | 32     |
| Jeans     | 34     |
| Jeans     | 36     |
| T-Shirt   | Medium |
| T-Shirt   | Large  |
| Cellphone | NULL   |
+-----------+--------+


set @json='
[
  {"name":"Jeans",  "sizes": [32, 34, 36], "colors":["black", "blue"]}
]';

select * from json_table(@json, '$[*]' 
  columns(
    name  varchar(10) path '$.name', 
    nested path '$.sizes[*]' columns (
      size varchar(32) path '$'
    ),
    nested path '$.colors[*]' columns (
      color varchar(32) path '$'
    )
  )
) as jt;

+-------+------+-------+
| name  | size | color |
+-------+------+-------+
| Jeans | 32   | NULL  |
| Jeans | 34   | NULL  |
| Jeans | 36   | NULL  |
| Jeans | NULL | black |
| Jeans | NULL | blue  |
+-------+------+-------+

JSON_TYPE

JSON_TYPE(json_val)
返回JSON值的类型(作为字符串)

返回类型	值	示例
ARRAY	数组	[1, 2, {"key": "value"}]
OBJECT	对象	{"key":"value"}
BOOLEAN	true/false	true, false
DOUBLE	浮动数	1.2
INTEGER	整数	1
NULL	空（注意返回的是字符串）	null
STRING	字符串	"a sample string"

SELECT JSON_TYPE('{"A": 1, "B": 2, "C": 3}');
+---------------------------------------+
| JSON_TYPE('{"A": 1, "B": 2, "C": 3}') |
+---------------------------------------+
| OBJECT                                |
+---------------------------------------+

JSON_UNQUOTE

JSON_UNQUOTE(val)
去掉JSON的双引号，返回一个字符串，如果参数为空则返回NULL
转义字符 " b f n r t \ uXXXX

SELECT JSON_UNQUOTE('"Monty"');
+-------------------------+
| JSON_UNQUOTE('"Monty"') |
+-------------------------+
| Monty                   |
+-------------------------+

SELECT JSON_UNQUOTE('Si\bng\ting');
+-----------------------------+
| JSON_UNQUOTE('Si\bng\ting') |
+-----------------------------+
| Sng   ing                   |
+-----------------------------+

#可以调 NO_BACKSLASH_ESCAPES 不识别转义序列
SET @@sql_mode = 'NO_BACKSLASH_ESCAPES';
SELECT JSON_UNQUOTE('Si\bng\ting');
+-----------------------------+
| JSON_UNQUOTE('Si\bng\ting') |
+-----------------------------+
| Si\bng\ting                 |
+-----------------------------+

JSON_VALID

JSON_VALID(value)
检查JSON数据合法有效，如果有效则返回1，否则返回0，如果数据为NULL则返回NULL。
在10.4.3+版本中，如果字段类型是JSON类型，这个函数会自动起作用。

SELECT JSON_VALID('{"id": 1, "name": "Monty"}');
+------------------------------------------+
| JSON_VALID('{"id": 1, "name": "Monty"}') |
+------------------------------------------+
|                                        1 |
+------------------------------------------+

SELECT JSON_VALID('{"id": 1, "name": "Monty", "oddfield"}');
+------------------------------------------------------+
| JSON_VALID('{"id": 1, "name": "Monty", "oddfield"}') |
+------------------------------------------------------+
|                                                    0 |
+------------------------------------------------------+

JSON_VALUE

JSON_VALUE(json_doc, path)
返回指这定的JSON值，注意要返回数据或者对象要使用 JSON_QUERY

select json_value('{"key1":123}', '$.key1');
+--------------------------------------+
| json_value('{"key1":123}', '$.key1') |
+--------------------------------------+
| 123                                  |
+--------------------------------------+

select json_value('{"key1": [1,2,3], "key1":123}', '$.key1');
+-------------------------------------------------------+
| json_value('{"key1": [1,2,3], "key1":123}', '$.key1') |
+-------------------------------------------------------+
| 123                                                   |
+-------------------------------------------------------+

MariaDB 操作 JSON 数据的正确打开方式

函数说明

JSON_QUERY 、JSON_VALUE

JSON_ARRAY

JSON_ARRAYAGG

JSON_ARRAY_APPEND

JSON_ARRAY_INSERT

JSON_COMPACT

JSON_CONTAINS

JSON_CONTAINS_PATH

JSON_DEPTH

JSON_DETAILED

JSON_EQUALS

JSON_EXISTS

JSON_EXTRACT

JSON_INSERT

JSON_KEYS

JSON_LENGTH

JSON_LOOSE

JSON_MERGE_PATCH

JSON_MERGE_PRESERVE

JSON_NORMALIZE

JSON_OBJECT

JSON_OBJECTAGG

JSON_QUERY

JSON_QUOTE

JSON_REMOVE

JSON_REPLACE

JSON_SEARCH

JSON_SET

JSON_TABLE

JSON_TYPE

JSON_UNQUOTE

JSON_VALID

JSON_VALUE

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

数据库