我使用的是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各做一遍?
1条答案
按热度按时间3qpi33ja1#
好了,我找到了一个解决方案,虽然它可能不是最优雅的一个。但是,这需要三个通道,一个用于personType NATURAL,一个用于personType MULTIPLE中的每个人:
任何简化和优化都是受欢迎的。