January 10, 2019 at 7:43 am
I got a problem with some json data, and getting to read it column by column in sql.
The json {
"api": {
"results": 2,
"names": {
"1": {
"firstname": "Ole",
"lastname": "Hansen"
},
"2": {
"firstname": "Jesper",
"lastname": "Hansen"
},
}
}
}
The Sql:SELECT *
FROM OPENJSON(@json,'$.api.names')
WITH(
firstname nvarchar(255),
lastname nvarchar(255)
) a
My problem is that I cant get it output in rows with:
firstname and lastname in the same row
What I can do is do a SELECT *
FROM OPENJSON(@json,'$.api.names')
And then it will return the json data from from each "object" in a row as json format
Whats my problem? Can anybody understand what I mean or is it total jibbirish?
January 10, 2019 at 8:22 am
And if I do like this
SELECT *
FROM OPENJSON(@json,'$.api.names."1"')
WITH(
firstname nvarchar(255),
lastname nvarchar(255)
) a
I can get the first "row" just like I should, but I want all "rows" in the same query?
January 10, 2019 at 1:42 pm
Hi Tommy,
If you can modify the query (along with the JSON) like below, it might help:
DECLARE @MyJson VARCHAR(MAX) = '{
"api": {
"results": 2,
"names": [
{
"firstname": "Ole",
"lastname": "Hansen"
},
{
"firstname": "Jesper",
"lastname": "Hansen"
}
]
}
}'
SELECT * FROM OPENJSON(@MyJson, '$.api.names')
WITH(
firstname nvarchar(255),
lastname nvarchar(255)
) a
Is it desired?
Thanks.
January 10, 2019 at 1:49 pm
That is the desired results - however, I would love a solution where I didnt have to change the JSON - is that possible?
January 10, 2019 at 1:55 pm
I fear that may not be possible as the format that you gave is not valid as you can check below:
DECLARE @TestJson VARCHAR(MAX) = '{
"api": {
"results": 2,
"names": {
"1": {
"firstname": "Ole",
"lastname": "Hansen"
},
"2": {
"firstname": "Jesper",
"lastname": "Hansen"
},
}
}
}'
SELECT ISJSON(@TestJson)
January 10, 2019 at 1:57 pm
Ahh thats why:) I will go ahead and change the source JSON, thanks for your input and help:)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply