数据库怎么解析json字符串

数据库如何解析JSON字符串：从存储到查询的全面指南

在当今数据驱动的时代，JSON（JavaScript Object Notation）因其轻量、灵活和易于阅读的特性，已成为数据交换的通用格式，数据库系统不再仅仅处理结构化的行列表数据，越来越多的场景需要直接存储、解析和查询JSON格式的半结构化数据，数据库究竟是如何解析JSON字符串的呢？本文将探讨主流数据库对JSON字符串的解析机制、常用方法及最佳实践。

为什么需要在数据库中解析JSON？

在数据库层面解析JSON字符串,主要基于以下需求：

灵活的数据建模：应对 schema-less 或 schema-flexible 的数据需求，例如用户配置、日志记录、产品属性等。
数据存储与检索：直接将JSON文档或JSON字符串存储在数据库中,并能够高效地提取其中的特定信息。
复杂查询与过滤：基于JSON内部的字段值进行条件查询、聚合分析等。
数据转换与集成：在不同系统间交换数据时,能够方便地将JSON字符串转换为数据库内部可操作的数据结构。

主流数据库的JSON解析方法

不同的数据库系统提供了不同方式和程度的JSON支持,但其核心解析思路有共通之处。

MySQL/MariaDB

MySQL 5.7+ 和 MariaDB 10.0+ 对JSON提供了原生支持。

存储：使用 JSON 数据类型专门存储JSON文档，数据库会对其进行验证,确保存储的是有效的JSON格式。

CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile JSON -- 使用JSON类型
);
INSERT INTO user_profiles (profile) VALUES ('{"name": "Alice", "age": 30, "address": {"city": "New York", "zip": "10001"}}');

解析与查询：
- -> 操作符：从JSON文档中提取指定路径的值，返回JSON格式。
```
SELECT profile -> '$.name' FROM user_profiles; -- 返回 "Alice" (带引号的字符串)
```
- ->> 操作符：从JSON文档中提取指定路径的值，返回原生类型（字符串、数字、布尔等）。
```
SELECT profile ->> '$.name' FROM user_profiles; -- 返回 Alice (不带引号)
SELECT profile ->> '$.age' FROM user_profiles;  -- 返回 30 (数字)
```
- JSON_EXTRACT() 函数：与 -> 类似，提取JSON路径的值。
```
SELECT JSON_EXTRACT(profile, '$.address.city') FROM user_profiles;
```
- JSON_UNQUOTE() 函数：去除JSON值的引号，转换为原生类型，常与 JSON_EXTRACT 结合使用。
```
SELECT JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')) FROM user_profiles;
```
- JSON_CONTAINS()：检查JSON文档是否包含指定值或路径。
- JSON_SEARCH()：在JSON文档中搜索指定值的路径。
- JSON_TABLE() (MySQL 8.0+, MariaDB 10.2.4+)：将JSON数据拆分为 relational rows，极大增强了JSON数据的查询能力,允许将JSON数组或对象中的数据转换为表格形式进行JOIN等操作。

PostgreSQL

PostgreSQL 对JSON的支持非常强大，提供了 json 和 jsonb 两种类型。

存储：
- json：存储原始的JSON文本，存储时会进行语法验证,查询时重新解析。
- jsonb (Binary JSON)：以分解的二进制格式存储，查询效率更高，支持索引，且会保留键的顺序和去除重复键。推荐使用 jsonb。
```
CREATE TABLE events (
id SERIAL PRIMARY KEY,
event_data JSONB
);
INSERT INTO events (event_data) VALUES ('{"event_type": "login", "user_id": 123, "timestamp": "2023-10-27T10:00:00Z", "metadata": {"ip": "192.168.1.1", "device": "mobile"}}');
```
解析与查询：
- -> 操作符：获取JSON对象中指定键的值（作为JSON）或JSON数组中指定索引的元素（作为JSON）。
```
SELECT event_data -> 'event_type' FROM events; -- 返回 "login"
SELECT event_data -> 'metadata' ->> 'ip' FROM events; -- 返回 "192.168.1.1"
```
- ->> 操作符：获取JSON对象中指定键的值（作为文本）或JSON数组中指定索引的元素（作为文本）。
```
SELECT event_data ->> 'event_type' FROM events; -- 返回 login
SELECT event_data ->> 'user_id' FROM events; -- 返回 123 (文本形式)
```
- #> 操作符：获取JSON路径的值（作为JSON），路径用文本数组表示。
```
SELECT event_data #> '{metadata, ip}' FROM events; -- 返回 "192.168.1.1"
```
- #>> 操作符：获取JSON路径的值（作为文本）。
```
SELECT event_data #>> '{metadata, ip}' FROM events; -- 返回 192.168.1.1
```
- jsonb_extract_path() / jsonb_extract_path_text()：函数形式，与 #> / #>> 类似。
- @> / <@：包含判断（JSONB文档是否包含另一个JSONB文档/值）。
- / ?&：键存在判断 / 多键同时存在判断。
- jsonb_to_record() / jsonb_to_recordset()：将JSONB对象或数组转换为记录行,方便与表进行JOIN。

SQL Server

SQL Server 2016+ 引入了原生JSON支持。

存储：通常使用 NVARCHAR 或 VARCHAR 类型存储JSON字符串，也可以使用 JSON 类型（SQL Server 2016+，但本质上还是存储为文本）。

CREATE TABLE product_details (
    id INT PRIMARY KEY,
    attributes NVARCHAR(MAX) -- 存储JSON字符串
);
INSERT INTO product_details (id, attributes) VALUES (1, '{"color": "red", "size": "M", "features": ["breathable", "waterproof"]}');

解析与查询：

JSON_VALUE() 函数：从JSON字符串中提取指定路径的标量值（字符串、数字、布尔、null）。

SELECT JSON_VALUE(attributes, '$.color') FROM product_details WHERE id = 1; -- 返回 "red" (NVARCHAR)
SELECT JSON_VALUE(attributes, '$.size') FROM product_details WHERE id = 1; -- 返回 "M"

JSON_QUERY() 函数：从JSON字符串中提取指定路径的JSON子对象或数组（返回JSON格式的NVARCHAR）。
```
SELECT JSON_QUERY(attributes, '$.features') FROM product_details WHERE id = 1; -- 返回 ["breathable", "waterproof"]
```

OPENJSON() 函数：这是SQL Server中处理JSON的强大工具，它将JSON对象或数组转换为表格形式。

单对象转单行：

SELECT id, JSON_VALUE(attributes, '$.color') AS color, JSON_VALUE(attributes, '$.size') AS size
FROM product_details
CROSS APPLY OPENJSON(attributes) WITH (color NVARCHAR(50) '$.color', size NVARCHAR(10) '$.size');

数组转多行：

SELECT id, value AS feature
FROM product_details
CROSS APPLY OPENJSON(attributes, '$.features') AS features;

ISJSON() 函数：验证字符串是否为有效的JSON格式。

Oracle

Oracle Database 12c Release 2 开始对JSON提供了原生支持。

存储：可以使用 JSON 数据类型，或存储在 CLOB / BLOB / VARCHAR2 列中，推荐使用 JSON 类型。

CREATE TABLE customer_orders (
    order_id NUMBER PRIMARY KEY,
    order_data JSON
);
INSERT INTO customer_orders (order_id, order_data) VALUES (1, '{"customer": "Bob", "items": [{"product_id": "P101", "quantity": 2}, {"product_id": "P102", "quantity": 1}], "total": 99.99}');