sql怎么取json串

SQL中如何高效提取JSON串中的数据

在现代数据库应用中,JSON已成为跨平台数据交换的主流格式，越来越多的数据库系统（如MySQL、PostgreSQL、SQL Server、Oracle等）直接支持JSON数据的存储与查询，如何在SQL中高效提取JSON串中的特定数据，成为开发人员必备的技能，本文将以主流数据库为例，详细介绍JSON数据的提取方法，包括基础语法、常用函数及实战技巧。

JSON数据提取的核心思路

JSON（JavaScript Object Notation）本质上是键值对的集合，常见结构包括对象（{"key": "value"}）和数组（[{"key": "value"}, {"key": "value2"}]），SQL提取JSON数据的核心思路是：通过“路径”定位目标键或索引，再通过函数解析并返回对应值，不同数据库的JSON函数语法略有差异，但逻辑高度一致。

主流数据库的JSON提取方法

MySQL：JSON函数家族

MySQL 5.7+原生支持JSON，提供了丰富的JSON函数，最常用的是JSON_EXTRACT()和->/->>操作符。

（1）基础提取：JSON_EXTRACT()与操作符

• JSON_EXTRACT(json_doc, path)：从JSON文档中提取指定路径的值，返回JSON格式结果（如字符串、数字、数组等）。
• ->：JSON_EXTRACT()的简写，返回JSON格式结果。
• ->>：JSON_UNQUOTE()与JSON_EXTRACT()的组合，返回非JSON格式的原始值（如去除字符串的引号）。

示例：假设有一张用户表user，字段info存储JSON数据：

{"name": "张三", "age": 25, "contacts": {"phone": "13800138000", "email": "zhangsan@example.com"}, "hobbies": ["reading", "swimming"]}

提取姓名（字符串值，需去引号）：

SELECT info->>'$.name' AS name FROM user;  -- 返回：张三

提取年龄（数字值，->和->>结果相同）：

SELECT info->>'$.age' AS age FROM user;    -- 返回：25

提取手机号（嵌套对象）：

SELECT info->'$.contacts.phone' AS phone FROM user;  -- 返回："13800138000"（带引号）
SELECT info->>'$.contacts.phone' AS phone FROM user;  -- 返回：13800138000（去引号）

（2）数组提取：JSON_EXTRACT()与索引

JSON数组通过索引（从0开始）访问，如提取第二个爱好：

SELECT info->'$.hobbies[1]' AS hobby FROM user;  -- 返回："swimming"
SELECT info->>'$.hobbies[1]' AS hobby FROM user;  -- 返回：swimming

（3）高级技巧：JSON_TABLE()（MySQL 8.0+）

若需将JSON数据拆分为多行或多列,可使用JSON_TABLE()，实现“JSON转关系表”：

SELECT 
    name,
    phone,
    email,
    hobby
FROM user,
JSON_TABLE(
    info,
    '$.hobbies[*]' COLUMNS(
        hobby VARCHAR(50) PATH '$'
    )
) AS ht,
JSON_TABLE(
    info,
    '$.contacts' COLUMNS(
        phone VARCHAR(20) PATH '$.phone',
        email VARCHAR(50) PATH '$.email'
    )
) AS ct;

结果：
| name | phone | email | hobby | |------|-------------|----------------------|-----------| | 张三 | 13800138000 | zhangsan@example.com | reading | | 张三 | 13800138000 | zhangsan@example.com | swimming |

PostgreSQL：#>与#>>操作符

PostgreSQL对JSON的支持更为原生,通过#>和#>>操作符提取数据，语法与MySQL类似，但路径符号略有不同（PostgreSQL使用和[]）。

（1）基础提取

假设表user的info字段同上：
提取姓名：

SELECT info#>'{name}' AS name FROM user;  -- 返回："张三"（JSONB格式）
SELECT info#>>'{name}' AS name FROM user;  -- 返回：张三（文本格式）

提取嵌套手机号：

