This function helps in converting provided JSON into Json paths. It will detect JSON type data for the first column in the provided Sql statement and convert into Json paths which is then output to the user in relational format. The schema is built from the table having highest value of JSON data and returns the row from the table having highest JSON data.
The user will see the output having Json paths in a table format as an end result.
Syntax
declare @result nvarchar(max);
exec lyft_detect_json_paths
@query=’your query’
@return_json_paths=1,@json_value_sql = @result
select @result
Arguments
'@query'
Is a character expression of type nvarchar. The expression is used to provide a sql query with one column having JSON data type.
'@return_json_paths'
Is a character expression of type nvarchar. The expression is used to tell the Lyftrondata app to return the data in JSON paths.
Return types
nvarchar(max)
Example 1: Selecting JSON from table field
Step 1: Select JSON from the table field as shown below
select UserJson from [adventureWorks2012].[dbo].[dbo.JsonTbl]
UserJson
-------
{
"destination_addresses":[
"Washington, DC, USA",
"Philadelphia, PA, USA",
"Santa Barbara, CA, USA",
"Miami, FL, USA",
"Austin, TX, USA",
"Napa County, CA, USA"
],
"origin_addresses":[
"New York, NY, USA"
],
"rows":[
{
"elements":[
{
"distance":{
"text":"227 mi",
"value":365468
},
"duration":{
"text":"3 hours 54 mins",
"value":14064
},
"status":"OK"
},
{
"distance":{
"text":"94.6 mi",
"value":152193
},
"duration":{
"text":"1 hour 44 mins",
"value":6227
},
"status":"OK"
},
{
"distance":{
"text":"2,878 mi",
"value":4632197
},
"duration":{
"text":"1 day 18 hours",
"value":151772
},
"status":"OK"
},
{
"distance":{
"text":"1,286 mi",
"value":2069031
},
"duration":{
"text":"18 hours 43 mins",
"value":67405
},
"status":"OK"
},
{
"distance":{
"text":"1,742 mi",
"value":2802972
},
"duration":{
"text":"1 day 2 hours",
"value":93070
},
"status":"OK"
},
{
"distance":{
"text":"2,871 mi",
"value":4620514
},
"duration":{
"text":"1 day 18 hours",
"value":152913
},
"status":"OK"
}
]
}
],
"status":"OK"
}
Step 2: Execute the lyft_detect_json_paths procedure
DECLARE @result NVARCHAR(max);
EXEC lyft_detect_json_paths
@query='select UserJson from [adventureWorks2012].[dbo].[dbo.JsonTbl]',
@return_json_paths=1,@json_value_sql=@result;
SELECT @result;
Result
1 $.destination_addresses[0]
2 $.destination_addresses[1]
3 $.destination_addresses[2]
4 $.destination_addresses[3]
5 $.destination_addresses[4]
6 $.destination_addresses[5]
7 $.origin_addresses[0]
8 $.rows[0].elements[0].distance.text
9 $.rows[0].elements[0].distance.value
10 $.rows[0].elements[0].duration.text
11 $.rows[0].elements[0].duration.value
12 $.rows[0].elements[0].status
13 $.rows[0].elements[1].distance.text
14 $.rows[0].elements[1].distance.value
15 $.rows[0].elements[1].duration.text
16 $.rows[0].elements[1].duration.value
17 $.rows[0].elements[1].status
18 $.rows[0].elements[2].distance.text
19 $.rows[0].elements[2].distance.value
20 $.rows[0].elements[2].duration.text
21 $.rows[0].elements[2].duration.value
22 $.rows[0].elements[2].status
23 $.rows[0].elements[3].distance.text
24 $.rows[0].elements[3].distance.value
25 $.rows[0].elements[3].duration.text
26 $.rows[0].elements[3].duration.value
27 $.rows[0].elements[3].status
28 $.rows[0].elements[4].distance.text
29 $.rows[0].elements[4].distance.value
30 $.rows[0].elements[4].duration.text
31 $.rows[0].elements[4].duration.value
32 $.rows[0].elements[4].status
33 $.rows[0].elements[5].distance.text
34 $.rows[0].elements[5].distance.value
35 $.rows[0].elements[5].duration.text
36 $.rows[0].elements[5].duration.value
37 $.rows[0].elements[5].status
38 $.status
Example 2: Selecting JSON from file
DECLARE @result NVARCHAR(max);
EXEC lyft_detect_json_paths
@query='select ''{
"destination_addresses":[
"Washington, DC, USA",
"Philadelphia, PA, USA",
"Santa Barbara, CA, USA",
"Miami, FL, USA",
"Austin, TX, USA",
"Napa County, CA, USA"
],
"origin_addresses":[
"New York, NY, USA"
],
"rows":[
{
"elements":[
{
"distance":{
"text":"227 mi",
"value":365468
},
"duration":{
"text":"3 hours 54 mins",
"value":14064
},
"status":"OK"
},
{
"distance":{
"text":"94.6 mi",
"value":152193
},
"duration":{
"text":"1 hour 44 mins",
"value":6227
},
"status":"OK"
},
{
"distance":{
"text":"2,878 mi",
"value":4632197
},
"duration":{
"text":"1 day 18 hours",
"value":151772
},
"status":"OK"
},
{
"distance":{
"text":"1,286 mi",
"value":2069031
},
"duration":{
"text":"18 hours 43 mins",
"value":67405
},
"status":"OK"
},
{
"distance":{
"text":"1,742 mi",
"value":2802972
},
"duration":{
"text":"1 day 2 hours",
"value":93070
},
"status":"OK"
},
{
"distance":{
"text":"2,871 mi",
"value":4620514
},
"duration":{
"text":"1 day 18 hours",
"value":152913
},
"status":"OK"
}
]
}
],
"status":"OK"
}''',
@return_json_paths=1,@json_value_sql=@result;
SELECT @result;
Result
1 $.destination_addresses[0]
2 $.destination_addresses[1]
3 $.destination_addresses[2]
4 $.destination_addresses[3]
5 $.destination_addresses[4]
6 $.destination_addresses[5]
7 $.origin_addresses[0]
8 $.rows[0].elements[0].distance.text
9 $.rows[0].elements[0].distance.value
10 $.rows[0].elements[0].duration.text
11 $.rows[0].elements[0].duration.value
12 $.rows[0].elements[0].status
13 $.rows[0].elements[1].distance.text
14 $.rows[0].elements[1].distance.value
15 $.rows[0].elements[1].duration.text
16 $.rows[0].elements[1].duration.value
17 $.rows[0].elements[1].status
18 $.rows[0].elements[2].distance.text
19 $.rows[0].elements[2].distance.value
20 $.rows[0].elements[2].duration.text
21 $.rows[0].elements[2].duration.value
22 $.rows[0].elements[2].status
23 $.rows[0].elements[3].distance.text
24 $.rows[0].elements[3].distance.value
25 $.rows[0].elements[3].duration.text
26 $.rows[0].elements[3].duration.value
27 $.rows[0].elements[3].status
28 $.rows[0].elements[4].distance.text
29 $.rows[0].elements[4].distance.value
30 $.rows[0].elements[4].duration.text
31 $.rows[0].elements[4].duration.value
32 $.rows[0].elements[4].status
33 $.rows[0].elements[5].distance.text
34 $.rows[0].elements[5].distance.value
35 $.rows[0].elements[5].duration.text
36 $.rows[0].elements[5].duration.value
37 $.rows[0].elements[5].status
38 $.status