January 8, 2018 at 4:27 am
Hi
I have an issue in troubleshooting below is sample code
DECLARE @json NVARCHAR(MAX) = N'{
"id": 1,
"name": "Foo",
"price": 123,
"tags": [
"Bar",
"Eek"
],
"stock": {
"warehouse": 300,
"retail": 20
}
}'
SELECT *
FROM OPENJSON(@json)
WITH(
idn int '$.id',
name NVARCHAR(50) '$.name',
price float '$.price',
warehouse NVARCHAR(50) '$.stock.warehouse',
retail NVARCHAR(50) '$.stock.retail',
tags varchar(100) '$.tags[0]',
tags varchar(100) '$.tags[1]')
how do we pass param to tags in the above SQL query is it is possible , say here in the above query we have 2 tags how to tag of @input value
say if @input is specified as 0 its should return first value and so on..
like wise SP_execute or exec (@SQL) is not working
Req:1
Declare @SQL varchar(4000)
Select @SQL ='SELECT * FROM OPENJSON('+@json+')'
print @SQL
exec (@SQL )
Output:
SELECT * FROM OPENJSON({
"id": 1,
"name": "Foo",
"price": 123,
"tags": [
"Bar",
"Eek"
],
"stock": {
"warehouse": 300,
"retail": 20
}
})
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'id'.
Req:2
declare @iput int =1
---JSON path is not properly formatted. Unexpected character '@' is found at position 7.
-- I knew that syntax is wrong this is just to show
SELECT *FROM OPENJSON(@json)
with (tags varchar(100) '$.tags[@iput]')
-- incorrect syntax
SELECT *FROM OPENJSON(@json)
with (tags varchar(100) '$.tags['@iput']')
January 8, 2018 at 1:02 pm
Hi,
If you have different variation of parameters you can do something like
if(@style = 1)
begin
SELECT *
FROM OPENJSON(@json)
WITH(
idn int '$.id',
name NVARCHAR(50) '$.name',
price float '$.price',
warehouse NVARCHAR(50) '$.stock.warehouse',
retail NVARCHAR(50) '$.stock.retail',
tags varchar(100) '$.tags[0]',
tags varchar(100) '$.tags[1]')
end;
if(@style = 2)
begin
SELECT *
FROM OPENJSON(@json)
WITH(
idn int '$.id',
name NVARCHAR(50) '$.name',
warehouse NVARCHAR(50) '$.stock.warehouse',
tags varchar(100) '$.tags[0]')
...
etc
January 8, 2018 at 1:03 pm
Another option is use a dynamic SQL.
January 8, 2018 at 10:33 pm
Evgeny - Monday, January 8, 2018 1:03 PMAnother option is use a dynamic SQL.
Thanks for your reply We dont know how many tags are there and it is up to user to decide so we cant not opt the above first solution.
I have tried dynamic option but it did not work me
January 11, 2018 at 3:23 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply