Oracle

从Oracle 19c开始，数据库提供了内置的支持来处理JSON文档，并且可以直接在表中存储JSON数据。

--假设我们有一个名为PRODUCTS的表，其中包含一个名为ATTRIBUTES的列，用于存储产品属性的JSON数据。

--创建表并定义一个JSON类型字段的表
CREATE TABLE PRODUCTS (
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(100),
    ATTRIBUTES CLOB CHECK (ATTRIBUTES IS JSON)
);

--插入两行数据
INSERT INTO PRODUCTS (ID, NAME, ATTRIBUTES) VALUES (1, 'Widget', '{"color": "blue", "size": "medium"}');
INSERT INTO PRODUCTS (ID, NAME, ATTRIBUTES) VALUES (2, 'Gadget', '{"color": "red", "size": "small"}');

--解析数据
SELECT ID, NAME, 
       JSON_VALUE(ATTRIBUTES, '$.color') AS COLOR, 
       JSON_VALUE(ATTRIBUTES, '$.size') AS sizes
FROM PRODUCTS;

MySQL

MySQL从5.7.8版本开始引入了对JSON数据类型的支持，并提供了一系列的函数来处理JSON数据。

--创建表并定义一个JSON类型字段的表
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON
);

--插入两行数据
INSERT INTO products (name, attributes) VALUES ('Widget', '{"color": "blue", "size": "medium"}');
INSERT INTO products (name, attributes) VALUES ('Gadget', '{"color": "red", "size": "small"}');

--解析数据
SELECT id, name, 
       JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color')) AS color, 
       JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.size')) AS size
FROM   products;

SQLServer

SQLSERVER从2016版本开始支持OPENJSON函数。

--创建表并定义一个nvarchar(max)类型字段的表
CREATE TABLE Products (
    ProductId int PRIMARY KEY,
    Name nvarchar(50),
    Attributes nvarchar(max)
);

--插入两行数据
INSERT INTO Products (ProductId, Name, Attributes) VALUES (1, 'Widget', N'{"Color": "Blue", "Size": "Medium"}');
INSERT INTO Products (ProductId, Name, Attributes) VALUES (2, 'Gadget', N'{"Color": "Red", "Size": "Small"}');

-- 提取 JSON 字段中的单个值
SELECT ProductId, Name
, JSON_VALUE(Attributes, '$.Color') AS Color
, JSON_VALUE(Attributes, '$.Size') AS Size
FROM Products;

-- 解析 JSON 字符串并展开为行
SELECT *
FROM Products
CROSS APPLY OPENJSON(Attributes)
WITH (Color nvarchar(50) '$.Color',
      Size nvarchar(50) '$.Size');

PostgreSQL

PostgreSQL 从9.0版本开始支持 JSON 类型，从9.2版本开始支持 JSONB 类型。JSON写性能强，JSONB读性能强。

--创建表并定义一个JSONB类型字段的表
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    attributes JSONB
);

--插入两行数据
INSERT INTO products (name, attributes) VALUES ('Widget', '{"Color": "Blue", "Size": "Medium"}');
INSERT INTO products (name, attributes) VALUES ('Gadget', '{"Color": "Red", "Size": "Small"}');

-- 提取 JSON 字段中的单个值
SELECT product_id, name
, (attributes->>'Color') AS color
, (attributes->>'Size') AS Sizes
FROM products;