SQL JSON处理指南 各数据库JSON函数用法对比

关系型数据库处理json数据是为了兼顾结构化与半结构化数据的灵活性，适用于快速迭代和部分字段频繁变更的场景。1. 不同数据库创建json的方式不同：postgresql推荐使用jsonb类型，支持高效存储和索引；mysql通过json_object和json_array构建；sql server以nvarchar(max)模拟json；oracle则在varchar2或clob中存储并支持json构建函数。2. json查询方面：postgresql使用->和->>操作符提取键值；mysql提供json_extract和简写符号；sql server依赖json_value和json_query；oracle支持json_value、json_query及点表示法。3. 修改json数据时：postgresql有jsonb_set、jsonb_insert等函数；mysql提供json_set、json_insert等；sql server使用json_modify；oracle则通过json_transform实现。4. 数组操作上：postgresql支持jsonb_array_elements等函数展开和计算长度；mysql有json_array_append等；sql server通过json_modify处理数组元素；oracle使用json_array_append等方法。此外，为提升效率，应考虑对json字段建立索引、精确路径选择、避免不必要的类型转换、聚合json数据以及合理控制json的使用范围，避免将核心业务数据过度放入json列中。

在关系型数据库中处理JSON数据，本质上是在传统结构化世界与灵活半结构化世界之间搭建一座桥梁。它让我们可以将一些不那么固定、或者说未来可能频繁变化的字段，以JSON的形式存储在一个列里，而不用频繁地修改表结构。这在应对快速迭代的业务需求，或是处理来自API、日志等非结构化数据源时，显得尤为实用。当然，这并不是说JSON能替代所有传统列，它更多是一种补充，一种在特定场景下能大幅提升开发效率和数据模型灵活性的工具。但不同的数据库在实现这些功能时，语法和理念上确实存在不小的差异，这常常让开发者在跨数据库迁移或学习时感到头疼。

解决方案

要有效地处理SQL中的JSON数据，关键在于理解不同数据库提供的核心函数集，并掌握其操作JSON的路径语法。我们将重点关注JSON数据的创建、查询（提取）、修改与删除，以及数组操作。

1. JSON数据的创建与构建

• PostgreSQL: PostgreSQL拥有原生的JSON和JSONB类型，推荐使用JSONB因为它存储效率更高，并且支持索引。

  -- 从文本创建JSON
  SELECT '{"name": "Alice", "age": 30}'::jsonb;
  -- 从键值对构建JSON对象
  SELECT jsonb_build_object('name', 'Bob', 'age', 25);
  -- 从值构建JSON数组
  SELECT jsonb_build_array('apple', 'banana', 'orange');

  登录后复制
• MySQL: MySQL 5.7+ 引入了原生的JSON数据类型。

  -- 从键值对构建JSON对象
  SELECT JSON_OBJECT('name', 'Charlie', 'age', 35);
  -- 从值构建JSON数组
  SELECT JSON_ARRAY('red', 'green', 'blue');

  登录后复制
• SQL Server: SQL Server 2016+ 支持JSON字符串处理，但没有原生JSON类型，通常以NVARCHAR(MAX)存储。

  -- 从查询结果构建JSON对象或数组
  SELECT name, age FROM users FOR JSON PATH; -- 生成JSON数组
  SELECT name, age FROM users FOR JSON PATH, WITHOUT_ARRAY_WRAPPER; -- 生成单个JSON对象

  登录后复制
• Oracle: Oracle 12cR2+ 引入了JSON支持，通常存储在VARCHAR2或CLOB中。

  -- 从键值对构建JSON对象
  SELECT JSON_OBJECT('name' VALUE 'David', 'age' VALUE 40) FROM DUAL;
  -- 从值构建JSON数组
  SELECT JSON_ARRAY('car', 'bike', 'plane') FROM DUAL;

  登录后复制

2. JSON数据的查询与提取

