MySQL 8.0中的一个新的JSON函数是JSON_TABLE。它也是MySQL的第一个表函数。也就是说，返回值不是标量值而是结果集。JSON_TABLE将JSON文档(部分)转换为关系表。在这篇博客文章中，我将向您展示如何做到这一点，并讨论JSON_TABLE如何启用使用SQL处理JSON数据的新方法。
JSON_TABLE例子
首先，我将创建一个表t1，其中的一列为JSON类型，并向表中插入一个JSON文档:
CREATE TABLE t1(json_col JSON);
INSERT INTO t1 VALUES (
'{ "people": [
{ "name":"John Smith", "address":"780 Mission St, San Francisco, CA 94103"},
{ "name":"Sally Brown", "address":"75 37th Ave S, St Cloud, MN 94103"},
{ "name":"John Johnson", "address":"1262 Roosevelt Trail, Raymond, ME 04071"}
] }'
);我可以使用以下SQL查询转换为关系表的文件:
SELECT people.*
FROM t1,
JSON_TABLE(json_col, '$.people[*]' COLUMNS (
name VARCHAR(40) PATH '$.name',
address VARCHAR(100) PATH '$.address')
) people;因为JSON_TABLE返回一个结果集，所以可以在FROM子句中使用它。JSON_TABLE接受以下参数:
- 1.JSON数据源:这个表达式可以引用FROM列表中前面表中的列。在本例中，json_col指的是包含JSON文档的列。(注意，按照SQL标准的规定，前面的表和引用该表的JSON_TABLE之间有一个隐式的横向连接(lateral join)。换句话说，对于前面的表的每一行，将“调用”JSON_TABLE函数。)
- 2.应该转换为表的JSON数组的路径。在本例中，它是people数组的对象。
- 3.那些表中的列，其列名、类型和路径应该在JSON对象中可以找到值。
我们还需要给返回的表一个名称，这里我们称它为people。
以上查询将返回以下结果:
| NAME | ADDRESS |
|---|---|
| John Smith | 780 Mission St, San Francisco, CA 94103 |
| Sally Brown | 75 37th Ave S, St Cloud, MN 9410 |
| John Johnson | 1262 Roosevelt Trail, Raymond, ME 04071 |
对JSON文档的关系操作
当我们使用JSON_TABLE将JSON文档转换为表时，我们可以使用“SQL工具集”并对数据执行诸如选择和聚合之类的关系操作。例如，我们可以使用这个查询来选择只叫John的人:
SELECT people.*
FROM t1,
JSON_TABLE(json_col, '$.people[*]' COLUMNS (
name VARCHAR(40) PATH '$.name',
address VARCHAR(100) PATH '$.address')
) people;
WHERE people.name LIKE 'John%';| NAME | ADDRESS |
|---|---|
| John Smith | 780 Mission St, San Francisco, CA 94103 |
| John Johnson | 1262 Roosevelt Trail, Raymond, ME 04071 |
另一个MySQL 8.0中的新函数JSON_ARRAYAGG，我们现在可以将结果转换回JSON文档:
SELECT JSON_OBJECT("people",
JSON_ARRAYAGG(JSON_OBJECT("name", name, "address", address))) json_doc
FROM t1,
JSON_TABLE(json_col, '$.people[*]' COLUMNS (
name VARCHAR(40) PATH '$.name',
address VARCHAR(100) PATH '$.address')
) people;
WHERE people.name LIKE 'John%';JSON文档
{"people": [{"name": "John Smith", "address": "780 Mission St, San Francisco, CA 94103"}, {"name": "John Johnson", "address": "1262 Roosevelt Trail, Raymond, ME 04071"}]}如上所示，我们可以通过JSON_TABLE和JSON_ARRAYAGG对JSON文档执行关系操作。
嵌套的JSON数组
JSON_TABLE还可以处理嵌套JSON数组。给定以下JSON数组与家族对象，有数组与子对象:
[
{
"father": "John",
"mother": "Mary",
"children": [
{
"age": 12,
"name": "Eric"
},
{
"age": 10,
"name": "Beth"
}
],
"marriage_date": "2003-12-05"
},
{
"father": "Paul",
"mother": "Laura",
"children": [
{
"age": 9,
"name": "Sarah"
},
{
"age": 3,
"name": "Noah"
},
{
"age": 1,
"name": "Peter"
}
]
}
]我们想要将这个文档转换为每个子元素对应一行的表:
| ID | FATHER | MARRIED | CHILD_ID | CHILD | AGE |
|---|---|---|---|---|---|
| 1 | John | 1 | 1 | Eric | 12 |
| 1 | John | 1 | 2 | Beth | 10 |
| 2 | Paul | 0 | 1 | Sarah | 9 |
| 2 | Paul | 0 | 2 | Noah | 3 |
| 2 | Paul | 0 | 3 | Peter | 1 |
JSON_TABLE (families, '$[*]' COLUMNS (
id FOR ORDINALITY,
father VARCHAR(30) PATH '$.father',
married INTEGER EXISTS PATH '$.marriage_date',
NESTED PATH '$.children[*]' COLUMNS (
child_id FOR ORDINALITY,
child VARCHAR(30) PATH '$.name',
age INTEGER PATH '$.age') )
)我们使用嵌套路径来指定应该从子数组中提取哪些值。这个示例还表明，我们可以通过指定序数而不是路径来为行分配id。我们还可以使用EXISTS PATH检查路径是否存在。这里，如果找到结婚日期，则married为1，否则为0。
对JSON数据进行SQL聚合
一旦我们使用JSON_TABLE将JSON数据转换为关系表，我们就可以利用SQL聚合来计算JSON数据的计数、总和、平均值等。使用上面提供的例子，这个查询将计算每个家庭孩子的平均年龄:
SELECT father, COUNT(*) "#children", AVG(age) "age average"
FROM t,
JSON_TABLE (families, '$[*]' COLUMNS (
id FOR ORDINALITY,
father VARCHAR(30) PATH '$.father',
NESTED PATH '$.children[*]' COLUMNS (
age INTEGER PATH '$.age' ) )
) fam
GROUP BY id, father; | ATHER#CHILDREN | AGE | AVERAGE |
|---|---|---|
| John | 2 | 11.0000 |
| Paul | 3 | 4.3333 |
我们甚至可以使用JSON_MERGE_PATCH函数将计算出来的数据放回JSON文档中:
SELECT JSON_ARRAYAGG(fam_obj) families
FROM (
SELECT JSON_MERGE_PATCH(family,
JSON_OBJECT("#children", COUNT(*), "avg_age" , AVG(age))) fam_obj
FROM t,
JSON_TABLE (families, '$[*]' COLUMNS (
id FOR ORDINALITY,
family JSON PATH '$',
NESTED PATH '$.children[*]' COLUMNS (
age INTEGER PATH '$.age' ) )
) fam
GROUP BY id, family) fams;这里，我们将现有的family对象与计算后的总和和平均值构造的对象合并。然后，JSON_ARRAYAGG将把所有合并的对象放回一个数组中。
结论
在这篇博客文章中，我展示了如何使用JSON_TABLE对JSON数据执行关系操作。使用JSON_ARRAYAGG，您可以采取相反的做法，将结果集转换为JSON文档。这能让你两全其美;您可以将数据存储为JSON格式，但同时也可以利用SQL的强大功能。
感谢您使用MySQL !
