如何筛选TEXT列值存在于同行Array列中的数据行

需求：查询满足transit（TEXT类型）列的值存在于同一行transit_options（数组类型）列中的数据行。

示例表结构与数据 #

预期输出 #

各数据库实现SQL #

PostgreSQL #

PostgreSQL原生支持数组类型，可使用两种方式实现：

-- 方式1：使用ANY()函数
SELECT *
FROM your_table_name
WHERE transit = ANY(transit_options);

-- 方式2：使用@>数组包含操作符
SELECT *
FROM your_table_name
WHERE transit_options @> ARRAY[transit];

MySQL 8.0+ #

若transit_options为JSON数组类型，直接使用JSON_CONTAINS()：

SELECT *
FROM your_table_name
WHERE JSON_CONTAINS(transit_options, JSON_QUOTE(transit));

若transit_options是字符串格式的数组，先转换为JSON格式再判断：

SELECT *
FROM your_table_name
WHERE JSON_CONTAINS(REPLACE(REPLACE(transit_options, "'", '"'), '[', '["'), JSON_QUOTE(transit));

SQLite #

通过json_each()展开数组后匹配：

SELECT DISTINCT t.*
FROM your_table_name t
JOIN json_each(t.transit_options) j
WHERE j.value = t.transit;

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