• PostgreSQL: 使用->和->>操作符，前者返回JSONB类型，后者返回文本。

  SELECT data->'name' AS json_name, data->>'age' AS text_age
  FROM my_table WHERE data->>'city' = 'New York';
  -- 嵌套路径
  SELECT data->'address'->>'street' FROM my_table;

  登录后复制
• MySQL: 使用JSON_EXTRACT()函数或其简写->操作符。

  SELECT JSON_EXTRACT(json_column, '$.name') AS json_name,
         json_column->'$.age' AS text_age
  FROM my_table WHERE JSON_EXTRACT(json_column, '$.city') = 'London';
  -- 嵌套路径
  SELECT json_column->'$.address.street' FROM my_table;

  登录后复制
• SQL Server: 使用JSON_VALUE()提取标量值，JSON_QUERY()提取对象或数组。

  SELECT JSON_VALUE(json_column, '$.name') AS text_name,
         JSON_VALUE(json_column, '$.age') AS text_age
  FROM my_table WHERE JSON_VALUE(json_column, '$.city') = 'Paris';
  -- 嵌套路径
  SELECT JSON_VALUE(json_column, '$.address.street') FROM my_table;

  登录后复制
• Oracle: 使用JSON_VALUE()、JSON_QUERY()或点表示法。

  SELECT JSON_VALUE(json_column, '$.name') AS text_name,
         json_column.age AS text_age -- Oracle特有的点表示法
  FROM my_table WHERE JSON_VALUE(json_column, '$.city') = 'Berlin';
  -- 嵌套路径
  SELECT json_column.address.street FROM my_table;

  登录后复制

3. JSON数据的修改与更新

• PostgreSQL: 使用jsonb_set()、jsonb_insert()等函数。

  UPDATE my_table
  SET data = jsonb_set(data, '{age}', '31', false) -- 修改age，如果不存在则不创建
  WHERE id = 1;
  UPDATE my_table
  SET data = jsonb_insert(data, '{new_key}', '"new_value"', true) -- 插入new_key
  WHERE id = 1;

  登录后复制
• MySQL: 使用JSON_SET()、JSON_INSERT()、JSON_REPLACE()。

  UPDATE my_table
  SET json_column = JSON_SET(json_column, '$.age', 31) -- 修改age，如果不存在则创建
  WHERE id = 1;
  UPDATE my_table
  SET json_column = JSON_INSERT(json_column, '$.new_key', 'new_value') -- 插入new_key，如果已存在则忽略
  WHERE id = 1;

  登录后复制
• SQL Server: 使用JSON_MODIFY()。

  UPDATE my_table
  SET json_column = JSON_MODIFY(json_column, '$.age', 31) -- 修改age
  WHERE id = 1;
  UPDATE my_table
  SET json_column = JSON_MODIFY(json_column, '$.new_key', 'new_value') -- 添加new_key
  WHERE id = 1;

  登录后复制
• Oracle: 使用JSON_TRANSFORM()。

  UPDATE my_table
  SET json_column = JSON_TRANSFORM(json_column, SET '$.age' = 31)
  WHERE id = 1;
  UPDATE my_table
  SET json_column = JSON_TRANSFORM(json_column, INSERT '$.new_key' 'new_value')
  WHERE id = 1;

  登录后复制

4. JSON数组操作

• PostgreSQL:jsonb_array_elements()展开数组，jsonb_array_length()获取长度。

  SELECT jsonb_array_elements(data->'items') FROM my_table;
  SELECT jsonb_array_length(data->'items') FROM my_table;

  登录后复制
• MySQL:JSON_ARRAY_APPEND()、JSON_ARRAY_INSERT()、JSON_LENGTH()。

  SELECT JSON_ARRAY_APPEND(json_column, '$.tags', 'new_tag') FROM my_table;
  SELECT JSON_LENGTH(json_column, '$.items') FROM my_table;

  登录后复制
• SQL Server:JSON_MODIFY()用于添加/删除数组元素。

  UPDATE my_table
  SET json_column = JSON_MODIFY(json_column, 'append $.tags', 'new_tag')
  WHERE id = 1;

  登录后复制
