技术 | 提取JSON在不同数据库中处理语法的差异

CREATE TABLE users(
  id INT PRIMARY KEY,
  info JSON  -- 或类似类型（如 JSONB、NVARCHAR）
);

INSERT INTO users(id, info) VALUES 
(1, '{"name": "Alice", "age": 30}'),
(2, '{"name": "Bob", "age": 25}');

SELECT id, JSON_EXTRACT(info, '$.name') AS name FROM users;
-- 简写
SELECT id, info->>'$.name' AS name FROM users;

SELECT id, info->'name' AS name_json, info->>'name' AS name_text FROM users;

SELECT id, JSON_VALUE(info, '$.name') AS name FROM users;

SELECT info->>'$.user.address.city' AS city FROM users;

SELECT info->'user'->'address'->>'city' AS city FROM users;

SELECT JSON_VALUE(info, '$.user.address.city') AS city FROM users;