You need to execute the following statement in a different way. Note, that when you use OPENJSON()OPENJSON()
with explicit schema you need to use AS JSONAS JSON
to specify that the referenced property contains an inner JSON object or array. NoteOf course, that you may use JSON_QUERY()
to get the same result:
JSON:
DECLARE @json nvarchar(max) =
'{
"firstName": "John",
"lastName" : "doe",
"address" : {
"streetAddress": "naist street",
"city" : "Nara",
"postalCode" : "630-0192"
}
}'
Statement (using OPENJSON()
):
SELECT *
FROM OPENJSON(@json)
WITH ([Type] NVARCHAR(max) '$.address' AS JSON);
Statement (using JSON_QUERY()
)
SELECT JSON_QUERY(@json, '$.address') AS [Type]
Result:
Type
{
"streetAddress": "naist street",
"city" : "Nara",
"postalCode" : "630-0192"
}