MSSQL 2017解析动态JSON数组：提取多人员姓名组件

结论 #

可以同时解析两种personType，无需分别处理，通过嵌套OPENJSON和CROSS APPLY就能统一完成解析逻辑。

示例SQL代码 #

假设你的JSON结构如下：

• 单人（personType=NATURAL）：{"personType":"NATURAL", "terms":[{"termType":"title","string":"Mr"},{"termType":"givenname","string":"John"},{"termType":"familyname","string":"Doe"}]}
• 多人（personType=MULTIPLE）：{"personType":"MULTIPLE", "persons":[{"terms":[{"termType":"givenname","string":"Jane"},{"termType":"familyname","string":"Smith"}]},{"terms":[{"termType":"title","string":"Ms"},{"termType":"givenname","string":"Alice"},{"termType":"familyname","string":"Brown"}]}]}

对应的解析SQL：

SELECT
    t.ID,
    t.fullName,
    COALESCE(p.person_seq, 1) AS 人员序号,
    tr.termType,
    tr.string
FROM test_data t
-- 解析JSON根节点，提取核心字段
CROSS APPLY OPENJSON(t.jsonString)
WITH (
    personType VARCHAR(20) '$.personType',
    terms NVARCHAR(MAX) '$.terms' AS JSON, -- 单人的姓名组件数组
    persons NVARCHAR(MAX) '$.persons' AS JSON -- 多人的人员列表数组
) j
-- 处理多人场景：拆分人员列表，生成人员序号
OUTER APPLY (
    SELECT 
        CAST([key] AS INT) + 1 AS person_seq, -- 数组索引从0开始，+1转为自然序号
        terms AS person_terms
    FROM OPENJSON(j.persons)
    WITH (
        terms NVARCHAR(MAX) '$.terms' AS JSON -- 提取每个人员的姓名组件数组
    )
) p
-- 统一解析姓名组件数组：单人用根节点的terms，多人用每个人员的terms
CROSS APPLY OPENJSON(COALESCE(p.person_terms, j.terms))
WITH (
    termType VARCHAR(50) '$.termType',
    string NVARCHAR(100) '$.string'
) tr

逻辑说明 #

1. 先通过CROSS APPLY OPENJSON解析JSON根节点，区分出单人/多人的核心数据结构。
2. 用OUTER APPLY处理多人场景的persons数组，拆分出每个人员的姓名组件数组，并生成人员序号。
3. 最后通过COALESCE统一指定要解析的terms数组路径，不管是单人还是多人，都用同一个OPENJSON步骤提取termType和string字段。

如果你的JSON结构和示例略有差异（比如多人的姓名组件路径不同），只需调整OPENJSON中的JSON路径即可，核心思路是通过嵌套APPLY将两种结构的terms数组归一到同一解析流程中，无需拆分处理。

内容的提问来源于stack exchange，提问作者M4tee