SQL Server openjson

Question

From my json data, I need result like this: I want to get two columns distances and durations - can anyone help?

{ 
    "distances": [
                     [0, 6136.2, 6136.2, 0],
                     [4704.7, 0, 0, 4704.7],
                     [4704.7, 0, 0, 4704.7],
                     [0, 6136.2, 6136.2, 0]
                 ],
    "durations": [
                     [0, 554.5, 554.5, 0],
                     [446.4, 0, 0, 446.4],
                     [446.4, 0, 0, 446.4],
                     [0, 554.5, 554.5, 0]
                 ]
}

Answer 1

Here's one way

DECLARE @Json NVARCHAR(MAX) = N'{ 
    "distances": [
                     [0, 6136.2, 6136.2, 0],
                     [4704.7, 0, 0, 4704.7],
                     [4704.7, 0, 0, 4704.7],
                     [0, 6136.2, 6136.2, 0]
                 ],
    "durations": [
                     [0, 554.5, 554.5, 0],
                     [446.4, 0, 0, 446.4],
                     [446.4, 0, 0, 446.4],
                     [0, 554.5, 554.5, 0]
                 ]
}'


SELECT CA.distance,
       CA.duration,
       dist1.[key] AS OuterArrayIndex,
       CA.[key] AS InnerArrayIndex
FROM   OPENJSON(JSON_QUERY(@Json, '$.distances')) dist1
       JOIN OPENJSON(JSON_QUERY(@Json, '$.durations')) dur1
         ON dist1.[key] = dur1.[key] 
CROSS APPLY
(
SELECT dist2.value AS distance,
       dur2.value  AS duration,
       dist2.[key]
FROM   OPENJSON(dist1.value) dist2
       JOIN OPENJSON(dur1.value) dur2
         ON dist2.[key] = dur2.[key] 
)CA
ORDER BY OuterArrayIndex, 
         InnerArrayIndex

Returns

+----------+----------+-----------------+-----------------+
| distance | duration | OuterArrayIndex | InnerArrayIndex |
+----------+----------+-----------------+-----------------+
|        0 |        0 |               0 |               0 |
|   6136.2 |    554.5 |               0 |               1 |
|   6136.2 |    554.5 |               0 |               2 |
|        0 |        0 |               0 |               3 |
|   4704.7 |    446.4 |               1 |               0 |
|        0 |        0 |               1 |               1 |
|        0 |        0 |               1 |               2 |
|   4704.7 |    446.4 |               1 |               3 |
|   4704.7 |    446.4 |               2 |               0 |
|        0 |        0 |               2 |               1 |
|        0 |        0 |               2 |               2 |
|   4704.7 |    446.4 |               2 |               3 |
|        0 |        0 |               3 |               0 |
|   6136.2 |    554.5 |               3 |               1 |
|   6136.2 |    554.5 |               3 |               2 |
|        0 |        0 |               3 |               3 |
+----------+----------+-----------------+-----------------+