原文作者：罗伯·赫奇佩斯
原文链接：https://dzone.com/articles/using-json-in-mariadb
MariaDB 的 JSON 存储和管理功能的基础知识。
我敢打赌，如果您正在阅读本文，您至少听说过MariaDB。哎呀，也许你甚至在某个时候使用过它。但是，如果没有，我强烈建议您快速浏览一下，因为在最高级别上，它是一个非常可靠、免费（是的，免费）的开源数据库解决方案，您可以将其用于从一些轻量级修补到支持的任何事情成熟的生产级应用程序。
提示：如果您想直接进入 MariaDB 数据库并使用本文中的相同数据集，请务必查看此MariaDB JSON 快速入门指南！
MariaDB 一开始也是其核心，是一个关系数据库管理系统，或简称为 RDBMS，但深入了解它的功能，您会很快发现它远不止这些。
很多很多...
其中一项功能是它能够处理JavaScript 对象表示法 (JSON)格式的数据，完全免费且开箱即用。好的，但为什么这很重要？好吧，在数据库的上下文中，JSON 通常被认为是您可以与 NoSQL 解决方案一起使用的东西。这是有道理的，因为“NoSQL 革命”旨在解决的问题之一是灵活性，或者能够创建、更新和删除数据以及它们所在的结构，而无需修改那些讨厌的关系数据库之类的东西模式。
结构化与半结构化数据
在过去的几年里，由于 NoSQL 解决方案通过使用半结构化数据而取得的成功，JSON 集成已经进入了关系世界。并且有充分的理由。在关系数据库中存储 JSON 文档的能力允许您创建包含结构化和半结构化数据的混合数据模型，并享受 JSON 的所有好处，而不必牺牲关系数据库的优势（例如 SQL 和所有东西）数据的完整性）。
在过去的几年里，由于 NoSQL 解决方案通过使用半结构化数据所取得的成功，JSON 集成已经进入了关系世界。
好的，这个“设置舞台”业务已经够多了。让我们看看 MariaDB 中可用的一些 JSON 功能以及如何使用它。我说一些是因为 MariaDB 包含大量的 JSON 功能。事实上，一篇博文无法涵盖太多内容。但是，我们当然可以达到最高点，这应该为您提供更深入潜水所需的基础。
结构化数据+半结构化数据
在许多用例中，结合结构化和半结构化数据可能是有意义的。这就是你的软件开发世界。但是，我始终发现通过专注于一个简单的（希望是）相关的用例来使用新技术是最容易的，然后您可以使用该用例来获得自己的创意源泉。
为了帮助了解 MariaDB 中可用的 JSON 功能，我将使用一个假设的应用程序。这个应用程序将只包含一个名为locations的表，它将存储，是的，你猜对了，位置。很简单，对吧？
我们不会使用任何类型的前端管理，但想象一下这些位置可以在某种地图上表示，如下所示。
使用位置数据在地图上放置兴趣点
从最简单的角度来看，地理位置，无论类型如何，都包含基本信息，例如名称、类型、经度和纬度。但是，根据类型，每个位置可能有不同的详细信息。
同一张表中的结构化和半结构化数据
表创建
在 MariaDB 中使用 JSON 就像启用在表中存储 JSON 数据的能力一样简单。实际上，用于创建新位置表的 SQL 看起来应该很熟悉。
MariaDB SQL
1
创建 表位置（
2
id INT NOT NULL AUTO_INCREMENT ,
3
名称VARCHAR ( 100 ) NOT NULL ,
4
类型CHAR ( 1 ) NOT NULL ,
5
纬度DECIMAL ( 9 , 6 ) NOT NULL ,
6
经度DECIMAL ( 9 , 6 ) NOT NULL ,
7
属性 JSON，
8
主 键（id）
9
);
请注意，位置表中包含的attr列是使用 JSON 数据类型定义的。更具体地说，该列使用 JSON 别名数据类型。这意味着没有实际的 JSON 数据类型，而是将 JSON 指定的数据类型转换为 MariaDB 中的现有数据类型。
…没有实际的 JSON 数据类型，而是将 JSON 指定的数据类型转换为 MariaDB 中的现有数据类型。
仔细观察，我们可以使用SHOW CREATE查询来检查实际创建的内容的详细信息。
MariaDB SQL
1
显示 创建 表位置；
执行前面的语句将产生以下结果。
MariaDB SQL
1
创建 表位置（
2
id INT NOT NULL AUTO_INCREMENT ,
3
名称VARCHAR ( 100 ) NOT NULL ,
4
类型CHAR ( 1 ) NOT NULL ,
5
纬度DECIMAL ( 9 , 6 ) NOT NULL ,
6
经度DECIMAL ( 9 , 6 ) NOT NULL ,
7
attr LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
8
检查（JSON_VALID（`attr`）
9
主 键（id）
10
);
注意attr列的数据类型是LONGTEXT. 除此之外，您会看到在字符集和排序规则的字段中添加了一些约束。然而，最重要的是约束，它表示当attr中的数据被修改（通过插入或更新）CHECK时将执行的函数。
JSON_VALID ()函数是一个预定义函数，用于接收 JSON 数据（以字符串的形式）并验证它是否有效。有效意思是格式正确的 JSON 数据。
插入数据
您插入的 JSON 数据包含在引号中，就像您要插入的任何其他基于字符串的信息一样。唯一的区别是字符串必须是有效的 JSON。
MariaDB SQL
1
插入 位置（类型、名称、纬度、经度、属性）值
2
（'R'，'Lou Malnatis'，42.0021628，-87.7255662，
3
'{"details": {"foodType": "Pizza", "menu": "https://www.loumalnatis.com/our-menu"},4
"favorites": [{"description": "Pepperoni deep dish", "price": 18.75},5
{"description": "The Lou", "price": 24.75}]}' );请注意，您可以在插入到同一个表中指定具有完全不同结构的不同 JSON 数据。当然，这是有道理的，因为这就是重点！
MariaDB SQL
1
插入 位置（类型、名称、纬度、经度、属性）值
2
( 'A' , '云门' , 41.8826572 , - 87.6233039 ,
3
'{"category": "Landmark", "lastVisitDate": "11/10/2019"}' );…您可以在插入到同一个表中指定具有完全不同结构的不同 JSON 数据。
查询数据
您现在可能已经掌握了它，但在 MariaDB 中管理 JSON 数据实际上归结为使用预定义的函数。在本文的其余部分，我们将介绍一些可供您使用的功能。
读取标量数据
JSON_VALUE ()函数从指定数据中的指定路径返回一个 JSON 标量值。在下面的示例中，我使用attr列作为“指定数据”，但请注意，提供给函数的 JSON 也可以是JSON 数据的原始字符串。
MariaDB SQL
1
选择名称、纬度、经度、
2
JSON_VALUE(attr, '$.details.foodType' )作为food_type
3
从位置
4
WHERE类型 = 'R' ;
这取决于先前插入到位置表中的数据，可能会产生类似于以下的结果。
如果您想知道“如何处理空值/不存在的值？”，因为，由于半结构化的性质以及实际上的目的，这就是重点。是的，该JSON_VALUE()函数处理这个问题。
处理空值/不存在的值怎么样？…是的
注意： 整个查询然后显示表格结果类型流是我将在本文中继续讨论的内容。只是一个抬头！
您也不限于将 JSON 函数严格用作SELECT子句的一部分。您可以在过滤部分中轻松使用它们。
MariaDB SQL
1
SELECT id、姓名、纬度、经度
2
从位置
3
WHERE type = 'S' AND
4
JSON_VALUE(attr, '$.details.yearOpened' ) = 1924 ;
读取对象数据
JSON_QUERY ()函数接受 JSON 数据和 JSON 路径并返回 JSON 数据。JSON_VALUE()和之间的区别在于JSON_QUERY()返回JSON_QUERY()整个 JSON 对象数据。
MariaDB SQL
1
选择名称、描述、
2
JSON_QUERY(attr, '$.details' )作为详细信息
3
从位置
4
WHERE类型 = 'R'
该JSON_QUERY()函数还可以返回数组。
MariaDB SQL
1
选择名称、描述、
2
JSON_QUERY(attr, '$.teams' )作为home_teams
3
从位置
4
哪里类型= 'S' ;
创建索引
在这一点上，您可能想知道所有这些查询正在进行，能够创建（性能增强）索引呢？这甚至可能吗？你说得对！
这一切都从创建一个虚拟列开始。
MariaDB SQL
1
ALTER TABLE位置添加 列
2
food_type VARCHAR ( 25 ) AS (JSON_VALUE(attr, '$.details.foodType' ))虚拟;
然后，您可以使用虚拟列，结合其他虚拟或持久列来创建新索引。
MariaDB SQL
1
在位置上创建索引食物类型（食物类型）；
修改数据
如您所知，读取数据实际上只是成功的一半。要真正从能够在关系数据库中存储 JSON 数据中获得价值，您还需要能够修改或编写它。幸运的是，MariaDB 也为此提供了很多功能。
插入字段
JSON_INSERT ()函数返回通过将一个或多个路径/值对插入 JSON 数据而创建的 JSON 数据。
MariaDB SQL
1
更新位置
2
SET attr = JSON_INSERT(attr, '$.nickname' , 'The Bean' )
3
哪里id = 8 ;
插入数组
您还可以使用JSON_ARRAY()函数创建新数组。然后，在JSON_INSERT()函数中，可以将新数组插入到指定的 JSON 数据中（在本例中为attr字段）。
MariaDB SQL
1
更新位置
2
SET attr = JSON_INSERT(attr,
3
'$.foodTypes' ,
4
JSON_ARRAY( '亚洲'，'墨西哥' ))
5
哪里id = 1 ;
添加数组元素
使用JSON_ARRAY_APPEND()函数，您可以通过添加一个或多个元素来修改已经有的数组。
MariaDB SQL
1
更新位置
2
设置属性 = JSON_ARRAY_APPEND（属性，
3
'$.foodTypes' , '德语')
4
其中id = 1；
删除数组元素
JSON_REMOVE ()可用于删除由索引指定的数组元素。
MariaDB SQL
1
更新位置
2
SET attr = JSON_REMOVE(attr,
3
'$.foodTypes[2]'）
4
哪里id = 1 ;
提示： 该JSON_REMOVE()函数非常强大，可用于在从 JSON 数据中删除指定路径的任何 JSON 数据（即数组元素、对象等）后返回结果 JSON 文档。
混合数据查询
您可能希望从结构化数据创建 JSON 数据。为此，您可以使用JSON_OBJECT()函数。
MariaDB SQL
1
选择
2
JSON_OBJECT( 'name' , name, 'latitude' , latitude, 'longitude' , longitude) AS 数据
3
从位置
4
哪里类型= 'S' ;
合并数据
您可以JSON_OBJECT()使用JSON_MERGE()函数合并从函数返回的数据并将其与现有 JSON 数据合并。请注意，您可以使用该函数创建一个全新的 JSON 对象，JSON_OBJECT()然后使用该JSON_MERGE()函数将其与attr字段的值组合或合并。
MariaDB SQL
1
选择
2
JSON_MERGE(
3
JSON_OBJECT(
4
'名字' , 名字,
5
'纬度' , 纬度,
6
'经度' , 经度),
7
attr) AS 数据
8
从位置
9
WHERE类型 = 'R' ;
JSON 到表格数据
目前，在 MariaDB 10.6 中，最新版本添加了JSON_TABLE()函数。这个新功能使您能够将 JSON 数据直接转换为表格格式，甚至可以直接在FROM子句中使用它来连接其他表（或表格数据）。
MariaDB SQL
1
选择l .name，d .food_type，d .menu
2
从
3
位置AS l，
4
JSON_TABLE(l .attr ,
5
'$'列(
6
food_type VARCHAR ( 25 ) PATH '$ .foodType ',
7
菜单VARCHAR ( 200 ) PATH '$ .menu ')
8
)作为d
9
哪里id = 2 ;
提示：您可以在此处JSON_TABLE()找到有关新功能的更详细文章！
加强数据完整性
最后，我想谈谈您必须能够在 MariaDB 中存在的 JSON 数据中强制执行数据完整性的能力。用更简单的英语来说，这意味着您有能力为表中允许存在的 JSON 类型创建约束或要求。
下面是一个示例，说明如何创建一个新的CONSTRAINT，在本例中名为check_attr，它指定对于“S”类型的每个位置，其中的 JSON 数据必须符合特定标准。也就是说，您可以控制属性或值的数据类型、属性是否必须存在，甚至是指定属性中值的长度等。这一切都是使用 MariaDB 中的 JSON 函数完成的。如您所见，它非常灵活且功能强大。
MariaDB SQL
1
ALTER TABLE位置添加 约束check_attr
2
检查（
3
类型！= “S” 或（类型= “S” 和
4
JSON_TYPE(JSON_QUERY(attr, '$.details' )) = 'OBJECT' AND
5
JSON_TYPE(JSON_QUERY(attr, '$.details.events' )) = 'ARRAY' AND
6
JSON_TYPE(JSON_VALUE(attr, '$.details.yearOpened' )) = 'INTEGER' AND
7
JSON_TYPE(JSON_VALUE(attr, '$.details.capacity' )) = 'INTEGER' AND
8
JSON_EXISTS(attr, '$.details.yearOpened' ) = 1 AND
9
JSON_EXISTS(attr, '$.details.capacity' ) = 1 AND
10
JSON_LENGTH(JSON_QUERY(attr, '$.details.events' )) > 0 ));
下一步
非常感谢您阅读这篇文章，了解如何将 MariaDB 的强大功能与 JSON 的灵活性结合起来。但我们只触及了 MariaDB 中可用功能的皮毛。
目前在 MariaDB 中提供了 JSON 函数的完整列表。
最终，我们都以不同的方式学习。如果您想进一步了解可用的 JSON 功能，以及如何开始使用 MariaDB 创建混合数据模型，请查看我还整理的以下资源。
- 代码：使用 Docker 的 MariaDB JSON 快速入门（不到 2 分钟）
- 网络研讨会：混合数据模型最佳实践：JSON + 关系
- 用于跟踪不同类型位置的示例 Web 应用程序，这些应用程序利用了半结构化 JSON 数据：
了解更多
如果您想进一步了解 MariaDB 的功能，请务必查看开发人员中心和我们新的开发人员代码中心 GitHub 组织。在那里，您可以找到更多类似的内容，涵盖各种技术、用例和编程语言。
您还可以在官方文档中更深入地了解 MariaDB 的功能。
而且，与往常一样，如果没有我们很棒的社区，我们将一事无成！如果您愿意帮助贡献，您可以在GitHub 上找到我们，直接向我们发送反馈，或加入新的MariaDB 社区 Slack中的对话！
快乐编码！
