正如我在评论中提到的,我会转而使用
WITH
子句和定义列及其数据类型。然后,您还可以使用以下命令将值分成两行。注:额外费用
OPENJSON
最后,哪一个治疗
custinfo
就像阵列一样;返回2行(数组中的每个值对应1行):
DECLARE @json NVARCHAR(MAX) ='{
"customer": [
{
"id": "123",
"history": [
{
"id": "a123",
"dates": [
{
"date": "2022-03-19",
"details": {
"custinfo": [
"male",
"married"
],
"age": 40
}}]}]}]}';
SELECT c.id AS CustId,
h.id AS CustId_Hist,
d.date AS date,
d.age AS age,
ci.[value] AS custinfo
FROM OPENJSON( @json,'$.customer')
WITH (id int,
history nvarchar(MAX) AS JSON) c
CROSS APPLY OPENJSON (c.history)
WITH (id varchar(10),
dates nvarchar(MAX) AS JSON) h
CROSS APPLY OPENJSON (h.dates)
WITH(date date,
details nvarchar(MAX) AS JSON,
age int '$.details.age') d
CROSS APPLY OPENJSON(d.details,'$.custinfo') ci;