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.