SQLJSON详解

一、SQLJSON格式提取

SQLJSON是MySQL 5.7版本引入的新功能，它提供了一种将JSON数据添加到SQL语句中的方式，可以使开发人员更加方便地操作JSON数据。SQLJSON格式提取是SQLJSON的一大特性，它可以将一个JSON对象转换成多个行，每行对应JSON的一个键值对。下面是一个SQLJSON格式提取的例子：

SELECT json_extract('{"id":1,"name":"John","age":25}', '$.*');

这个例子中的json_extract函数将会将一个JSON对象中的所有键值对都提取出来，输出的结果是：

+---------+
|   $.*   |
+---------+
|     id |
|   name |
|   age   |
+---------+

可以看到，JSON对象中的”id”、”name”和”age”三个键都被提取出来了。

二、SQLJSON字段比较查询

SQLJSON还可以进行字段比较查询，可以使用json_contains函数来实现。例如：

SELECT JSON_CONTAINS('{"id":1,"name":"John","age":25}', '{"name":"John"}');

这个例子中，json_contains函数将会判断第一个JSON对象是否包含第二个JSON对象，输出的结果为：

+----------------------------------+
| json_contains('{"id":1,"name":"John","age":25}', '{"name":"John"}') |
+----------------------------------+
|                                 1 |
+----------------------------------+

可以看到，第一个JSON对象中确实包含”name”:”John”这个键值对。

三、SQLJSON选取

SQLJSON还可以通过json_extract函数来选取JSON对象中的某个键对应的值，也可以同时选取多个键对应的值。例如：

SELECT json_extract('{"id":1,"name":"John","age":25}', '$.name');

这个例子中，json_extract函数将会选取第一个JSON对象中的”name”键对应的值，输出的结果为：

+---------------+
| json_extract('{"id":1,"name":"John","age":25}', '$.name') |
+---------------+
|            John |
+---------------+

可以看到，选取了”name”键对应的值。

除了选取单个键对应的值，json_extract函数还可以同时选取多个键对应的值，例如：

SELECT json_extract('{"id":1,"name":"John","age":25}', '$.name', '$.age');

这个例子中，json_extract函数将会选取第一个JSON对象中的”name”和”age”两个键对应的值，输出的结果为：

+------------------------------------+
| json_extract('{"id":1,"name":"John","age":25}', '$.name', '$.age') |
+------------------------------------+
|                                                           John, 25 |
+------------------------------------+

可以看到，同时选取了”name”和”age”键对应的值。

四、SQLJSON数组操作

SQLJSON还可以进行数组操作，可以使用json_array函数将多个JSON对象组成一个JSON数组。例如：

SELECT json_array('{"id":1,"name":"John","age":25}', '{"id":2,"name":"Lily","age":22}');

这个例子中，json_array函数将会将两个JSON对象组成一个JSON数组，输出的结果为：

+----------------------------------------------------------------------------------------------------------------------------------------+
| json_array('{"id":1,"name":"John","age":25}', '{"id":2,"name":"Lily","age":22}') |
+----------------------------------------------------------------------------------------------------------------------------------------+
| [{"id":1,"name":"John","age":25}, {"id":2,"name":"Lily","age":22}] |
+----------------------------------------------------------------------------------------------------------------------------------------+

可以看到，两个JSON对象组成了一个JSON数组。

除了将多个JSON对象组成一个JSON数组，json_array函数还可以用来将多个值组成一个JSON数组，例如：

SELECT json_array('John',25);

这个例子中，json_array函数将会将”John”和25两个值组成一个JSON数组，输出的结果为：

+--------------------------------+
| json_array('John',25) |
+--------------------------------+
|                        ["John",25] |
+--------------------------------+

可以看到，”John”和25两个值组成了一个JSON数组。

五、SQLJSON扩展性

SQLJSON具有很强的扩展性，它可以与其他MySQL功能组合使用，比如JOIN操作、子查询等等。例如，我们可以使用json_extract函数在一个JSON数组中查询某个字段：

SELECT json_extract('[{"id":1,"name":"John","age":25},{"id":2,"name":"Lily","age":22}]', '$[1].name');

这个例子中，json_extract函数将会从一个JSON数组中选取第二个元素的”name”字段的值，输出的结果为：

+------------------------------------------------------------------------+
| json_extract('[{"id":1,"name":"John","age":25},{"id":2,"name":"Lily","age":22}]', '$[1].name') |
+------------------------------------------------------------------------+
|                                                                     Lily |
+------------------------------------------------------------------------+

可以看到，成功选取了第二个元素的”name”字段的值。