如何用SQL验证动态键值对与键值列匹配性（SAP HANA）

通用思路 #

• 结构化输入：把API传入的动态键值对数组，通过HANA的UNNEST函数转换成行式表结构，每一行对应一个键值对（包含KEY和VALUE两个字段）。
• 关联校验：将转换后的输入表与存储允许键值对的表做左关联，匹配规则为键相同且值在对应键的允许范围内（支持键允许任意值的特殊场景）。
• 筛选无效项：左关联后，允许表中无匹配的记录即为无效键值对（表现为允许表的字段为NULL）。
• 格式化输出：将无效的键和值拼接成key:invalid_value的格式返回。

具体SQL实现 #

假设前提 #

• 允许列表表名：ALLOWED_KEY_VALUES，字段：
  • ALLOWED_KEY：存储允许的键（VARCHAR类型）
  • ALLOWED_VALUE：存储对应键允许的值（VARCHAR类型）
  • （可选）ALLOW_ALL_VALUES：布尔类型，标记该键是否允许任意值（默认FALSE）
• API传入的参数：input_kv_array，类型为ARRAY OF STRUCT(KEY VARCHAR(100), VALUE VARCHAR(200))

基础版（键值严格匹配） #

适用于每个键都有明确允许值列表的场景：

SELECT 
    CONCAT(kv."KEY", ':', kv."VALUE") AS INVALID_KV_PAIR
FROM UNNEST(:input_kv_array) AS kv("KEY", "VALUE")
LEFT JOIN ALLOWED_KEY_VALUES akv
    ON akv.ALLOWED_KEY = kv."KEY" 
    AND akv.ALLOWED_VALUE = kv."VALUE"
WHERE akv.ALLOWED_KEY IS NULL

进阶版（支持键允许任意值） #

如果存在某些键不需要限制值的情况，可通过ALLOW_ALL_VALUES字段处理：

SELECT 
    CONCAT(kv."KEY", ':', kv."VALUE") AS INVALID_KV_PAIR
FROM UNNEST(:input_kv_array) AS kv("KEY", "VALUE")
LEFT JOIN ALLOWED_KEY_VALUES akv
    ON akv.ALLOWED_KEY = kv."KEY" 
    AND (akv.ALLOW_ALL_VALUES = TRUE OR akv.ALLOWED_VALUE = kv."VALUE")
WHERE akv.ALLOWED_KEY IS NULL

说明 #

• UNNEST函数是HANA处理数组类型的核心函数，能将数组拆分为行数据。
• 左关联（LEFT JOIN）确保所有输入的键值对都被保留，无匹配的即为无效项。
• CONCAT函数用于拼接键和值，输出符合要求的格式。

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