Question 108996 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

CROSS APPLY OPENJSON / PIVOT – wrong ordering

the following code opens a json content and pivots it in the end. Somehow the ordering / pivoting mixes up wrongly.

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 example

{"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"}

Current results – wrong ordering (e.g. 690: 1 should show 1192: 4352)

requestId row_id 690 1192
1 7 1 4228
1 6 2 3712
1 5 3 3693
1 4 4 3749
1 2 51 3887
1 3 51 4372
1 1 89 4352

Target – correct ordering (row_id not shown)

requestId row_id 690 1192
1 x 1 4352
1 x 2 3887
1 x 3 4372
1 x 4 3749
1 x 51 3693
1 x 51 3712
1 x 89 4228

I am interested in the correct ordering according to JSON syntax

Tags:

2

Answers


  1. 0

    The sort is messed up cause you use (SELECT NULL).
    To get proper ordering you need to use the "edge" version of OPENJSON, something like this:

    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;
    Login or Signup to reply.
  2. 0

    Instead of using JSON_VALUE like the other answer, you can continue using OPENJSON with a schema. You just need two OPENJSON calls, once without a schema to get the array values with indexes, and once with a schema to break that out.

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search