如何作为JSON获得后Gresql查询结果

Question

我在表格中有文本字段，我想使用条件的地方查询此字段：​​我想查询所有至少一个单词as as as s sords as s of单词并返回JSON的记录，并返回了这样的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']

Guestered输出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"]}

Desidered输出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

我想获得这些输出中的任何一个。

我已经发现我可以使用在其中（'今天'，'likes'，'eat'），但如果可能的话，我找不到如何在任何所需的输出中获得结果。

Answer 1

我选择了文本列的列名单词。 “文本”是一个基本的类型名称，因此太令人困惑了。

对于带有平原 文本 列的给定表格：

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＆lt;＆gt; fiddle

可以用 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);

See：

• 查询和按json数组中的匹配数进行查询