SELECT info#>>'{contacts,phone}' AS phone FROM user;  -- 返回：13800138000

（2）数组提取

提取第二个爱好：

SELECT info#>>'{hobbies,1}' AS hobby FROM user;  -- 返回：swimming

（3）高级技巧：jsonb_to_recordset()（PostgreSQL特有）

若需将JSON数组转为多行,可使用jsonb_to_recordset()：
假设user表的hobbies字段存储JSON数组：["reading", "swimming"]，拆分为多行：

SELECT * FROM jsonb_to_recordset('["reading", "swimming"]'::jsonb) AS t(hobby text);

结果：
| hobby | |-----------| | reading | | swimming |

SQL Server：JSON_VALUE()与JSON_QUERY()

SQL Server 2016+开始支持JSON，通过JSON_VALUE()（提取单值）和JSON_QUERY()（提取对象/数组）区分。

（1）JSON_VALUE()：提取标量值（字符串、数字、布尔值）

-- 提取姓名
SELECT JSON_VALUE(info, '$.name') AS name FROM user;  -- 返回：张三（去引号）
-- 提取年龄
SELECT JSON_VALUE(info, '$.age') AS age FROM user;    -- 返回：25

（2）JSON_QUERY()：提取复杂值（对象、数组）

-- 提取联系方式（对象）
SELECT JSON_QUERY(info, '$.contacts') AS contacts FROM user;  
-- 返回：{"phone": "13800138000", "email": "zhangsan@example.com"}
-- 提取爱好（数组）
SELECT JSON_QUERY(info, '$.hobbies') AS hobbies FROM user;  
-- 返回：["reading", "swimming"]

（3）注意事项

• 若目标路径是标量值却用JSON_QUERY()，或复杂值用JSON_VALUE()，将返回NULL。
• SQL Server还支持OPENJSON()将JSON转为表，类似MySQL的JSON_TABLE()：

  SELECT * FROM OPENJSON(info, '$.hobbies') WITH (hobby nvarchar(50) '$');

  结果：
  | key | value | type | hobby | |-----|-------|------|-----------| | 0 | reading | 1 | reading | | 1 | swimming | 1 | swimming |

Oracle：JSON_VALUE()与JSON_QUERY()

Oracle 12c+支持JSON，语法与SQL Server类似，但路径表达式需使用单引号，且可通过RETURNING子句指定返回类型。

（1）JSON_VALUE()：提取标量值

-- 提取姓名（返回VARCHAR2）
SELECT JSON_VALUE(info, '$.name') RETURNING VARCHAR2(50) AS name FROM user;  -- 返回：张三
-- 提取年龄（返回NUMBER）
SELECT JSON_VALUE(info, '$.age') RETURNING NUMBER AS age FROM user;          -- 返回：25

（2）JSON_QUERY()：提取复杂值

-- 提取联系方式（返回JSON对象）
SELECT JSON_QUERY(info, '$.contacts') RETURNING JSON AS contacts FROM user;
-- 提取爱好（返回JSON数组）
SELECT JSON_QUERY(info, '$.hobbies') RETURNING JSON AS hobbies FROM user;

（3）高级技巧：JSON_TABLE()（Oracle 12c+）

SELECT 
    jt.name,
    jt.phone,
    jt.email,
    ht.hobby
FROM user,
JSON_TABLE(
    info,
    '$.hobbies[*]' COLUMNS(
        hobby VARCHAR2(50) PATH '$'
    )
) AS ht,
JSON_TABLE(
    info,
    '$.contacts' COLUMNS(
        phone VARCHAR2(20) PATH '$.phone',
        email VARCHAR2(50) PATH '$.email'
    )
) AS jt;

通用技巧与注意事项

路径表达式规范

• 对象键名：用$.key或$."key-with-hyphen"（键名含特殊字符时用双引号包裹）。
• 数组索引：用$[0]、$[1]，MySQL/PostgreSQL支持[*]表示所有元素。
• 嵌