Oracle查询JSON整数数组列：筛选数组包含指定元素的行

解决方案 #

Oracle 12c及以上版本原生支持JSON查询操作，你可以使用JSON_EXISTS函数实现数组元素存在性校验，对应的查询语句如下：

SELECT * 
FROM orders
WHERE JSON_EXISTS(products, '$[*]?(@ == 8)');

语法说明 #

• $[*] 表示遍历products列存储的JSON数组中所有元素
• ?(@ == 8) 是过滤规则，其中@指代当前遍历到的数组元素，当存在任意元素值等于8时，JSON_EXISTS返回真值，该行会被筛选出来

如果你需要同时对数组元素做更多处理，也可以使用JSON_TABLE将数组拆分为行再过滤：

SELECT DISTINCT o.*
FROM orders o,
JSON_TABLE(o.products, '$[*]' COLUMNS (product_val NUMBER PATH '$')) jt
WHERE jt.product_val = 8;

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