针对数组中的值的SELECT子句

Question

我有一个包含列区域的表

create unlogged table dump
(
id                                  varchar not null,
regions                             citext default '[]'::text,
);

需要对返回包含印度和英国的所有值的表执行搜索查询

行可以包含任何数量的值，我需要印度和英国都存在的区域数组的行

使用选择查询的样本输出

SELECT regions from test_dump where regions IS NOT NULL

{"pennsylvania, united states","michigan, united states"}
{}
{"new york, united states"}
{"act, australia"}
{}
{"vermont, united states"}
{"vermont, united states"}
{"ontario, canada"}
{"colorado, united states","ontario, canada"}
{"kwazulu-natal, south africa","new south wales, australia"}
{"kwazulu-natal, south africa","new south wales, australia"}
{"greater london, united kingdom","surrey, united kingdom"}
{"madrid, spain"}
{"illinois, united states"}
{}
{"punjab, india","ontario, canada"}
{"veneto, italy"}
{"maharashtra, india"}
{"midtjylland, denmark"}
{"ohio, united states","indiana, united states"}
{"massachusetts, united states"}

Answer 1

使用unest((函数将数组扩展到一个行集：

SELECT *
FROM <table>
WHERE
EXISTS
( SELECT 1
FROM unnest(<value_column>)
AS a(value)
WHERE a.value ilike '%india'     
)
AND
EXISTS
( SELECT 1
FROM unnest(<value_column>)
AS a(value)
WHERE a.value ilike '%united kingdom'     
)

Answer 2

替代解决方案：

更新以将数据包含在@smvenk的注释中，并更改为查询地址。

select id, varchar_array from array_test ;
id |                     varchar_array                     
----+-------------------------------------------------------
1 | {"florida, united states"}
2 | {"texas, united states"}
3 | {"ohio, united states","north dakota, united states"}
4 | {"london, united kingdom","mumbai, india"}
5 | {"indiana, united states","wales, united kingdom"}

SELECT
id,
array_val
FROM
array_test,
array_to_string(varchar_array, ',') AS un (array_val)
WHERE
array_val ILIKE '%, india%'
AND array_val ILIKE '%, united kingdom%';
id |              array_val               
----+--------------------------------------
4 | london, united kingdom,mumbai, india

从这里开始阵列功能：

array_to_string(数组anyarray，分隔符text[，null_string text](→文本

将每个数组元素转换为其文本表示形式，并连接由分隔符字符串分隔的元素。如果给定了null_string并且它不是null，那么null数组项由该字符串表示；否则，它们被省略。

array_to_string(array[1,2,3，NULL，5]，'，'，''(→1,2,3，，5

这将所有单独的值拉入一个字符串中，然后搜索india和united kingdom。