T-SQL解析动态JSON数组 _大数据知识库

我使用的是MSSQL 2017，并将名称解析服务的JSON输出存储在一个表中：

CREATE TABLE test_data (
   ID int,
   fullName nvarchar(50),
   jsonString nvarchar(max)
)
INSERT INTO test_data
   (ID, fullName, jsonString)
VALUES
   (1, N'Dr. Damian Summer', N'{"parsedPerson": {"personType": "NATURAL","personRole": "PRIMARY","mailingPersonRoles": ["ADDRESSEE"],"gender": {"gender": "MALE","confidence": 0.8199955555555556},"addressingGivenName": "Damian","addressingSurname": "Summer","outputPersonName": {"terms": [{"string": "Dr.","termType": "TITLE"},{"string": "Damian","termType": "GIVENNAME"},{"string": "Summer","termType": "SURNAME"}]}},"parserDisputes": [],"likeliness": 0.9480247866561989,"confidence": 0.9451463934269034}'),
   (2, N'Rick Da Costa + M. Tosh', N'{"parsedPerson":{"personType":"MULTIPLE","personRole":"PRIMARY","mailingPersonRoles":["ADDRESSEE"],"people":[{"personType":"NATURAL","personRole":"PRIMARY","mailingPersonRoles":["ADDRESSEE"],"gender":{"gender":"MALE","confidence":1.0},"addressingGivenName":"Rick","addressingSurname":"Da Costa","outputPersonName":{"terms":[{"string":"Rick","termType":"GIVENNAME"},{"string":"Da Costa","termType":"SURNAME"}]}},{"personType":"NATURAL","personRole":"PRIMARY","mailingPersonRoles":["ADDRESSEE"],"gender":{"gender": "UNKNOWN","confidence": 0.6400000000000001},"addressingSurname":"Tosh","outputPersonName":{"terms":[{"string": "M.","termType":"GIVENNAMEINITIAL"},{"string":"Tosh","termType":"SURNAME"}]}}]},"parserDisputes":[],"likeliness": 0.6503418684197658,"confidence": 0.8653094883324904}'),
   (3, N'Cygan Marianne + Edgar', N'{"parsedPerson":{"personType":"MULTIPLE","personRole":"PRIMARY","mailingPersonRoles":["ADDRESSEE"],"people":[{"personType":"NATURAL","personRole":"MEMBER","mailingPersonRoles":["MEMBER"],"gender":{"gender":"FEMALE","confidence":0.94667621434538729},"addressingGivenName":"Marianne","addressingSurname":"Cygan","outputPersonName":{"terms":[{"string":"Cygan","termType":"SURNAME"},{"string":"Marianne","termType":"GIVENNAME"}]}},{"personType":"NATURAL","personRole":"MEMBER","mailingPersonRoles":["MEMBER"],"gender":{"gender":"MALE","confidence":0.95406313536650611},"addressingGivenName":"Edgar","addressingSurname":"Cygan","outputPersonName":{"terms":[{"string":"Cygan","termType":"SURNAME"},{"string":"Edgar","termType":"GIVENNAME"}]}}]},"parserDisputes":[],"likeliness":0.8933697196920577,"confidence":0.96231315577078291}')

一条记录可以包含一个人（personType = NATURAL）或多个人（personType = MULTIPLE），对于找到的每个人，姓名被拆分为多个组成部分（title、givenname、surname ...），这些组成部分保存在“terms”数组中。
结果应如下所示：

-----------------------------------------------------------------------------------------
ID  fullName            person          termType        string      
-----------------------------------------------------------------------------------------
1   Dr. Damian Summer                   TITLE           Dr.
1   Dr. Damian Summer                   GIVENNAME       Damian
1   Dr. Damian Summer                   SURNAME         Summer
2   Rick Da Costa + M. Tosh     1       GIVENNAME       Rick
2   Rick Da Costa + M. Tosh     1       SURNAME         Da Costa
2   Rick Da Costa + M. Tosh     2       GIVENNAMEINITIAL    M.
2   Rick Da Costa + M. Tosh     2       SURNAME         Tosh
3   Cygan Marianne + Edgar      1       GIVENNAME       Marianne
3   Cygan Marianne + Edgar      1       SURNAME         Cygan
3   Cygan Marianne + Edgar      2       GIVENNAME       Edgar
3   Cygan Marianne + Edgar      2       SURNAME         Cygan

如何获取“termType”和“string”的所有值以及人员编号？我尝试过使用OPENJSON和交叉应用，但没有成功。
有没有办法同时解析两个personType？或者我必须对NATURAL和MULTIPLE各做一遍？

好了，我找到了一个解决方案，虽然它可能不是最优雅的一个。但是，这需要三个通道，一个用于personType NATURAL，一个用于personType MULTIPLE中的每个人：

--NATURAL 
    SELECT d.ID,
       d.fullName,
       j2.termType,
       j2.string
    FROM test_data d
    CROSS APPLY OPENJSON(d.jsonString, '$.parsedPerson.outputPersonName.terms') j1
    CROSS APPLY
    OPENJSON(j1.Value, '$')
    WITH
    (
        string NVARCHAR(50) '$.string',
        termType NVARCHAR(20) '$.termType'
    ) j2;

    --MULTIPLE Person 1
    SELECT d.ID,
       d.fullName,
       j2.termType,
       j2.string
    FROM test_data d
    CROSS APPLY OPENJSON(d.jsonString, '$.parsedPerson.people[0].outputPersonName.terms') j1
    CROSS APPLY
    OPENJSON(j1.Value, '$')
    WITH
    (
        string NVARCHAR(50) '$.string',
        termType NVARCHAR(20) '$.termType'
    ) j2;

--MULTIPLE Person 2
    SELECT d.ID,
       d.fullName,
       j2.termType,
       j2.string
    FROM test_data d
    CROSS APPLY OPENJSON(d.jsonString, '$.parsedPerson.people[1].outputPersonName.terms') j1
    CROSS APPLY
    OPENJSON(j1.Value, '$')
    WITH
    (
        string NVARCHAR(50) '$.string',
        termType NVARCHAR(20) '$.termType'
    ) j2;

任何简化和优化都是受欢迎的。

T-SQL解析动态JSON数组

1条答案

相关问题

热门标签

最新问答