时间戳

首页 > 解决方案 > SQL：给定一个字符串数组，过滤字符串列是否部分包含在该数组中

sql - SQL：给定一个字符串数组，过滤字符串列是否部分包含在该数组中

问题描述

表架构

Tags
----
["foo"]
["foo2", "bar"]
["bar", "foo_bar"]
["bar"]

该Tags列的内容是一个 json 字符串。

假设数组是["foo", "foo_bar"]，这个数组中包含的任何标签都需要被过滤掉，所以预期的输出将是

Tags
----
["foo2", "bar"]
["bar"]

我目前使用的查询语句类似如下

SELECT tags
FROM table
WHERE (
    -- Translate the array into cases
    CASE
        WHEN tags like '%"foo"%' THEN false  -- filter out "foo"
        WHEN tags like '%"foo_bar"%' THEN false -- filter out "foo_bar"
        ...  -- filter out others in the given array
        ELSE true
    END
)

我想知道是否有更有效的方法来做到这一点？

标签： sqlsqlite

解决方案

如果将数组作为 json 字符串传递，则可以使用JSON1 扩展函数和聚合与HAVING子句中的条件：

WITH cte(tag) AS (SELECT json_each.value FROM json_each('["foo", "foo_bar"]'))
SELECT t.tags
FROM tablename t, json_each(tags)
GROUP BY t.tags
HAVING SUM(json_each.value IN cte) = 0

见演示。

推荐阅读