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" }

Thanks for any help

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" }

I do not know how to extract a path of json as navarchar(max). It always return 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" }

Thanks for helps

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" }

Thanks for any help