SQL Server中OPENJSON + WITH 解析JSON数据的示例

OPENJSON 与 WITH 子句解析 JSON 数据的详细示例：

1. 基础示例 - 简单对象解析

-- 示例 JSON
DECLARE @json NVARCHAR(MAX) = N'{
    "id": 1,
    "name": "张三",
    "age": 30,
    "active": true
}';

-- 使用 OPENJSON + WITH 解析
SELECT *
FROM OPENJSON(@json)
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name',
    age INT '$.age',
    active BIT '$.active'
);

2. 嵌套对象解析

DECLARE @json NVARCHAR(MAX) = N'{
    "userId": 100,
    "userInfo": {
        "firstName": "李",
        "lastName": "四",
        "email": "lisi@example.com"
    },
    "registrationDate": "2024-01-15"
}';

-- 解析嵌套对象
SELECT *
FROM OPENJSON(@json)
WITH (
    userId INT '$.userId',
    firstName NVARCHAR(50) '$.userInfo.firstName',
    lastName NVARCHAR(50) '$.userInfo.lastName',
    email NVARCHAR(100) '$.userInfo.email',
    regDate DATE '$.registrationDate'
);

3. 数组解析

3.1 基本数组

DECLARE @json NVARCHAR(MAX) = N'[
    {"id": 1, "product": "苹果", "price": 5.5},
    {"id": 2, "product": "香蕉", "price": 3.2},
    {"id": 3, "product": "橙子", "price": 4.8}
]';

-- 解析 JSON 数组
SELECT *
FROM OPENJSON(@json)
WITH (
    id INT '$.id',
    product NVARCHAR(50) '$.product',
    price DECIMAL(10,2) '$.price'
);

3.2 嵌套数组

DECLARE @json NVARCHAR(MAX) = N'{
    "orderId": "ORD001",
    "items": [
        {"productId": 101, "quantity": 2, "unitPrice": 25.50},
        {"productId": 205, "quantity": 1, "unitPrice": 120.00},
        {"productId": 309, "quantity": 3, "unitPrice": 15.75}
    ]
}';

-- 使用 CROSS APPLY 解析嵌套数组
SELECT 
    o.orderId,
    i.*
FROM OPENJSON(@json)
WITH (
    orderId NVARCHAR(20) '$.orderId',
    items NVARCHAR(MAX) '$.items' AS JSON
) AS o
CROSS APPLY OPENJSON(o.items)
WITH (
    productId INT '$.productId',
    quantity INT '$.quantity',
    unitPrice DECIMAL(10,2) '$.unitPrice'
) AS i;

4. 复杂结构解析

DECLARE @json NVARCHAR(MAX) = N'{
    "department": "技术部",
    "employees": [
        {
            "empId": 1001,
            "name": "王五",
            "skills": ["C#", "SQL", "JavaScript"],
            "projects": [
                {"name": "项目A", "role": "开发"},
                {"name": "项目B", "role": "测试"}
            ]
        },
        {
            "empId": 1002,
            "name": "赵六",
            "skills": ["Python", "Java"],
            "projects": [
                {"name": "项目C", "role": "架构师"}
            ]
        }
    ]
}';

-- 多级解析
SELECT 
    d.department,
    e.empId,
    e.name,
    s.skill,
    p.projectName,
    p.role
FROM OPENJSON(@json)
WITH (
    department NVARCHAR(50) '$.department',
    employees NVARCHAR(MAX) '$.employees' AS JSON
) AS d
CROSS APPLY OPENJSON(d.employees)
WITH (
    empId INT '$.empId',
    name NVARCHAR(50) '$.name',
    skills NVARCHAR(MAX) '$.skills' AS JSON,
    projects NVARCHAR(MAX) '$.projects' AS JSON
) AS e
CROSS APPLY OPENJSON(e.skills) s
CROSS APPLY OPENJSON(e.projects)
WITH (
    projectName NVARCHAR(100) '$.name',
    role NVARCHAR(50) '$.role'
) p;

5. 实际应用示例

5.1 从表中解析 JSON 列

-- 创建测试表
CREATE TABLE Orders (
    OrderId INT PRIMARY KEY,
    OrderData NVARCHAR(MAX)
);

-- 插入测试数据
INSERT INTO Orders VALUES
(1, '{"orderNo":"2024001","customer":"张三","totalAmount":1500.50}'),
(2, '{"orderNo":"2024002","customer":"李四","totalAmount":2300.00}');

-- 查询并解析 JSON 列
SELECT 
    o.OrderId,
    j.orderNo,
    j.customer,
    j.totalAmount
FROM Orders o
CROSS APPLY OPENJSON(o.OrderData)
WITH (
    orderNo NVARCHAR(20) '$.orderNo',
    customer NVARCHAR(50) '$.customer',
    totalAmount DECIMAL(10,2) '$.totalAmount'
) AS j;

5.2 处理可能缺失的字段

DECLARE @json NVARCHAR(MAX) = N'{
    "id": 1,
    "name": "测试产品",
    "price": 99.9
    -- 注意：description 字段缺失
}';

-- 使用宽松路径模式（SQL Server 2016+）
SELECT *
FROM OPENJSON(@json)
WITH (
    id INT,
    name NVARCHAR(100),
    price DECIMAL(10,2),
    description NVARCHAR(500) '$.description'  -- 缺失字段返回 NULL
);

6. 高级技巧

6.1 动态列名

DECLARE @json NVARCHAR(MAX) = N'{
    "metadata": {
        "createdBy": "admin",
        "createdAt": "2024-01-20T10:30:00"
    },
    "data": {
        "column1": "值1",
        "column2": 100,
        "column3": true
    }
}';

-- 解析动态属性
SELECT *
FROM OPENJSON(@json, '$.data');

6.2 路径表达式示例

DECLARE @json NVARCHAR(MAX) = N'{
    "store": {
        "books": [
            {"title": "SQL 指南", "author": "作者A"},
            {"title": "JSON 手册", "author": "作者B"}
        ],
        "location": "北京"
    }
}';

-- 不同路径表达式
SELECT *
FROM OPENJSON(@json, '$.store.books[0]');  -- 第一本书

SELECT *
FROM OPENJSON(@json, '$.store.books')  -- 所有书
WITH (
    title NVARCHAR(100) '$.title',
    author NVARCHAR(50) '$.author'
);