JSON_TABLE 两全其美

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。

以上查询将返回以下结果:

对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%';

另一个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"
      }
    ]
  }
]

我们想要将这个文档转换为每个子元素对应一行的表:

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; 

我们甚至可以使用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 !