JSON解析与处理

随着JSON在数据交换中的广泛应用，JSON解析函数成为现代SQL的必备技能。

📊 支持情况

1. JSON基础结构

{
  "user_id": 1001,
  "name": "张三",
  "email": "zhangsan@email.com",
  "phones": ["13812345678", "13987654321"],
  "address": {
    "city": "北京",
    "district": "朝阳区"
  },
  "orders": [
    {"order_id": 1, "amount": 500},
    {"order_id": 2, "amount": 800}
  ]
}

2. MySQL JSON函数

JSON_EXTRACT 提取值

-- 提取标量值
SELECT JSON_EXTRACT('{"name": "张三", "age": 25}', '$.name');  -- "张三"
SELECT JSON_EXTRACT('{"name": "张三", "age": 25}', '$.age');   -- 25

-- 提取嵌套对象
SELECT JSON_EXTRACT('{"address": {"city": "北京"}}', '$.address.city');  -- "北京"

-- 提取数组元素
SELECT JSON_EXTRACT('{"phones": ["138", "139"]}', '$.phones[0]');  -- "138"
SELECT JSON_EXTRACT('{"phones": ["138", "139"]}', '$.phones[1]');  -- "139"

-- 提取所有数组元素
SELECT JSON_EXTRACT('{"phones": ["138", "139"]}', '$.phones[*]');  -- ["138","139"]

-- 条件提取
SELECT JSON_EXTRACT('{"items": [1, 2, 3]}', '$.items[1]');  -- 2

JSON_VALUE 提取标量

-- 直接返回标量值（不是JSON字符串）
SELECT JSON_VALUE('{"name": "张三"}', '$.name');  -- 张三（字符串）
SELECT JSON_VALUE('{"age": 25}', '$.age');        -- 25（数字）

JSON_QUERY 查询JSON片段

-- 返回JSON对象/数组
SELECT JSON_QUERY('{"address": {"city": "北京"}}', '$.address');  -- {"city": "北京"}

JSON_CONTAINS 检查包含

-- 检查JSON是否包含值
SELECT JSON_CONTAINS('["a", "b", "c"]', '"b"');  -- 1

-- 检查对象是否包含键
SELECT JSON_CONTAINS_KEY('{"name": "张三"}', 'name');  -- 1

JSON_LENGTH 长度

-- JSON数组长度
SELECT JSON_LENGTH('["a", "b", "c"]');  -- 3

-- JSON对象键值对数量
SELECT JSON_LENGTH('{"a": 1, "b": 2}');  -- 2

JSON_UNQUOTE 去引号

-- 去掉JSON字符串的引号
SELECT JSON_UNQUOTE(JSON_EXTRACT('{"name": "张三"}', '$.name'));  -- 张三

组合使用

SELECT
    data,
    JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) AS name,
    JSON_EXTRACT(data, '$.age') AS age,
    JSON_EXTRACT(data, '$.address.city') AS city,
    JSON_LENGTH(JSON_EXTRACT(data, '$.phones')) AS phone_count
FROM users;

3. Oracle JSON函数

JSON_VALUE

-- 提取标量值
SELECT JSON_VALUE('{"name": "张三", "age": 25}', '$.name') FROM dual;  -- 张三
SELECT JSON_VALUE('{"age": 25}', '$.age') FROM dual;                  -- 25

JSON_QUERY

-- 提取JSON片段
SELECT JSON_QUERY('{"address": {"city": "北京"}}', '$.address') FROM dual;
-- {"city":"北京"}

JSON_TABLE 转为表

SELECT *
FROM JSON_TABLE(
    '[{"id": 1, "name": "a"}, {"id": 2, "name": "b"}]',
    '$[*]'
    COLUMNS (
        id INT PATH '$.id',
        name VARCHAR2(100) PATH '$.name'
    )
);

JSON_EXISTS

-- 检查路径是否存在
SELECT JSON_EXISTS('{"name": "张三"}', '$.age') FROM dual;  -- FALSE (0)
SELECT JSON_EXISTS('{"name": "张三"}', '$.name') FROM dual; -- TRUE (1)

4. ClickHouse JSON函数

JSON_VALUE

SELECT JSON_VALUE('{"name": "张三"}', '$.name');  -- 张三

visitParamExtract 系列

-- 提取不同类型
SELECT visitParamExtractString('{"name": "张三"}', 'name');  -- 张三
SELECT visitParamExtractInt('{"age": 25}', 'age');          -- 25
SELECT visitParamExtractFloat('{"price": 99.9}', 'price');  -- 99.9
SELECT visitParamExtractBool('{"flag": true}', 'flag');     -- 1

JSONExtract

SELECT JSONExtract('{"name": "张三", "age": 25}', 'Tuple(name String, age Int)');  -- ('张三',25)
SELECT JSONExtractArray('{"ids": [1, 2, 3]}', '$.ids');     -- [1,2,3]

5. Hive JSON函数

get_json_object

-- 提取单个值
SELECT get_json_object('{"name": "张三"}', '$.name');              -- 张三
SELECT get_json_object('{"address": {"city": "北京"}}', '$.address.city');  -- 北京

-- 提取数组
SELECT get_json_object('{"phones": ["138", "139"]}', '$.phones[0]');  -- 138

