JSON formatting with SQL Server

JOo
October 12, 2023
197 views
1 vote
2 Answers

I would like to format the json file shown below into a table.
I get the json file from an REST-API.
I already tried to resolve it with OPENJSON and CROSS APPLYS but without success.
There I get only zero values back.
Is there a way to format the json file so that it matches the desired result?

Here is the JSON Snippet:

{
   "destination_addresses" :
   [
      "Straße ohne Straßennamen, 99999 Ort, Deutschland",
      "Straße ohne Straßennamen, 99999 Ort, Deutschland"
   ],
   "origin_addresses" :
   [
      "Straße ohne Straßennamen, 99999 Ort, Deutschland"
   ],
   "rows" :
   [
      {
         "elements" :
         [
            {
               "distance" :
               {
                  "text" : "337 km",
                  "value" : 337183
               },
               "duration" :
               {
                  "text" : "3 Stunden, 36 Minuten",
                  "value" : 12968
               },
               "status" : "OK"
            },
            {
               "distance" :
               {
                  "text" : "397 km",
                  "value" : 397126
               },
               "duration" :
               {
                  "text" : "4 Stunden, 22 Minuten",
                  "value" : 15692
               },
               "status" : "OK"
            }
         ]
      }
   ],
   "status" : "OK"
}

The API Output looks like this:

The result should be a table that looks like this:

destination_addresses origin_addresses distance_text distance_value duration_text duration_value

Straße ohne Straßennamen, 99999 Ort, Deutschland Straße ohne Straßennamen, 99999 Ort, Deutschland 337 km 337183 3 Stunden, 36 Minuten 12968

Straße ohne Straßennamen, 99999 Ort, Deutschland Straße ohne Straßennamen, 99999 Ort, Deutschland 397 km 397126 4 Stunden, 22 Minuten 12968

Here is my Cross Apply attempt:

SELECT 
ori.destination_addresses, 
ele.origin_addresses ,
f.[text], f.[value], 
dur.[text], 
dur.[value] 
FROM OPENJSON(@json , '$')
CROSS APPLY OPENJSON(value)
with(destination_addresses nvarchar(100),
origin_addresses nvarchar(max) AS JSON)ori
CROSS APPLY OPENJSON(ori.origin_addresses)
with(origin_addresses nvarchar(100),
elements nvarchar(max) AS JSON) ele 
CROSS APPLY OPENJSON(ele.elements)
with(distance nvarchar(max) AS JSON)dis 
CROSS APPLY OPENJSON(dis.distance)
with([text] nvarchar(20),
[value] numeric,
duration nvarchar(max) AS JSON)dur 
CROSS APPLY OPENJSON(dur.duration)
with([text] nvarchar(20),[value] numeric)f

Here is the Cross Apply result:

I am also getting this error msg.
The JSON text is not formatted properly. The unexpected character "O" was found at position 0.

Answers

I’m assuming in reality the json you have is an array of elements rather than one. If not so, you can remove one level of OPENJSON.
I also assume there’s always one origin address.

select  ad.value as destination_address, json_value(x.value,'$.origin_addresses[0]') as origin_address, d.*
from openjson(N'[{
   "destination_addresses" :
   [
      "Straße ohne Straßennamen, 99999 Ort, Deutschland",
      "Straße ohne Straßennamen, 99999 Ort, Deutschland"
   ],
   "origin_addresses" :
   [
      "Straße ohne Straßennamen, 99999 Ort, Deutschland"
   ],
   "rows" :
   [
      {
         "elements" :
         [
            {
               "distance" :
               {
                  "text" : "337 km",
                  "value" : 337183
               },
               "duration" :
               {
                  "text" : "3 Stunden, 36 Minuten",
                  "value" : 12968
               },
               "status" : "OK"
            },
            {
               "distance" :
               {
                  "text" : "397 km",
                  "value" : 397126
               },
               "duration" :
               {
                  "text" : "4 Stunden, 22 Minuten",
                  "value" : 15692
               },
               "status" : "OK"
            }
         ]
      }
   ],
   "status" : "OK"
}]') x
cross apply openjson(x.value, '$.destination_addresses') ad
cross apply OPENJSON(x.value, '$.rows[0].elements') el
cross apply OPENJSON(el.value) with (
    distanceText nvarchar(100) '$.distance.text'
,   distanceValue bigint '$.distance.value'
,   durationText nvarchar(100) '$.duration.text'
,   durationValue bigint '$.duration.value'
,   status varchar(100) '$.status'  
    ) d
where el.[key] = ad.[key]

I split the json by destination addresses, and then split the rows the same way. where el.[key] = ad.[key] makes sure each row from the addresses element matchings corresponding row of "rows".

Output:

destination_address	origin_address	distanceText	distanceValue	durationText	durationValue	status
Straße ohne Straßennamen, 99999 Ort, Deutschland	Straße ohne Straßennamen, 99999 Ort, Deutschland	337 km	337183	3 Stunden, 36 Minuten	12968	OK
Straße ohne Straßennamen, 99999 Ort, Deutschland	Straße ohne Straßennamen, 99999 Ort, Deutschland	397 km	397126	4 Stunden, 22 Minuten	15692	OK

- PanagiotisKanavos
- October 12, 2023 at 1:38 pm
- 0 votes
0
The best option would be to parse the JSON text and create the desired rows before inserting it into the database. You could try to use this query BUT it’s VERY fragile :
```
SELECT 
    d.value as destination_address,
    o.value as origin_address,
    Distance_Text ,
    Distance_Value,
    Duration_Text ,
    Duration_Value
FROM 
    OPENJSON(@json,'$.destination_addresses') d,
    OPENJSON(@json,'$.origin_addresses')  o,
    OPENJSON(@json,'$.rows')  r 
    cross apply openjson(r.value,'$.elements') e
    cross apply openjson(e.value) with ( 
        Distance_Text  nvarchar(20) '$.distance.text',
        Distance_Value integer      '$.distance.value',
        Duration_Text  nvarchar(20) '$.duration.text',
        Duration_Value integer      '$.duration.value'
    ) a
where d.[key]=e.[key]
```
The JSON text itself has problems. It seems that destination and actual element values must be matched by index, BUT elements are contained in a rows array. What happens if there are multiple rows? Or multiple destinations?

The key column returned by OPENJSON without a schema contains the array index. This can be used to somehow match destination and element data by index, assuming there’s only one row.

If there are several rows, e.Key can’t be used to JOIN with destination addresses, as it contains the element index inside a single row only.

If each row corresponds to a single origin address we could use a join between o.Key and r.Key to map rows to origins :
```
where d.[key]=e.[key] and o.[key]=r.[key]
```
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.