Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.
    Tweeted twitter.com/StackDBAs/status/1006586067292753921
3 edited title; edited tags
| link

How can I implement full-text search on a single value in a JSONB column?

2 edited title
| link

How can I implement full-text search on a jsonbvalue in a JSONB column with Postgres?

    Post Migrated Here from stackoverflow.com
1
source | link

How can I implement full-text search on a jsonb column with Postgres?

I have a table that stores a conversation between two people.

The data will look something like this:

CREATE TABLE foo
AS
  SELECT $$[
    { "user": 1, "timestamp": 1, "message": "First message" },
    { "user": 2, "timestamp": 2, "message": "Second message" },
    { "user": 2, "timestamp": 3, "message": "Debounced message from same user" },
    { "user": 1, "timestamp": 4, "message": "Last message" }
  ]$$::jsonb AS jsondata;

I never need to look up each message individually, so I just want to store the whole conversation in a single jsonb field. I need to perform a full-text search across all of the messages.

My first thought was to create a new text column, concat all of the messages into one long string, and create a trigram GIN index on that column.

That seems like a hack that wastes a lot of space, so I would like to avoid the intermediate column. How can I create the index directly from the jsonb column?