dabasys
January 10, 2019, 5:42pm
#1
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?
JamesK
January 10, 2019, 9:33pm
#2
Something along these lines:
DECLARE @json NVARCHAR(1024)
=
'{
"api":
{
"results": 2,
"names":
{
"1":
{
"firstname": "Ole",
"lastname": "Hansen"
},
"2":
{
"firstname": "Jesper",
"lastname": "Hansen"
}
}
}
}';
SELECT *
FROM OPENJSON(@json,'$.api.names') AS c
CROSS APPLY OPENJSON(c.value)
WITH(
firstname nvarchar(255),
lastname nvarchar(255)
)
If you are constructing the json (rather than being give to you from another source), I would prefer to use an array instead of the individually named elements, like shown below:
DECLARE @json NVARCHAR(1024)
=
N'{
"api":
{
"results":"2",
"names":
[
{
"id": 1,
"firstname":"Ole",
"lastname":"Hansen"
},
{
"id": 2,
"firstname":"Jesper",
"lastname":"Hansen"
}
]
}
}';
SELECT *
FROM OPENJSON(@json,'$.api.names')
WITH(
firstname nvarchar(255),
lastname nvarchar(255)
)
1 Like
dabasys
January 11, 2019, 6:32am
#3
Thanks, the CROSS APPLY works like a charm:)