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 !

原文链接：JSON_TABLE – The Best of Both Worlds September 16, 2018 Øystein Grøvlen