我在表中有文本字段，我想使用 where 条件查询该字段：我想查询所有包含至少一个单词的记录作为单词列表并返回如下 JSON：
text
The employee was fired today
He likes chocolate a lot
She eat chocolate today
Car was stolen yesterday
select * from tbl
where text CONTAINS ANY ['today','likes','eat']
期望的输出 1：
{"id":"1", "text":"The employee was fired today", "tag":"today"}
{"id":"2", "text":"He likes chocolate a lot", "tag":"likes"}
{"id":"3", "text":"She eat chocolate today", "tag":["today","eat"]}
期望的输出 2：
text tag tag_counts
The employee was fired today today 1
He likes chocolate a lot likes 1
She eat chocolate today eat, today 2
我想获得这些输出中的任何一个。
我已经发现我可以使用WHERE IN ('today','likes','eat')，但如果可能的话，我不知道如何在任何所需的输出中获得结果。
我words为文本列选择了列名。“文本”是一个基本类型名称，因此过于混乱。
对于带有普通text列的给定表：
SELECT *
FROM tbl t
, LATERAL (
SELECT string_agg(tag, ', ') AS tags, count(*) AS tag_count
FROM (
SELECT unnest(string_to_array(words, ' ')) tag
INTERSECT ALL
SELECT unnest ('{today, likes, eat}'::text[])
) i
) ct
WHERE string_to_array(t.words, ' ') && '{today, likes, eat}';
在表格中以文本数组 ( text[]) 开头更简单：
SELECT *
FROM tbl1 t
, LATERAL (
SELECT string_agg(tag, ', ') AS tags, count(*) AS tag_count
FROM (
SELECT unnest(words) tag
INTERSECT ALL
SELECT unnest ('{today, likes, eat}'::text[])
) i
) ct
WHERE t.words && '{today, likes, eat}';
db<>在这里摆弄
可以支持GIN 索引。列的表达式索引text：
CREATE INDEX tbl_words_gin_idx ON tbl USING gin (string_to_array(words, ' '));
更简单，再一次，对于text[]：
CREATE INDEX tbl1_words_gin_idx ON tbl1 USING gin (words);
看：
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句