如何使用 TSQL 檢測 JSON 對像中是否存在鍵？

這是一個有趣的。

想像一下，我有一個 JSON 對象，其中包含四個鍵值對，其中鍵是 a、b、c 和 d。

想像一下這個對像被傳遞給一個儲存過程，並且每個鍵都是可選的（只要對像中至少有 1 個鍵）。也就是說，即使 b、c 和 d 已被排除，傳遞 ‘{“a”:“1”}’ 也是有效的。

問題是：

如何在儲存過程中檢查是否已傳入特定密鑰？

如果我嘗試使用 訪問密鑰json_value，但該密鑰不存在，那麼在寬鬆模式下，我會收到一個null值；在嚴格模式下，我收到一個錯誤。我想這是我可以區分的一種方法：在嚴格模式下引用密鑰並擷取任何錯誤 - 但我想避免使用 try-catch 邏輯。

以下是一些範例呼叫：

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');

由此可見：

• 始終“按原樣”返回非空字元串值（鍵 ‘a’）
• 始終“按原樣”返回空字元串（鍵 ‘b’）
• 始終“按原樣”返回 null（鍵 ‘c’）
• 在鬆散模式下，缺少的鍵返回為 null。如果您取消註釋相關行，則嘗試在嚴格模式下引用不存在的鍵會返回錯誤。

換句話說，我無法區分缺少的鍵和提供了空值的鍵。

為什麼我要區分傳入 null 和缺少鍵之間的區別？如果鍵存在，它的值應該被持久化（無論是否為空）。如果密鑰不存在，我不想堅持 null 。如果鍵不存在，我只想不做與該鍵（或將保留其值的列）相關的任何操作。

我的下一個方法是想知道我是否可以計算匹配的行數 - 也許不是錯誤，count 函式將返回 ‘0’ 作為鍵 ’d’ 上匹配的行數。

這就是有趣的地方。

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;

我希望看到值 1 和 0。我想要麼就是這樣，要麼會引發錯誤。實際結果是兩個查詢都返回計數 1。

為什麼第二個查詢返回“1”？

我是否必須求助於 try-catch 邏輯來檢測給定鍵是否存在於 JSON 片段中？

嘗試改用OPENJSON。這將返回一個 Type 列，該列指示鍵的 NULL 值。您可以將其 LEFT JOIN 加入可能鍵的源列表並檢查 NULL 類型值或 NULL 返回值以確定鍵是否存在。下面的例子：

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]

…實際上，只需再工作幾分鐘，我就找到了答案，即：

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

select 'checking a ', case when 'a' in (select [key] from openjson(@data)) then 'present' else 'absent' end union
select 'checking d', case when 'd' in (select [key] from openjson(@data)) then 'present' else 'absent' end;

只需選擇所有鍵，然後查看您想要的鍵是否在結果集中。

引用自：https://dba.stackexchange.com/questions/238528