交叉应用OPENJSON / PIVOT -顺序错误

Question

下面的代码打开一个json内容并在最后对其进行透视。不知怎么的，排序和旋转混淆了。

WITH request
as
(
SELECT requestId,
property1191,
'['+replace(replace(property1191, '[', ''), ']', '')+']' as json
from capex_management_requests
)
SELECT *
FROM
(
SELECT
P.requestId,
AttsData.[Id],
AttsData.[data],
ROW_NUMBER() OVER (PARTITION BY P.requestId, AttsData.[Id]
ORDER BY (SELECT 1) ) AS row_id
FROM request P
CROSS APPLY OPENJSON (P.json, N'$') 
WITH 
(
Id VARCHAR(200) N'$.metaId',
data VARCHAR(200) N'$.data'
) AS AttsData
) DS
PIVOT 
(
MAX(data) FOR Id IN ([690], [1192])
) piv;

JSON示例

{"metaId":690,"data":"1"},{"metaId":1192,"data":"4352"}],[{"metaId":690,"data":"2"},{"metaId":1192,"data":"3887"}],[{"metaId":690,"data":"3"},{"metaId":1192,"data":"4372"}],[{"metaId":690,"data":"4"},{"metaId":1192,"data":"3749"}],[{"metaId":690,"data":"51"},{"metaId":1192,"data":"3693"}],[{"metaId":690,"data":"51"},{"metaId":1192,"data":"3712"}],[{"metaId":690,"data":"89"},{"metaId":1192,"data":"4228"}

当前结果-顺序错误(例如:690:1应该显示1192:4352)

<表类>requestIdrow_id6901192tbody><<tr>1714228162371215336931443749125138871351437211894352

不像其他答案那样使用JSON_VALUE，您可以继续使用模式下的OPENJSON。你只需要两个OPENJSON调用，一次没有模式来获取带有索引的数组值，一次有模式来打破它。

WITH request
as
(
SELECT requestId,
property1191,
'['+replace(replace(property1191, '[', ''), ']', '')+']' as json
from capex_management_requests
)
SELECT *
FROM
(
SELECT
P.requestId,
AttsData.[Id],
AttsData.[data],
ROW_NUMBER() OVER (PARTITION BY P.requestId, AttsData.[Id]
ORDER BY CAST(arr.[key] AS int)) AS row_id
FROM request P
CROSS APPLY OPENJSON (P.json) AS arr
CROSS APPLY OPENJSON (arr.value)
WITH 
(
Id VARCHAR(200) N'$.metaId',
data VARCHAR(200)
) AS AttsData
) DS
PIVOT 
(
MAX(data) FOR Id IN ([690], [1192])
) piv;

排序是混乱的，因为你使用(SELECT NULL)。为了得到正确的排序，你需要使用"edge"。OPENJSON的版本，像这样:

declare @json nvarchar(max) = N'{"metaId":690,"data":"1"},{"metaId":1192,"data":"4352"}],[{"metaId":690,"data":"2"},{"metaId":1192,"data":"3887"}],[{"metaId":690,"data":"3"},{"metaId":1192,"data":"4372"}],[{"metaId":690,"data":"4"},{"metaId":1192,"data":"3749"}],[{"metaId":690,"data":"51"},{"metaId":1192,"data":"3693"}],[{"metaId":690,"data":"51"},{"metaId":1192,"data":"3712"}],[{"metaId":690,"data":"89"},{"metaId":1192,"data":"4228"}'
;WITH request
as
(
SELECT 1 requestId,
'test' property1191,
'['+replace(replace(@json, '[', ''), ']', '')+']' as json
)
SELECT *
FROM
(
SELECT  P.requestId
,   JSON_VALUE(AttsData.value, '$.metaId') as id
,   JSON_VALUE(AttsData.value, '$.data') as data
,   ROW_NUMBER() OVER (PARTITION BY P.requestId, JSON_VALUE(AttsData.value, '$.metaId') ORDER BY CAST(attsdata.[key] AS INT)) AS row_id
FROM request P
CROSS APPLY OPENJSON (P.json)  AttsData
) DS
PIVOT 
(
MAX(data) FOR Id IN ([690], [1192])
) piv;