如何在SQL中处理JSON数据？解析与查询JSON的步骤

在SQL中处理JSON数据需选择支持JSON的数据库系统，利用其提供的JSON函数进行解析、查询、更新操作，并根据需求权衡JSON与关系型数据的优劣。 以下是具体步骤和示例：

不同数据库对JSON的支持程度不同，需根据完备性、性能和易用性选择：

• MySQL 5.7+：支持JSON类型及基础函数（如JSON_EXTRACT）。
• PostgreSQL 9.3+：提供JSON和JSONB类型，后者支持索引和高效查询。
• SQL Server 2016+：支持JSON_VALUE、OPENJSON等函数。
• Oracle 12c+：提供JSON_VALUE、JSON_TABLE等函数。

使用数据库提供的JSON函数将字符串转化为可操作结构：

• MySQL示例：SELECT JSON_EXTRACT(details, '$.name') AS product_name, JSON_UNQUOTE(JSON_EXTRACT(details, '$.price')) AS product_priceFROM products;-- 简写形式（MySQL 5.7.9+）SELECT details->>'$.name' AS product_name, details->>'$.price' AS product_priceFROM products;
• PostgreSQL示例：SELECT details->>'name' AS product_name, details->>'price' AS product_priceFROM products;
• SQL Server示例：SELECT JSON_VALUE(details, '$.name') AS product_name, JSON_VALUE(details, '$.price') AS product_priceFROM products;
• Oracle示例：SELECT JSON_VALUE(details, '$.name') AS product_name, JSON_VALUE(details, '$.price') AS product_priceFROM products;

结合WHERE子句和JSON函数进行条件查询，注意类型转换：

• MySQL示例：SELECT * FROM products WHERE JSON_EXTRACT(details, '$.price') > 100;-- 或简写形式SELECT * FROM products WHERE details->>'$.price' > 100;
• PostgreSQL示例（需显式类型转换）：SELECT * FROM products WHERE (details->>'price')::numeric > 100;
• SQL Server/Oracle示例：SELECT * FROM products WHERE JSON_VALUE(details, '$.price') > 100;

使用JSON函数修改嵌套字段值：

• MySQL示例：UPDATE products SET details = JSON_REPLACE(details, '$.price', 120) WHERE id = 1;
• PostgreSQL示例（使用JSONB类型）：UPDATE products SET details = jsonb_set(details, '{price}', '120') WHERE id = 1;
• SQL Server示例：UPDATE products SET details = JSON_MODIFY(details, '$.price', 120) WHERE id = 1;
• Oracle示例：UPDATE products SET details = JSON_MERGEPATCH(details, '{"price": 120}') WHERE id = 1;

• 索引创建：

  PostgreSQL：为JSONB列创建GIN索引。CREATE INDEX idx_details ON products USING gin (details);

  MySQL：在虚拟列上创建索引。ALTER TABLE products ADD COLUMN price DECIMAL(10,2) GENERATED ALWAYS AS (details->>'$.price') STORED,ADD INDEX idx_price (price);

使用路径表达式或嵌套函数访问深层字段：

• 示例：提取address对象中的city字段。

  MySQL：SELECT JSON_EXTRACT(details, '$.address.city') AS city FROM products;

  PostgreSQL：SELECT details->'address'->>'city' AS city FROM products;

  SQL Server/Oracle：SELECT JSON_VALUE(details, '$.address.city') AS city FROM products;

• JSON优势：

  灵活存储半结构化数据，适应频繁变更的schema。

  减少表数量，简化复杂嵌套结构的建模。

• 关系型数据优势：

  严格的数据类型和约束保证一致性。

  查询性能更高，尤其适合结构化数据。

• 建议：

  结构化数据优先使用关系型设计。

  半结构化数据（如日志、配置）可使用JSON。

  混合场景可将JSON作为列嵌入关系型表中。

• JSON vs JSONB：

  JSON：存储原始字符串，写入快但查询慢，保留空格和重复键。

  JSONB：存储二进制格式，写入慢但查询快，支持索引和自动优化。

  推荐：默认使用JSONB，除非需保留原始格式。

通过以上步骤，可高效地在SQL中处理JSON数据，同时根据实际需求平衡灵活性与性能。