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 this:
SELECT *
FROM OPENJSON(@json,'$.api.names')
And then it will return the json data from from each "object" in a row as json format
And if I do:
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?
Whats my problem? Can anybody understand what I mean or is it total jibbirish?