如何检查键值对是否存在于Variant列？SQL循环JSON输入实现咨询

1. 如何检查一组键值对是否存在于Variant列中？ #

以Snowflake为例，可通过键路径访问结合值匹配的方式实现，也可先确认键存在再验证值：

• 直接匹配键值：

SELECT *
FROM my_table
WHERE 
  -- 字符串值加引号，数字/布尔值直接写
  my_variant:key1 = 'value1' 
  AND my_variant:key2 = 100;

• 先检查键存在，再匹配值：

SELECT *
FROM my_table
WHERE 
  EXISTS_PATH(my_variant, '$.key1') 
  AND GET(my_variant, 'key1') = 'value1'
  AND EXISTS_PATH(my_variant, '$.key2') 
  AND GET(my_variant, 'key2') = 100;

2. 能否编写SQL查询遍历JSON输入结构，检查键值对是否存在于Variant列中？ #

可以不用循环，利用SQL的横向展开（LATERAL FLATTEN）处理JSON输入的键值对，示例如下：
假设JSON输入为{"k1": "v1", "k2": "v2"}，可编写查询：

-- 定义JSON输入
WITH json_input AS (
  SELECT PARSE_JSON('{"k1": "v1", "k2": "v2"}') AS input_object
),
-- 把JSON拆成键值对行
key_value_list AS (
  SELECT
    f.key AS input_key,
    f.value AS input_value
  FROM json_input,
       LATERAL FLATTEN(INPUT => input_object, MODE => 'OBJECT') f
)
-- 关联表检查匹配情况
SELECT DISTINCT t.*
FROM my_table t
JOIN key_value_list kv
  ON EXISTS_PATH(t.my_variant, CONCAT('$.', kv.input_key))
  AND GET(t.my_variant, kv.input_key) = kv.input_value;

该方法会将输入的JSON对象拆分为每行一个键值对，逐个检查表中Variant列是否存在对应键且值匹配，最后用DISTINCT避免同一行被多次返回。

内容的提问来源于stack exchange，提问作者ays1