PostgreSQL 13多列搜索向量创建：JSON/JSONB列及邮箱部分匹配需求

Step 1: Fix JSON/JSONB Value Extraction (Exclude Keys) #

Your current approach casts JSON/JSONB to text, which includes both keys and values. Use helper functions to extract only the values:

-- Extract all values from JSONB as space-separated text
CREATE OR REPLACE FUNCTION jsonb_values_to_text(j jsonb) RETURNS text AS $$
SELECT string_agg(value, ' ') FROM jsonb_each_text(j);
$$ LANGUAGE sql IMMUTABLE;

-- Extract all values from JSON as space-separated text
CREATE OR REPLACE FUNCTION json_values_to_text(j json) RETURNS text AS $$
SELECT string_agg(value, ' ') FROM json_each_text(j);
$$ LANGUAGE sql IMMUTABLE;

Update the search_vector column to use these functions:

ALTER TABLE company ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (
    to_tsvector(
        'french',
        name || ' ' || comment || ' ' || address || ' ' || postalCode || ' ' || cga || ' ' || manager || ' ' || franchise || ' ' ||
        coalesce(json_values_to_text(advantages), '') || ' ' ||
        coalesce(jsonb_values_to_text(contacts), '')
    )
) STORED;

Step 2: Enable Email Suffix Matching #

The default text search parser treats full emails as single tokens, so david@labyrinth.com is stored as one lexeme. To match suffixes like @labyrinth.com, choose one of these options:

Option 1: Include Domain Parts in the Search Vector #

Modify the jsonb_values_to_text function to add the email domain (including @) as a separate token:

CREATE OR REPLACE FUNCTION jsonb_values_to_text(j jsonb) RETURNS text AS $$
SELECT string_agg(
    CASE
        WHEN value ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
        THEN value || ' ' || replace(value, substring(value from '^.*@'), '@')
        ELSE value
    END,
    ' '
) FROM jsonb_each_text(j);
$$ LANGUAGE sql IMMUTABLE;

You can now search for the domain using:

SELECT * FROM company WHERE search_vector @@ to_tsquery('french', 'labyrinth.com');

Option 2: Combine tsvector with Direct JSONB Filter #

For exact suffix matches, use the tsvector for general search and filter the JSONB email field directly:

SELECT * FROM company
WHERE search_vector @@ to_tsquery('french', 'your_search_terms')
  AND contacts ->> 'email' LIKE '%@labyrinth.com';

Add an index to speed up the email filter:

CREATE INDEX idx_company_contacts_email ON company ((contacts ->> 'email'));

Option 3: Use Trigram Indexes for Flexible Partial Matches #

If you need partial matches across all columns, switch to a trigram GIN index:

ALTER TABLE company ADD COLUMN search_text text GENERATED ALWAYS AS (
    name || ' ' || comment || ' ' || address || ' ' || postalCode || ' ' || cga || ' ' || manager || ' ' || franchise || ' ' ||
    coalesce(json_values_to_text(advantages), '') || ' ' ||
    coalesce(jsonb_values_to_text(contacts), '')
) STORED;

CREATE INDEX idx_company_search_text_trgm ON company USING GIN (search_text gin_trgm_ops);

Search with:

SELECT * FROM company WHERE search_text LIKE '%@labyrinth.com%';

Final Index Setup #

Don't forget to add an index on the search_vector column for fast text searches:

CREATE INDEX idx_company_search_vector ON company USING GIN (search_vector);

内容的提问来源于stack exchange，提问作者MatMouth