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。

以上查詢將返回以下結果:

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