SQLTeam.com | Weblogs | Forums

Openjson problem with tsql

#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?

#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) 
)
#3

Thanks, the CROSS APPLY works like a charm:)