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

Solution

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;