• Oracle:JSON_ARRAY_APPEND()、JSON_ARRAY_INSERT()。

  SELECT JSON_ARRAY_APPEND(json_column, '$.tags', 'new_tag') FROM DUAL;

  登录后复制

为什么要在关系型数据库里处理JSON数据？

这问题问得好，因为我以前也常琢磨，既然要用JSON，为啥不直接上NoSQL数据库呢？后来我发现，这其实是个“取舍”的问题。核心原因在于，很多时候我们的数据并非纯粹的无模式，而是“大部分结构化，小部分半结构化”。

想想看，一个电商订单，订单ID、用户ID、总金额、创建时间这些是高度结构化的，你几乎不会改动它们的类型或格式。但订单里的“商品列表”或者“附加备注”，可能就没那么固定了。商品列表可能包含各种自定义属性，比如“尺码”、“颜色”、“材质”，甚至不同品类的商品，这些属性完全不一样。如果为每个可能的属性都创建一个列，那表结构会变得异常庞大且稀疏，维护起来简直是噩梦。

这时候，把这些可变的部分塞进一个JSON列里，就显得非常优雅。它保留了关系型数据库 ACID 特性、事务管理、强大的查询优化器等核心优势，同时又获得了NoSQL的灵活性。你可以快速迭代产品，增加新的商品属性，而无需修改数据库表结构，甚至不需要停机。对我来说，这是一种“两全其美”的策略，尤其适用于那些既需要强一致性又需要一定数据模型灵活性的业务场景。当然，前提是你得知道什么时候用，什么时候不用，别把所有东西都扔进JSON，那就失去关系型数据库的意义了。

不同数据库JSON函数的核心差异在哪里？

要说核心差异，我觉得主要体现在以下几个方面：

首先是原生支持程度和数据类型。PostgreSQL的JSONB类型无疑是这方面的佼佼者，它将JSON数据以二进制格式存储，支持索引，查询效率高，并且提供了非常丰富的操作符（如->、->>）和函数。MySQL也有JSON类型，但其内部实现和优化与PostgreSQL略有不同。而SQL Server和Oracle在较早的版本中，更多是将JSON视为字符串进行处理，虽然现在也提供了丰富的JSON函数，但底层存储和某些操作的效率可能不如原生类型那么直接和高效。这种差异直接影响了我们编写查询时的语法简洁性和执行效率。

其次是路径表达式的语法。虽然都支持类似XPath的路径表达式，但细节上各有千秋。MySQL和SQL Server通常使用$.key、$.array[index]这样的标准JSON路径语法，这比较直观。PostgreSQL则更倾向于使用其特有的操作符和函数，比如data->'key'或data->'array'->>0。Oracle则在标准函数之外，还提供了类似对象属性访问的点表示法（json_column.key），这让习惯了面向对象编程的开发者感到亲切。理解这些细微的差别，是避免“语法陷阱”的关键。

再者是错误处理和空值行为。当JSON路径指向的键不存在时，不同数据库的处理方式可能不同。有的会返回NULL，有的可能会抛出错误，或者需要你显式地指定ON ERROR或ON EMPTY子句。例如，SQL Server的JSON_VALUE函数就允许你定义当路径不存在或类型不匹配时的行为。这种差异在编写健壮的SQL查询时尤其重要，你得清楚你的查询在面对不完整或异常JSON数据时会如何表现。

最后，我觉得是对JSON Schema等高级特性的支持。虽然不是所有数据库都提供，但PostgreSQL和Oracle在某些方面对JSON Schema的验证有更深的支持，这对于确保JSON数据的质量和一致性非常有帮助。MySQL和SQL Server可能更多地依赖于应用层面的验证。这些差异决定了你在数据库层面能做多少数据治理工作，以及在面对复杂JSON结构时，是依赖数据库的约束还是完全交给应用层。

Find JSON Path Online

Easily find JSON paths within JSON objects using our intuitive Json Path Finder

30

查看详情

