Can anyone tell me why the first example works, but the second doesn't? To me they look like they should equate to the same thing...
DECLARE @prmInputData NVARCHAR(MAX) = '{ "a": { "b": 1, "c": 2 } }'
SELECT b, c, a
FROM OPENJSON(@prmInputData, '$')
WITH (
b INT '$.a.b',
c INT '$.a.c',
a NVARCHAR(MAX) '$.a' AS JSON
)
SELECT b, c, a
FROM OPENJSON(@prmInputData, '$.a')
WITH (
b INT '$.b',
c INT '$.c',
a NVARCHAR(MAX) '$' AS JSON
)
The first example returns "a" as a JSON object, correctly.
The second example returns "a" as NULL, incorrectly.
I'm not sure why!