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

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]

StackExchange Database Administrators Q#238528, answer score: 3