Sql-server – How to detect whether a key is present in a JSON object using TSQL

jsonsql-server-2016t-sql

This is an interesting one.

Imagine I have a JSON object containing four key-value pairs where the keys are a, b, c and d.

Imagine this object is passed to a stored procedure and every key is optional (as long as there is at least 1 key in the object). That is, it is valid to pass '{"a":"1"}' even though b, c and d have been excluded.

The question is:

How can I perform a check, in the stored procedure, as to whether a particular key has been passed in?

If I attempt to access a key using json_value, and the key is absent, then in lax mode, I receive a null value; in strict mode, I receive an error. I suppose this is one way I can tell the difference: reference the key in strict mode and catch any errors – but I want to avoid using try-catch logic.

Below are some example calls:

declare @data nvarchar(max); set @data = N'{"a":"1","b":"","c":null}';
select [value] from openjson(@data);
select 'strict a', json_value(@data, 'strict $.a') union
select 'strict b', json_value(@data, 'strict $.b') union
select 'strict c', json_value(@data, 'strict $.c') union
-- select 'strict d', json_value(@data, 'strict $.d') union
select 'lax a', json_value(@data, 'lax $.a') union
select 'lax b', json_value(@data, 'lax $.b') union
select 'lax c', json_value(@data, 'lax $.c') union
select 'lax d', json_value(@data, 'lax $.d');

This shows:

  • A non-null string value is always returned "as is" (key 'a')
  • An empty string is always returned "as is" (key 'b')
  • A null is always returned "as is" (key 'c')
  • An absent key is returned as null in lax mode. If you uncomment the relevant line, attempting to reference an absent key in strict mode returns an error.

In other words, I can't distinguish between an absent key, and a key that has been supplied with a null value.

Why do I want to distinguish the difference between null being passed in, and a key being absent? If the key is present, its value should be persisted (whether null or not). I do not want to persist null in the event the key is absent. If the key is absent, I want to simply do nothing relating to that key (or the column where its value would be persisted).

My next approach was to wonder whether I could count the matching rows – perhaps rather than an error, the count function would return '0' as the count of rows matching on key 'd'.

This is where it gets interesting.

declare @data nvarchar(max); set @data = N'{"a":"1","b":"","c":null}';

with CTE as (select json_value(@data, 'strict $.a') as strictA)
select count(1) from CTE;

with CTE2 as (select json_value(@data, 'strict $.d') as strictD)
select count(1) from CTE2;

I was hoping to see the values 1 and 0. I was thinking it would either be that, or that it throws an error. The actual result is that both queries return a count of 1.

Why does the second query return '1'?

And do I have to resort to try-catch logic to detect whether a given key exists in a JSON fragment?

Best Answer

Try using OPENJSON instead. This returns a Type column that indicates a NULL value for a key. You can LEFT JOIN this to a source list of possible keys and check for a NULL Type value or a NULL return value to determine if the key is present or not. Example below:

declare @data nvarchar(max); set @data = N'{"a":"1","b":"","c":null}';

SELECT [Value],
    CASE 
        WHEN [type] IS NULL THEN 'Not Present'
        WHEN [type] = 0 THEN 'Null Value'
        ELSE 'Non-Null Value'
    END AS [KeyPresent]
FROM
(
    SELECT 'a' AS [key]
    UNION
    SELECT 'b' AS [key]
    UNION
    SELECT 'c' AS [key]
    UNION
    SELECT 'd' AS [key]
) keys
LEFT JOIN OPENJSON(@data) jdata ON keys.[key] = jdata.[key]
Related Question