提问人：Suraj 提问时间：11/2/2023 最后编辑：marc_sSuraj 更新时间：11/3/2023 访问量：81
将嵌套的 JSON 规范化为多个表 [重复]
Normalize nested JSON into multiple tables [duplicate]
问：
这个问题在这里已经有答案了：
具有嵌套属性的 JSON 到 SQL 表 （1 个答案）
将嵌套的 JSON 解析到 Azure SQL 表 （1 个答案）
如何将此 JSON 导入 2 个单独的表？ （2 个答案）
20天前关闭。
我有一个表，其中每行都包含嵌套的 JSON。我想将此JSON规范化为包含数据的多个表。
例如，下面是 1 行的 JSON。
{
"Arn":"UNLQ5HFPVXMX7",
"Merchant":"UNL",
"Aun":"aad9561d-17bc-4280-a44f-7fd4cdb2c57a",
"ExternalArn":"b7e6d3da-4b63-44a8-adfc-884cce34288d",
"Status":"Commenced",
"PageIdentifier":"ValidationResult",
"ProductRate":{
"RollRate":false,
"IsFromRate":false
},
"Contacts":[
{
"Contact":{
"FirstName":"Lotsa",
"Surname":"Deductibles",
"Pun":"b5c648c5-0573-46b0-bc9d-4c4e24cbb1f1",
"Dependants":2,
"Emails":[
{
"EmailAddress":"1d507ac2-0631-4d3d-906b-5e473b71cedc",
"Type":"PERSONAL",
"Id":85885,
"CreatedOn":"2023-01-02T22:48:53.127529+00:00",
"CreatedBy":"[email protected]",
"LastUpdatedOn":"2023-01-02T22:48:53.1275294+00:00",
"LastUpdatedBy":"[email protected]",
"RowVersion":"AAAAABhpD4k="
}
],
"PhoneNumbers":[
{
"Type":"MOB",
"PhoneNumber":"+61403917776"
}
],
"TrackingIdentifiers":[
],
"Id":89684,
"CreatedOn":"2023-01-02T22:48:53.078947+00:00",
"CreatedBy":"[email protected]",
"LastUpdatedOn":"2023-01-02T22:48:53.0789472+00:00",
"LastUpdatedBy":"[email protected]",
"RowVersion":"AAAAABhpD4g="
},
"Dependants":2,
"MaritalStatus":"MARRIED",
"Type":"PA",
"ApplicationDocumentIds":[
],
"DocumentProvider":"EXT",
"Assets":[
{
"Type":"CASH"
}
],
"Expenses":[
{
}
],
"Incomes":[
{
"Type":"SAL",
"Total":100000.0
}
],
"Employment":[
{
"BusinessName":"Nicks Bank",
"Type":"CURRENT",
"EmploymentType":"UN",
"SalaryFrequency":"ANNUAL"
}
],
"PropertyAssets":[
{
"RentalIncome":1500.0,
"MonthlyRepayment":2000.0,
"OutstandingBalance":400000.0,
"Usage":"IL",
"Address":{
"CombinedAddress":"82 Hay St, HAYMARKET, NSW 2000"
},
"OwnershipPercentage":1.0
},
{
"RentalIncome":2000.0,
"MonthlyRepayment":2400.0,
"OutstandingBalance":500000.0,
"Usage":"IL",
"Address":{
"CombinedAddress":"U 1 1 Malthouse Lane, Melbourne VIC 3000"
},
"OwnershipPercentage":0.5
}
],
"Liabilities":[
{
"Type":"CCL"
},
{
"Type":"LCB"
},
{
"Type":"OLB"
},
{
"Type":"OLR"
}
],
"HasHecsDebt":true,
"ResidentialStatus":"RENT",
"Id":120028,
"CreatedOn":"2023-01-02T22:48:53.1723177+00:00",
"CreatedBy":"[email protected]",
"LastUpdatedOn":"2023-01-02T22:48:53.1723178+00:00",
"LastUpdatedBy":"[email protected]",
"RowVersion":"AAAAABhpD5g="
},
{
"Contact":{
"FirstName":"Fewa",
"Surname":"Deductibles",
"Pun":"e0f64678-b116-4354-9c6b-70a31d966bf3",
"Dependants":0,
"Type":"OPPORTUNITY",
"Emails":[
{
"EmailAddress":"abcde-fghijk",
"Type":"PERSONAL",
"Id":85886,
"CreatedOn":"2023-01-02T22:48:54.5431097+00:00",
"CreatedBy":"[email protected]",
"LastUpdatedOn":"2023-01-02T22:48:54.54311+00:00",
"LastUpdatedBy":"[email protected]",
"RowVersion":"AAAAABhpD5M="
}
],
"PhoneNumbers":[
{
"Type":"MOB",
"PhoneNumber":"+12345678",
"Id":87908,
"CreatedOn":"2023-01-02T22:48:54.547373+00:00",
"CreatedBy":"[email protected]",
"LastUpdatedOn":"2023-01-02T22:48:54.5473733+00:00",
"LastUpdatedBy":"[email protected]",
"RowVersion":"AAAAABhpD5Q="
}
],
"TrackingIdentifiers":[
],
"Id":89685,
"CreatedOn":"2023-01-02T22:48:54.5362658+00:00",
"CreatedBy":"[email protected]",
"LastUpdatedOn":"2023-01-02T22:48:54.536266+00:00",
"LastUpdatedBy":"[email protected]",
"RowVersion":"AAAAABhpD5I="
},
"Dependants":2,
"MaritalStatus":"MARRIED",
"Type":"SA",
"ApplicationDocumentIds":[
],
"DocumentProvider":"EXT",
"Expenses":[
{
"Total":0.0
}
],
"Incomes":[
{
"Type":"SAL",
"Total":90000.0
}
],
"Employment":[
{
"BusinessName":"Nicks Bank",
"Type":"CURRENT",
"EmploymentType":"UN",
"SalaryFrequency":"ANNUAL"
}
],
"PropertyAssets":[
{
"RentalIncome":2000.0,
"MonthlyRepayment":2400.0,
"OutstandingBalance":500000.0,
"Usage":"IL",
"Address":{
"CombinedAddress":"U 1 1 Malthouse Lane, Melbourne VIC 3000"
},
"OwnershipPercentage":0.5
}
],
"Liabilities":[
{
"Type":"CCL"
},
{
"Type":"LCB"
},
{
"Type":"OLB"
},
{
"Type":"OLR"
}
],
"HasHecsDebt":false,
"ResidentialStatus":"RENT",
"DriversLicense":{
},
"Id":120029,
"CreatedOn":"2023-01-02T22:48:54.5511511+00:00",
"CreatedBy":"[email protected]",
"LastUpdatedOn":"2023-01-02T22:48:54.5511512+00:00",
"LastUpdatedBy":"[email protected]",
"RowVersion":"AAAAABhpD5o="
}
],
"FinancialValidationProvider":"EXT",
"IsFirstHomeBuyer":false,
"IsConditionalLoan":false,
"IsAvmUsable":false,
"IsPcUsable":false,
"IsEvrUsable":false,
"SelfEmployedLoan":false,
"BusinessLoan":false,
"Id":230417,
"CreatedOn":"2023-01-02T22:48:53.0229128+00:00",
"CreatedBy":"[email protected]",
"LastUpdatedOn":"2023-01-02T22:48:54.5795498+00:00",
"LastUpdatedBy":"[email protected]",
"RowVersion":"AAAAABhpD6A="
}
例如，我想为联系人创建一个新表，如下所示，并转换下表中的JSON数据
create table contact
(
id int,
FirstName nvarchar(255),
MiddleName nvarchar(255),
Surname nvarchar(255),
Pun nvarchar(255),
Gender nvarchar(255),
DateOfBirth date
)
我尝试使用以下查询解析JSON
WITH JSONRoot AS
(
SELECT
Id as RowId,
CAST(hierarchyid::GetRoot().ToString() + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NVARCHAR(4000)) + '/' AS NVARCHAR(4000)) as [HierarchyId],
[key],
[value],
CAST([type] AS INT) AS [type]
FROM
dbo.app2023
CROSS APPLY OPENJSON(payload,'$') where [key] = 'Contacts'
UNION ALL
SELECT
RowId,
CAST(JSONRoot.[HierarchyId] + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NVARCHAR(4000)) + '/' AS NVARCHAR(4000)),
CASE WHEN JSONRoot.[type] = 4 THEN JSONRoot.[key]+'['+t.[key]+']' ELSE t.[key] END,
t.[value],
CAST(t.[type] AS INT)
FROM
JSONRoot
CROSS APPLY OPENJSON(JSONRoot.[value],'$') t
WHERE [JSONRoot].[key] like 'Contact%' and
JSONRoot.[type] > 3 /* Only parse complex data types */
)
SELECT
RowId,
--CAST([HierarchyId] AS HierarchyId) AS [HierarchyId],
[key],
[value]
--,[type]
INTO
ParseContact2023
FROM
JSONRoot
ORDER BY
RowId,
[HierarchyId]
这有助于我将 JSON 解析为键和值对，但此查询的问题在于在透视时。对于每个 rowid，它有多个联系人，因此在透视期间，每个联系人都会更改顺序。
答：
1赞
tinazmu
11/2/2023
#1
假设您的表有一个 id，并且 JSON 文本位于名为 ;您可以使用表值函数来解析它，但是捕获/保留联系人的顺序有点困难，如下所示（我没有包括您的所有列）：jsondatOPENJSON
select T.id
, TJSONContact.*
from YourTable T
CROSS APPLY OPENJSON (T.jsondat)
with (
Contacts nvarchar(max) as JSON
) TJSON
CROSS APPLY OPENJSON (TJSON.Contacts, '$.Contact')
WITH ( FirstName nvarchar(60)
, Surname nvarchar(60)
, Dependants integer
) as TJSONContact;
如果在不使用架构的情况下使用并提供数组，则该函数将返回一个列，您可以使用该列来保留联系人的顺序。OPENJSONWITHkey
例如，这里有一组列。TJSON[key], value
select T.id
, TJSONContact.*
from YourTable T
CROSS APPLY OPENJSON (T.jsondat, '$.Contacts') TJSON
CROSS APPLY OPENJSON (TJSON.value, '$.Contact')
WITH ( FirstName nvarchar(60)
, Surname nvarchar(60)
, Dependants integer
) as TJSONContact;
评论
0赞
tinazmu
11/3/2023
@Charlieface感谢您的建议;我认为 OP 可能想结合 TJSON 级别的一些属性。我同意这个想法，但我失去了测试环境;随意添加您的答案（或编辑此答案）。
上一个：规范化值太小或溢出
下一个：序列到序列预测转换器：需要归一化
评论
key