Select Json Object as nvarchar(max) in JSON_VALUE

Question

I do not know how to extract a path of JSON as nvarchar(max). It always returns null.

I do not want to specify the fields to get because I have no idea the field names. So the idea here is select it as a string and process them later.

DECLARE @json nvarchar(max) =
'{
    "firstName": "John",
    "lastName" : "doe",
    "address"  : {
        "streetAddress": "naist street",
        "city"         : "Nara",
        "postalCode"   : "630-0192"
    }
}'

SELECT [Type]
FROM OPENJSON( @json, '$.address' ) 
WITH ([Type] NVARCHAR(max) '$');

The expected result is that the Type column contains the whole JSON object of address as string

{ "streetAddress": "naist street", "city": "Nara", "postalCode" : "630-0192" }

Answer 1

You need to execute the statement in a different way. Note, that when you use OPENJSON() with explicit schema you need to use AS JSON to specify that the referenced property contains an inner JSON object or array. Of course, 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"
    }