T-SQL OPENJSON $ AS JSON not working

Question

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!

Answer 1

Easy way to spot the difference is to omit WITH part

Your original query:

DECLARE @prmInputData NVARCHAR(MAX) = '{ "a": { "b": 1, "c": 2 } }';

SELECT *
FROM OPENJSON(@prmInputData, '$')
WITH (
  b INT '$.a.b',
  c INT '$.a.c',
  a NVARCHAR(MAX) '$.a' AS JSON
);

SELECT *
FROM OPENJSON(@prmInputData, '$.a')
WITH (
  b INT '$.b',
  c INT '$.c',
  a NVARCHAR(MAX) '$' AS JSON
);

Output:

╔═══╦═══╦════════════════════╗
║ b ║ c ║         a          ║
╠═══╬═══╬════════════════════╣
║ 1 ║ 2 ║ { "b": 1, "c": 2 } ║
╚═══╩═══╩════════════════════╝

 vs 

╔═══╦═══╦══════╗
║ b ║ c ║  a   ║
╠═══╬═══╬══════╣
║ 1 ║ 2 ║ NULL ║
╚═══╩═══╩══════╝

---

After removing WITH:

DECLARE @prmInputData NVARCHAR(MAX) = '{ "a": { "b": 1, "c": 2 } }';

SELECT *
FROM OPENJSON(@prmInputData, '$');

SELECT *
FROM OPENJSON(@prmInputData, '$.a');

Result:

╔═════╦════════════════════╦══════╗
║ key ║       value        ║ type ║
╠═════╬════════════════════╬══════╣
║ a   ║ { "b": 1, "c": 2 } ║    5 ║      -- 5 ObjectValue
╚═════╩════════════════════╩══════╝

 vs

╔═════╦═══════╦══════╗
║ key ║ value ║ type ║
╠═════╬═══════╬══════╣
║ b   ║     1 ║    2 ║                   -- 2 IntValue
║ c   ║     2 ║    2 ║                   -- 2 IntValue
╚═════╩═══════╩══════╝

Now you can check how path behaves '$.a' vs '$'.

---

From OPENJSON:

If you want to return a nested JSON fragment from a JSON property, you have to provide the AS JSON flag. Without this option, if the property can't be found, OPENJSON returns a NULL value instead of the referenced JSON object or array, or it returns a run-time error in strict mode .

So trying second with strict mode:

DECLARE @prmInputData NVARCHAR(MAX) = '{ "a": { "b": 1, "c": 2 } }';

SELECT *
FROM OPENJSON(@prmInputData, '$.a')
WITH  (
  b INT '$.b',
  c INT '$.c',
  a NVARCHAR(MAX) 'strict $' AS JSON
);

It will end up with error:

Property cannot be found on the specified JSON path.