-- 提取所有数组
SELECT get_json_object('{"phones": ["138", "139"]}', '$.phones.*');   -- ["138","139"]

JSON_TUPLE

-- 一次提取多个值
SELECT
    json_tuple('{"name": "张三", "age": 25}', 'name', 'age') AS (name, age);

6. MaxCompute JSON函数

GET_JSON_OBJECT

SELECT GET_JSON_OBJECT('{"name": "张三"}', '$.name');  -- 张三

JSON_EXTRACT（MaxCompute 2.0+）

SELECT JSON_EXTRACT('{"items": [1, 2, 3]}', '$.items[0]');  -- 1

7. 实战案例

场景1：解析用户信息表

-- MySQL
SELECT
    id,
    JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.name')) AS name,
    JSON_EXTRACT(user_data, '$.age') AS age,
    JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.address.city')) AS city,
    JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.phones[0]')) AS primary_phone
FROM users
WHERE JSON_EXTRACT(user_data, '$.age') >= 18;

场景2：解析订单商品列表

-- MySQL
SELECT
    order_id,
    JSON_UNQUOTE(JSON_EXTRACT(items, '$[0].product_name')) AS first_product,
    JSON_LENGTH(items) AS item_count,
    SUM(JSON_EXTRACT(items, '$[*].quantity')) AS total_quantity
FROM orders,
     JSON_TABLE(
         items,
         '$[*]'
         COLUMNS (
             product_name VARCHAR(100) PATH '$.product_name',
             quantity INT PATH '$.quantity',
             price DECIMAL(10,2) PATH '$.price'
         )
     ) AS jt
GROUP BY order_id, items;

场景3：统计JSON数组

-- MySQL
SELECT
    user_id,
    JSON_LENGTH(orders) AS order_count,
    SUM(JSON_EXTRACT(o, '$.amount')) AS total_amount
FROM users,
     JSON_TABLE(
         orders,
         '$[*]'
         COLUMNS (amount DECIMAL(10,2) PATH '$.amount')
     ) AS jt
GROUP BY user_id, orders;

场景4：生成JSON

-- MySQL JSON_OBJECT
SELECT JSON_OBJECT(
    'id', id,
    'name', name,
    'email', email
) AS user_json
FROM users;

-- Oracle JSON_OBJECT
SELECT JSON_OBJECT(
    'id' VALUE id,
    'name' VALUE name
) AS user_json
FROM users;

8. JSON路径表达式

路径语法

过滤示例

-- MySQL - 不支持复杂过滤，需用JSON_TABLE
-- Oracle - 支持JSON_VALUE中使用过滤
SELECT value
FROM JSON_TABLE(
    '[{"price": 50}, {"price": 150}, {"price": 100}]',
    '$[*]'
    COLUMNS (
        price INT PATH '$.price'
    )
) WHERE price > 100;

⚠️ 注意事项

1. 性能问题

-- ❌ 避免：对大JSON字段建立索引前先解析
SELECT * FROM users WHERE JSON_EXTRACT(data, '$.name') = '张三';

-- ✅ 建议：使用Generated Column建立索引（MySQL）
ALTER TABLE users
ADD name VARCHAR(100)
AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))),
ADD INDEX idx_name (name);

2. NULL处理

-- JSON中的null vs SQL的NULL
SELECT JSON_EXTRACT('{"a": null}', '$.a');  -- null（JSON null）
SELECT JSON_VALUE('{"a": null}', '$.a');    -- NULL（SQL NULL）

3. 引号问题

-- JSON_EXTRACT返回的是带引号的JSON字符串
SELECT JSON_EXTRACT('{"name": "张三"}', '$.name');  -- "张三"

-- 需要用JSON_UNQUOTE去掉引号
SELECT JSON_UNQUOTE(JSON_EXTRACT('{"name": "张三"}', '$.name'));  -- 张三

-- 或使用JSON_VALUE（直接返回字符串）
SELECT JSON_VALUE('{"name": "张三"}', '$.name');  -- 张三

📝 练习题

-- 建表
CREATE TABLE api_logs (
    id INT,
    request_body STRING
);

INSERT INTO api_logs VALUES
(1, '{"method": "GET", "path": "/api/users", "status": 200, "response_time": 45}'),
(2, '{"method": "POST", "path": "/api/orders", "status": 201, "response_time": 120}'),
(3, '{"method": "GET", "path": "/api/products", "status": 200, "response_time": 35}'),
(4, '{"method": "DELETE", "path": "/api/users/123", "status": 204, "response_time": 80}');

要求：

1. 提取所有请求方法和路径
2. 筛选response_time > 100的记录
3. 统计每种方法的平均响应时间

-- MySQL参考答案
-- 1. 提取方法和路径
SELECT
    id,
    JSON_UNQUOTE(JSON_EXTRACT(request_body, '$.method')) AS method,
    JSON_UNQUOTE(JSON_EXTRACT(request_body, '$.path')) AS path
FROM api_logs;

-- 2. 筛选响应时间
SELECT *
FROM api_logs
WHERE JSON_EXTRACT(request_body, '$.response_time') > 100;

-- 3. 统计平均响应时间
SELECT
    JSON_UNQUOTE(JSON_EXTRACT(request_body, '$.method')) AS method,
    AVG(JSON_EXTRACT(request_body, '$.response_time')) AS avg_response_time
FROM api_logs
GROUP BY method;