OPENJSON扩展了其他列

Question

我有一个包含如下数据的表格：

id     deltas
23     [{"prop1": "value1","prop2": "value2},{"prop1": "value3","prop2": "value4}

这里的想法是deltas包含对象数组。对象的模式总是prop1和prop2，假设这两个属性是唯一的。意味着对象只包含这两个属性。

我想要的是编写一个TSQL查询，让我得到类似于：

id   property   value
23   prop1      value1
23   prop2      value2
23   prop1      value3
23   prop2      value4

我已经可以使用openjson了，因为我将兼容性级别设置为130。 我尝试了一个简单的openjson，但它给我的是一个键值对，第一个对象的键为0，值为JSON。我想解析该值的JSON。

- dgorti

Answer 1

您可以使用 OPENJSON 从 JSON 中提取值，然后使用 UNPIVOT 将这些值重新组织成您所需的格式：

--temp table variable that holds your json
declare @source table (id int, props nvarchar(max))
--temp table variable that holds values extracted from your json
declare @tmp table (id int, prop1 nvarchar(max), prop2 nvarchar(max))

--populate temp table with the data you posted
insert into @source
select 23,'[{"prop1": "value1","prop2": "value2"},{"prop1": "value3","prop2": "value4"}]'

--use OPENJSON to extract data from "props" column holding your json
insert into @tmp
select id, prop1,prop2 from  @source  cross apply OPENJSON(props) 
with
(
    prop1 nvarchar(100),
    prop2 nvarchar(100)
)

--unpivot the result to get desired result
select id, u.property, u.value
from  @tmp t
unpivot(
    [value] for [property] in (prop1,prop2)
) u;

这里是这个 TSQL 片段的输出结果:

如果你不需要中间结果，可以避免使用临时表，在一步中处理数据。

--temp table variable that holds your json
declare @source table (id int, props nvarchar(max))

--populate temp table with the data you posted
insert into @source
select 23,'[{"prop1": "value1","prop2": "value2"},{"prop1": "value3","prop2": "value4"}]'

--use openjson to fetch data from JSON and then unpivot the result to get desired format
select id, u.property, u.value
from  (select id, prop1, prop2 from  @source  cross apply OPENJSON(props) 
with
(
    prop1 nvarchar(100),
    prop2 nvarchar(100)
)
) t
unpivot(
    [value] for [property] in (t.prop1,t.prop2)
) u;

附言：

您发布的JSON数据：

[{"prop1": "value1","prop2": "value2},{"prop1": "value3","prop2": "value4}

由于value2和value4后面缺少最终方括号和几个引号，因此不合法。您可以使用ISJSON()进行检查，它将返回0：

select isjson(' [{"prop1": "value1","prop2": "value2},{"prop1": "value3","prop2": "value4}')

我假设输入实际上是：

[{"prop1": "value1","prop2": "value2"},{"prop1": "value3","prop2": "value4"}]

- Andrea