CrateDB中数组内同一对象的多值匹配查询问题

你的核心问题是要匹配数组内同一个对象同时满足key_id和value的条件，而不是数组中任意元素分别满足两个条件。原语句用ANY分别判断key_id和value，会导致跨对象匹配，可通过以下两种方法解决：

方法一：使用UNNEST展开数组并过滤 #

通过UNNEST将数组拆分为独立的对象行，过滤出符合条件的对象后，关联回原表并去重：

SELECT DISTINCT a.*
FROM test.artefact a
JOIN UNNEST(a.metadata) AS m
ON m.key_id = 2 AND m.value = 'TEST2';

• UNNEST(a.metadata)把数组中的每个对象拆成单独的行
• JOIN后通过ON条件筛选出同时满足key_id和value的对象
• DISTINCT避免原表记录因数组中有多个符合条件的对象而重复返回

方法二：直接使用数组元素条件判断（更简洁） #

CrateDB支持对数组元素进行lambda表达式判断，直接检查数组中是否存在满足双条件的对象：

SELECT *
FROM test.artefact
WHERE ANY(metadata) ? (x -> x['key_id'] = 2 AND x['value'] = 'TEST2');

• ANY(metadata) ? (x -> ...)表示检查数组中是否存在至少一个元素x，满足箭头后的条件
• 这个写法直接在原表上过滤，无需关联操作，性能更优

原语句出错原因 #

原语句1 = ANY(metadata['key_id']) AND 'TEST2' = ANY(metadata['value'])的逻辑是：

• 数组中存在任意一个对象的key_id=1
• 并且数组中存在任意一个对象的value='TEST2'
  这两个条件不需要来自同一个对象，所以会误匹配到你的测试数据。

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