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.
2条答案
按热度按时间pb3s4cty1#
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 :
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 byOPENJSON
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 betweeno.Key
andr.Key
to map rows to origins :webghufk2#
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.
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 |