How to detect whether a key is present in a JSON object using TSQL? 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?
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]