How to get a postgresql query result as JSON

Question

I have text field in a table and I want query this field using where a condition: I want to query all records that has at least one word from as list of words and returns a JSON like this:

       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']

Desidered Output 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 Output 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

I would like to get any of these outputs.

I already found that I can use WHERE IN ('today','likes','eat') but I can't find out how to get the result in any of the desired output, if possible.

Please *always* start by declaring your version of Postgres. Find out with `SELECT version();` You want to match on exact words? No stemming, case-sensitive, no punctuation? Are you at liberty to transform `"text"` to `text[]` (type array of text)? — Erwin Brandstetter, Jun 09 '22 at 23:22

Erwin Brandstetter · Accepted Answer · 2022-06-09T23:55:09.020

I chose the column name words for the text column. "text" is a basic type name and too confusing as such.

For your given table with a plain text column:

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}';

Simpler with a text array (text[]) in the table to begin with:

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<>fiddle here

Can be supported with a GIN index. An expression index for the text column:

CREATE INDEX tbl_words_gin_idx ON tbl USING gin (string_to_array(words, ' '));

Simpler, yet again, for text[]:

CREATE INDEX tbl1_words_gin_idx ON tbl1 USING gin (words);

See:

Query and order by number of matches in JSON array

How to get a postgresql query result as JSON

1 Answers1