This function helps in automatically converting json into relational format by preparing sql with json field parsing. It will detect JSON type data for the first column in the provided Sql statement and convert into Json paths which is further consolidated in an Sql Select statement which is returned as an output to the user. Users can then use this statement to query the data from that specific table.
Syntax
declare @result nvarchar(max);
exec lyft_detect_json_sql
@query=’your query’
@return_json_sql=1, @row_set=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_sql'
Is a character expression of type nvarchar. The expression is used to tell the Lyftrondata app to return the data in the Sql Select statement.
‘@row_set’
Is a character expression of type nvarchar. The expression is used to provide the top rows that should be used to create schema from JSON for sql statement creation.
Return types
nvarchar(max)
Example : Selecting JSON from table field
Step 1: Select JSON from the table field as shown below
select UserJson from [adventureWorks2012].[dbo].[dbo.JsonTbl]
UserJson
-------
{
Step 1:
"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_sql procedure
DECLARE @result NVARCHAR(max);
EXEC lyft_detect_json_sql
@query='select UserJson from [adventureWorks2012].[dbo].[dbo.JsonTbl]',
@return_json_sql=1,@row_set=100,@json_value_sql=@result
SELECT @result;
Result
SELECT Json_value(userjson, '$.destination_addresses[0]') AS
'$.destination_addresses[0]',
Json_value(userjson, '$.destination_addresses[1]') AS
'$.destination_addresses[1]',
Json_value(userjson, '$.destination_addresses[2]') AS
'$.destination_addresses[2]',
Json_value(userjson, '$.destination_addresses[3]') AS
'$.destination_addresses[3]',
Json_value(userjson, '$.destination_addresses[4]') AS
'$.destination_addresses[4]',
Json_value(userjson, '$.destination_addresses[5]') AS
'$.destination_addresses[5]',
Json_value(userjson, '$.origin_addresses[0]') AS
'$.origin_addresses[0]',
Json_value(userjson, '$.rows[0].elements[0].distance.text') AS
'$.rows[0].elements[0].distance.text',
Json_value(userjson, '$.rows[0].elements[0].distance.value') AS
'$.rows[0].elements[0].distance.value',
Json_value(userjson, '$.rows[0].elements[0].duration.text') AS
'$.rows[0].elements[0].duration.text',
Json_value(userjson, '$.rows[0].elements[0].duration.value') AS
'$.rows[0].elements[0].duration.value',
Json_value(userjson, '$.rows[0].elements[0].status') AS
'$.rows[0].elements[0].status',
Json_value(userjson, '$.rows[0].elements[1].distance.text') AS
'$.rows[0].elements[1].distance.text',
Json_value(userjson, '$.rows[0].elements[1].distance.value') AS
'$.rows[0].elements[1].distance.value',
Json_value(userjson, '$.rows[0].elements[1].duration.text') AS
'$.rows[0].elements[1].duration.text',
Json_value(userjson, '$.rows[0].elements[1].duration.value') AS
'$.rows[0].elements[1].duration.value',
Json_value(userjson, '$.rows[0].elements[1].status') AS
'$.rows[0].elements[1].status',
Json_value(userjson, '$.rows[0].elements[2].distance.text') AS
'$.rows[0].elements[2].distance.text',
Json_value(userjson, '$.rows[0].elements[2].distance.value') AS
'$.rows[0].elements[2].distance.value',
Json_value(userjson, '$.rows[0].elements[2].duration.text') AS
'$.rows[0].elements[2].duration.text',
Json_value(userjson, '$.rows[0].elements[2].duration.value') AS
'$.rows[0].elements[2].duration.value',
Json_value(userjson, '$.rows[0].elements[2].status') AS
'$.rows[0].elements[2].status',
Json_value(userjson, '$.rows[0].elements[3].distance.text') AS
'$.rows[0].elements[3].distance.text',
Json_value(userjson, '$.rows[0].elements[3].distance.value') AS
'$.rows[0].elements[3].distance.value',
Json_value(userjson, '$.rows[0].elements[3].duration.text') AS
'$.rows[0].elements[3].duration.text',
Json_value(userjson, '$.rows[0].elements[3].duration.value') AS
'$.rows[0].elements[3].duration.value',
Json_value(userjson, '$.rows[0].elements[3].status') AS
'$.rows[0].elements[3].status',
Json_value(userjson, '$.rows[0].elements[4].distance.text') AS
'$.rows[0].elements[4].distance.text',
Json_value(userjson, '$.rows[0].elements[4].distance.value') AS
'$.rows[0].elements[4].distance.value',
Json_value(userjson, '$.rows[0].elements[4].duration.text') AS
'$.rows[0].elements[4].duration.text',
Json_value(userjson, '$.rows[0].elements[4].duration.value') AS
'$.rows[0].elements[4].duration.value',
Json_value(userjson, '$.rows[0].elements[4].status') AS
'$.rows[0].elements[4].status',
Json_value(userjson, '$.rows[0].elements[5].distance.text') AS
'$.rows[0].elements[5].distance.text',
Json_value(userjson, '$.rows[0].elements[5].distance.value') AS
'$.rows[0].elements[5].distance.value',
Json_value(userjson, '$.rows[0].elements[5].duration.text') AS
'$.rows[0].elements[5].duration.text',
Json_value(userjson, '$.rows[0].elements[5].duration.value') AS
'$.rows[0].elements[5].duration.value',
Json_value(userjson, '$.rows[0].elements[5].status') AS
'$.rows[0].elements[5].status',
Json_value(userjson, '$.status') AS
'$.status',
Json_value(userjson, '$.rows[0].elements[6].distance.text') AS
'$.rows[0].elements[6].distance.text',
Json_value(userjson, '$.rows[0].elements[6].distance.value') AS
'$.rows[0].elements[6].distance.value',
Json_value(userjson, '$.rows[0].elements[6].duration.text') AS
'$.rows[0].elements[6].duration.text',
Json_value(userjson, '$.rows[0].elements[6].duration.value') AS
'$.rows[0].elements[6].duration.value',
Json_value(userjson, '$.rows[0].elements[6].status') AS
'$.rows[0].elements[6].status',
Json_value(userjson, '$.rows[0].elements[7].distance.text') AS
'$.rows[0].elements[7].distance.text',
Json_value(userjson, '$.rows[0].elements[7].distance.value') AS
'$.rows[0].elements[7].distance.value',
Json_value(userjson, '$.rows[0].elements[7].duration.text') AS
'$.rows[0].elements[7].duration.text',
Json_value(userjson, '$.rows[0].elements[7].duration.value') AS
'$.rows[0].elements[7].duration.value',
Json_value(userjson, '$.rows[0].elements[7].status') AS
'$.rows[0].elements[7].status'
FROM [adventureWorks2012].[dbo].[dbo.jsontbl]