This function helps in converting JSON into relational format by preparing a relational format with values as an output. It will detect JSON type data for the first column in the provided Sql statement and convert into Json paths which are further evaluated by query analyzer. The schema is built from the table having highest value of JSON data and returns the row from the table having highest JSON data.
Syntax
declare @result nvarchar(max);
exec lyft_detect_json_value
@query=’your query’
@return_json_value=1,@row_set=1,@file=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_value'
Is a character expression of type nvarchar. The expression is used to tell the Lyftrondata app to return the data in relational format.
‘@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.
‘@file’
Is a character expression of type nvarchar. The value of this attribute should be 1 when data is not from table and from a file source.
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_value procedure
DECLARE @result NVARCHAR(max);
EXEC lyft_detect_json_value
@query='select UserJson from [adventureWorks2012].[dbo].[dbo.JsonTbl]',
@return_json_value=1,@row_set=100,@json_value_sql=@result;
SELECT @result;
Result
Example 2: Selecting JSON from file
DECLARE @result NVARCHAR(max);
EXEC lyft_detect_json_value
@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_value=1,@row_set=100,@file=1,@json_value_sql=@result;
SELECT @result;