T-SQL OPENJSON $ AS JSON not working

Viewed 1455

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!

1 Answers
Related