'Reading json array into rows in SQL Server

Given the sample json data below, how can I write a query to pull the array data all in one step? My goal is to have one row for each item in the ActionRecs array (4). My actual json is more complicated but I think this gives a good example of my goal.

declare @json2 nvarchar(max)
set @json2 = '{
    "RequestId": "1",
    "ActionRecs": [
        {
            "Type": "Submit",
            "Employee": "Joe"
        },
        {
            "Type": "Review",
            "Employee": "Betty"
        },
        {
            "Type": "Approve",
            "Employee": "Sam"
        },
        {
            "Type": "Approve",
            "Employee": "Bill"
        }
    ]
}'

SELECT x.*
, JSON_QUERY(@json2, '$.ActionRecs') as ActionArray
from OPENJSON(@json2) 
with (Id varchar(5) '$.RequestId') as x

Query Reults

jsonsql-servertsqljson-queryjavaandroidkotlinmapboxmapbox-android


Solution 1:[1]

One possible approach is to use OPENJSON() with explicit schema and an additional CROSS APPLY operator:

DECLARE @json nvarchar(max)
SET @json = N'{
    "RequestId": "1",
    "ActionRecs": [
        {"Type": "Submit", "Employee": "Joe"},
        {"Type": "Review", "Employee": "Betty"},
        {"Type": "Approve", "Employee": "Sam"},
        {"Type": "Approve", "Employee": "Bill"}
    ]
}'

SELECT i.Id, a.[Type], a.[Employee]
FROM OPENJSON(@json) WITH (
   Id varchar(5) '$.RequestId',
   ActionRecs nvarchar(max) '$.ActionRecs' AS JSON
) AS i
CROSS APPLY OPENJSON(i.ActionRecs) WITH (
   [Type] nvarchar(max) '$.Type',
   [Employee] nvarchar(max) '$.Employee'
) a

Output:

Id  Type    Employee
1   Submit  Joe
1   Review  Betty
1   Approve Sam
1   Approve Bill

Solution 2:[2]

You already have a RouteProgressObserver, so you could check the route state and when it is COMPLETE you can do something. See https://docs.mapbox.com/android/navigation/guides/turn-by-turn-navigation/route-progress/#listen-to-progress-change for more information about route progress.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 David Wasser

Related Questions