Oracle 19C中如何实现无序JSON数组匹配的SQL查询

针对myTable表中data字段（varchar2类型存储JSON数组），要查询元素与指定参数完全匹配（忽略顺序）的记录，可以通过以下SQL实现：

场景1：参数无重复（如匹配元素a、b） #

SELECT t.*
FROM myTable t
WHERE 
  -- 确保数组元素数量与指定参数数量一致
  JSON_VALUE(t.data, '$.size()') = 2
  -- 数组中不存在指定参数外的元素
  AND NOT EXISTS (
    SELECT 1
    FROM JSON_TABLE(t.data, '$[*]' COLUMNS elem VARCHAR2(100) PATH '$') jt
    WHERE jt.elem NOT IN ('a', 'b')
  )
  -- 指定参数的所有元素都存在于数组中
  AND (
    SELECT COUNT(DISTINCT jt.elem)
    FROM JSON_TABLE(t.data, '$[*]' COLUMNS elem VARCHAR2(100) PATH '$') jt
    WHERE jt.elem IN ('a', 'b')
  ) = 2;

场景2：参数有重复（如匹配两个a、一个b） #

如果需要匹配包含重复元素的数组，需统计每个元素的出现次数：

SELECT t.*
FROM myTable t
WHERE 
  JSON_VALUE(t.data, '$.size()') = 3
  -- 统计数组中a的数量等于指定次数
  AND (
    SELECT COUNT(*)
    FROM JSON_TABLE(t.data, '$[*]' COLUMNS elem VARCHAR2(100) PATH '$') jt
    WHERE jt.elem = 'a'
  ) = 2
  -- 统计数组中b的数量等于指定次数
  AND (
    SELECT COUNT(*)
    FROM JSON_TABLE(t.data, '$[*]' COLUMNS elem VARCHAR2(100) PATH '$') jt
    WHERE jt.elem = 'b'
  ) = 1;

额外说明 #

• 若需过滤非法JSON格式的记录，可在WHERE条件中添加JSON_VALID(t.data) = 1
• JSON_TABLE用于将JSON数组拆分为行数据，方便进行元素级别的校验
• 调整VARCHAR2(100)为实际业务中元素的最大长度

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