[译文] 介绍 JSON 表

到现在为止，您可能已经了解JavaScript Object Notation (JSON)。JSON 在软件行业中无处不在，因为它为开发人员提供了一种简单灵活的数据管理方式。

在数据库领域，JSON 通常被认为是您可以与 NoSQL 解决方案一起使用的东西。然而，在过去几年中，JSON 集成已经进入了关系世界。并且有充分的理由。在关系数据库中存储 JSON 文档的能力允许您创建混合数据模型，包含结构化和半结构化数据，并享受 JSON 的所有好处，而不必牺牲关系数据库的优点（例如 SQL 和所有东西数据完整性）。

MariaDB 在MariaDB Server 10.2 中引入了用于管理数据库中的 JSON 文档的内置函数。但这只是开始。从那时起，我们一直在努力工作并扩展我们的 JSON 功能。

从 JSON 到表

在MariaDB Server 10.6 中，我们添加JSON_TABLE()了一个强大的新功能，使您能够将 JSON 数据直接转换为关系格式。但是，废话不多说，让我们来看看。为了了解新JSON_TABLE功能，让我们先看一个简单的例子。

我们将首先创建一个名为 的简单表，people该表可用于存储结构化数据、likeid和name值以及半结构化数据，例如，存储一个人的pets.

CREATE TABLE people (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
pets JSON
);

接下来，使用新的人员记录填充表，包括包含宠物详细信息数组的有效 JSON 文档。

INSERT INTO people (id, name, pets) VALUES (1, 'Rob', '[{"type":"dog","name":"Duke"},{"type":"cat","name":"Fluffy"}]');

在 MariaDB Server 10.6 之前，如果您想返回从 pets JSON 字段中提取的表格信息，您可以使用以前存在的 JSON 函数，例如JSON_VALUE()允许您检索标量值的 和JSON_QUERY()允许您检索 JSON 对象的MariaDB 来完成工作。

例如，以下查询：

SELECT 
id,
JSON_VALUE(JSON_QUERY(pets, CONCAT('$[', ind.ind, ']')), '$.type') pet_type,
JSON_VALUE(JSON_QUERY(pets, CONCAT('$[', ind.ind, ']')), '$.name') pet_name
FROM
people CROSS JOIN
(SELECT 0 AS ind UNION ALL SELECT 1 AS ind) ind;

将产生以下结果：

+----------------+------------+
| id | pet_type  | pet_name   |       
+----------------+------------+
| 1  | dog       | Duke       | 
| 1  | cat       | Fluffy     |
+----------------+------------+

虽然JSON_VALUE()和JSON_QUERY()函数本身就非常有用，但很容易看出在这种情况下使用它们会如何快速增加解决方案的复杂性，因为从表面上看，应该是一个相当简单的问题。

进入新 JSON_TABLE功能，让我们的生活更轻松！默认情况下，JSON_TABLE()返回一个由COLUMNS子句中指定的列组成的关系表，每个与路径表达式匹配的 JSON 项对应一行。请注意，它也可以在FROM不使用子查询的情况下直接在子句的一部分中使用。

SELECT p.id, pt.pet_type, pt.pet_name 
FROM  
people p, 
JSON_TABLE(p.pets, 
'$[*]' COLUMNS( 
pet_type VARCHAR(10) PATH '$.type',
pet_name VARCHAR(25) PATH '$.name'
) 
)  
AS pt;

执行上面的 SQL 语句将产生与前一个示例相同的结果。是的，就是这么简单。

+----------------+------------+
| id | pet_type  | pet_name   |       
+----------------+------------+
| 1  | dog       | Duke       | 
| 1  | cat       | Fluffy     |
+----------------+------------+

使用嵌套路径

该JSON_TABLE()函数还通过使用NESTED PATH子句支持嵌套路径值，该子句用于指定嵌套列。

当然，要研究嵌套路径支持，您需要在 JSON 文档中包含一些嵌套数据。继续我们之前的示例，您可以通过使用JSON_INSERT()和JSON_ARRAY()函数插入数组来修改现有人员记录来完成此操作。

对于此示例，让我们添加一个新数组favorite_foods，该数组会调用JSON 文档数组中的两个 JSON 对象中的每一个。

UPDATE people 
SET  
   pets = JSON_INSERT(pets, '$[0].favorite_foods',   
                       JSON_ARRAY('chicken', 'salmon', 'carrots'));
 
UPDATE people 
SET  
   pets = JSON_INSERT(pets, '$[1].favorite_foods',   
                       JSON_ARRAY('tuna', 'turkey'));

执行前面的 SQL 插入语句会将包含在单个人员记录中的现有 JSON 文档修改为以下内容：

[
    {
         "type": "dog",
         "name": "Duke",
         "favorite_foods": [
  "chicken", 
  "salmon", 
  "carrots"
    ]
    },
    {
         "type": "cat",
         "name": "Fluffy",
         "favorite_foods": [
  "tuna", 
  "turkey"
         ]
    }
]

在以下示例中，JSON 路径’[*]'匹配根 JSON 数组中的每个项目。然后 JSON 路径'.favorite_food[*]'表示为NESTED PATH与 中的所有值匹配的favorite_foods，这是您添加到现有 JSON 文档的数组的属性名称。

SELECT p.id, pt.pet_type, pt.pet_name, pt.favorite_food 
FROM 
people p,
JSON_TABLE(p.pets, 
'$[*]' COLUMNS (
pet_type VARCHAR(10) PATH '$.type',
pet_name VARCHAR(25) PATH '$.name',
NESTED PATH '$.favorite_foods[*]' 
COLUMNS (favorite_food VARCHAR(25) PATH '$')
)
) pt;

执行前面的 SQL 语句将产生以下结果集：

+----------------+------------+---------------+
| id | pet_type  | pet_name   | favorite_food |        
+----------------+------------+---------------+
| 1  | dog       | Duke       | chicken       |
| 1  | dog       | Duke       | salmon        |
| 1  | dog       | Duke       | carrots       |
| 1  | cat       | Fluffy     | tuna          |
| 1  | cat       | Fluffy     | turkey        |
+----------------+------------+---------------+