3

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 Answer 1

2

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.

Sign up to request clarification or add additional context in comments.

1 Comment

Ah that's interesting, thank you. So is there any way to select multiple layers like I want in the same OPENJSON query?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.