总的来说，虽然目标一致，但各家数据库在实现路径、效率和功能深度上都有自己的特色。这要求我们在选择和使用时，不能简单地“一刀切”，而是要根据具体的业务场景和对性能、灵活性的要求，做出最合适的选择。

进阶操作：JSON数据的高效查询与修改策略

处理JSON数据，尤其是当它们变得庞大或查询频繁时，光知道函数用法还不够，还得考虑效率。在我看来，有几个进阶策略特别值得关注：

1. 索引JSON字段

这是提升JSON查询性能的“杀手锏”。PostgreSQL的JSONB类型可以创建GIN索引，这对于在JSON内部的键值对进行高效搜索至关重要。你可以为JSONB列中的特定路径创建表达式索引，例如：

-- 为JSONB列中某个特定键创建索引
CREATE INDEX idx_my_table_data_status ON my_table ((data->>'status'));
-- 为JSONB列中包含特定键的所有文档创建GIN索引
CREATE INDEX idx_my_table_data_gin ON my_table USING GIN (data);

MySQL也支持在JSON列上创建函数索引，例如：

CREATE INDEX idx_my_table_json_status ON my_table ((CAST(json_column->'$.status' AS CHAR(50))));

SQL Server虽然没有原生JSON类型，但你可以在计算列上创建索引，这个计算列提取JSON中的特定值。Oracle同样支持在JSON路径上创建函数索引。

2. 路径选择与数据类型转换的考量

在查询时，尽量精确地指定JSON路径，避免全表扫描或不必要的解析。同时，注意数据类型转换。例如，如果你知道data->>'age'是一个数字，在比较时最好将其转换为数字类型，而不是作为字符串比较，因为字符串比较可能导致意想不到的结果（比如“10”小于“2”）。

-- PostgreSQL: 显式转换类型以进行数值比较
SELECT * FROM my_table WHERE (data->>'age')::int > 30;
-- MySQL: 确保比较类型一致
SELECT * FROM my_table WHERE CAST(json_column->'$.age' AS UNSIGNED) > 30;

3. 聚合JSON数据

很多时候，我们需要将多行数据聚合成一个JSON对象或数组，这在生成API响应或报表时非常有用。

• PostgreSQL:jsonb_agg()和jsonb_object_agg()

  SELECT jsonb_agg(data) FROM my_table; -- 将多行JSONB聚合成一个JSONB数组
  SELECT jsonb_object_agg(id, data) FROM my_table; -- 将id作为键，data作为值聚合成一个JSONB对象

  登录后复制
• MySQL:JSON_ARRAYAGG()和JSON_OBJECTAGG()

  SELECT JSON_ARRAYAGG(json_column) FROM my_table;
  SELECT JSON_OBJECTAGG(id, json_column) FROM my_table;

  登录后复制
• SQL Server:FOR JSON PATH或FOR JSON AUTO

  SELECT id, name, json_column FROM my_table FOR JSON PATH;

  登录后复制
• Oracle:JSON_ARRAYAGG()和JSON_OBJECTAGG()

  SELECT JSON_ARRAYAGG(json_column) FROM my_table;
  SELECT JSON_OBJECTAGG(id, json_column) FROM my_table;

  登录后复制

4. 避免过度使用JSON

虽然JSON很灵活，但并不是所有数据都适合放在JSON列里。那些需要频繁查询、排序、聚合，或者需要建立外键关系的核心业务数据，仍然应该放在独立的、强类型的列中。JSON更适合那些半结构化、非核心、或可能频繁变化的辅助信息。过度依赖JSON可能导致查询复杂性增加，索引效率下降，甚至影响数据完整性。在我的经验里，这就像一把双刃剑，用得好能事半功倍，用不好则可能挖坑。

总而言之，在SQL中处理JSON数据，既要享受其带来的灵活性，也要警惕潜在的性能和维护挑战。深入理解各数据库的实现细节，并结合实际业务场景进行优化，才是王道。

以上就是SQL JSON处理指南 各数据库JSON函数用法对比的详细内容，更多请关注php中文网其它相关文章！