为了适应技术发展的需求,SQL 标准于 2016 年增加了以下 JSON 功能:
JSON 对象的存储与检索。
将 JSON 对象表示成 SQL 数据。
将 SQL 数据表示成 JSON 对象。
如今,主流关系型数据库都增加了原生 JSON 数据类型和相关函数的支持,使得我们可以将 SQL 的强大功能与 JSON 文档存储的灵活性相结合。当我们需要为应用程序增加文档存储功能时,可以考虑直接在现有的关系型数据库中使用 JSON 数据类型。
以下是一个使用 JSON 字段存储员工信息的示例:
Oracle 21c
CREATE TABLE employee_json(
emp_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
emp_info JSON NOT NULL
);
MySQL
CREATE TABLE employee_json(
emp_id INTEGER AUTO_INCREMENT PRIMARY KEY,
emp_info JSON NOT NULL
);
Microsoft SQL Server
CREATE TABLE employee_json(
emp_id INTEGER IDENTITY PRIMARY KEY,
emp_info VARCHAR(MAX) NOT NULL CHECK ( ISJSON(emp_info)>0 )
);
PostgreSQL
CREATE TABLE employee_json(
emp_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
emp_info JSONB NOT NULL
);
SQLite
CREATE TABLE employee_json(
emp_id INTEGER PRIMARY KEY,
emp_info TEXT NOT NULL CHECK ( JSON_VALID(emp_info)=1 )
);
我们可以使用 INSERT 语句将文本数据插入 JSON 字段:
INSERT INTO employee_json(emp_info)
VALUES ('{"emp_name": "刘备", "sex": "男", "dept_id": 1, "manager": null, "hire_date": "2000-01-01", "job_id": 1, "income": [{"salary":30000}, {"bonus": 10000}], "email": "[email protected]"}');
其中,income节点是一个数组,包含了salary和bonus两个对象。
使用 SQL 语句查询 JSON 字段的方式与普通字段相同,SQL 标准使用 JSON_VALUE 函数查询 JSON 元素的值,使用 JSON_QUERY 函数查询元素中的对象和数组。
例如,以下语句从 emp_info 字段中获取员工的姓名和月薪:
Oracle和Microsoft SQL Server
SELECT emp_id,
JSON_VALUE(emp_info, '$.emp_name') emp_name,
JSON_VALUE(emp_info, '$.income[0].salary') salary,
JSON_VALUE(JSON_QUERY(emp_info, '$.income[0]'),'$.salary') salary
FROM employee_json
WHERE JSON_VALUE(emp_info, '$.emp_name') = '刘备';
emp_id|emp_name|salary|salary
------|--------|------|------
1|刘备 |30000 |30000
MySQL和SQLite
SELECT emp_id,
JSON_EXTRACT(emp_info, '$.emp_name') emp_name,
JSON_EXTRACT(emp_info, '$.income[0].salary') salary
FROM employee_json
WHERE JSON_EXTRACT(emp_info, '$.emp_name') = '刘备';
# MySQL
emp_id|emp_name|salary
------|--------|------
1|"刘备" |30000
# SQLite
emp_id|emp_name|salary
------|--------|------
1|刘备 | 30000
PostgreSQL
SELECT emp_id,
JSONB_EXTRACT_PATH_TEXT(emp_info, 'emp_name') emp_name,
JSONB_EXTRACT_PATH_TEXT(emp_info, 'income', '0', 'salary') salary
FROM employee_json
WHERE JSONB_EXTRACT_PATH_TEXT(emp_info, 'emp_name') = '刘备';
emp_id|emp_name|salary
------|--------|------
1|刘备 |30000
SQL 标准还定义了各种操作 JSON 数据的函数,具体可以参考特定数据库的实现。
2019 年 9 月 17 图形查询语言(GQL)成为了继 SQL 之后另一种新的 ISO 标准数据库查询语言。
同时,最新的 SQL:2023 中增加的一个全新部分:Property Graph Queries (SQL/PGQ)。这个新功能支持使用图数据库的方式查询表中的数据。