Jared's questions

我有一个“标题”列的记录，我按空格分割并用每个单词执行全文搜索。我将结果存储在物化视图中。

这行得通，但是我得到了各种单词的重复结果，我需要按它们的排名对结果进行排序。我可以做一个或另一个 - 不能两者兼而有之。我该怎么做？

我的查询：

SELECT
    asset.id,
    (
        select
            jsonb_agg(resultsForWord)
        FROM
            UNNEST(
                string_to_array(TRIM(regexp_replace(asset.title, '[^a-zA-Z+]', ' ', 'g')), ' ')
            ) as word
            INNER JOIN LATERAL 
            (
                SELECT
                    searchresult.id,
                    searchresult.title,
                    ts_rank(ts, to_tsquery ('english', word)) rank
                FROM
                    assets searchresult
                WHERE
                    searchresult.id != asset.id AND
                    ts_rank(ts, to_tsquery ('english', word)) > 0.5
                LIMIT 5
            ) AS resultsForWord ON 1=1
     ) results
FROM
    assets asset
WHERE asset.id = 'abc'
GROUP BY asset.id;

为了过滤掉我刚刚做的重复

jsonb_agg(DISTINCT resultsForWord)

按等级排序，我刚刚做了

jsonb_agg(resultsForWord ORDER BY rank DESC)

当我两者都做时，我得到：

ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list

示例数据：

CREATE TABLE assets (
  id TEXT PRIMARY KEY,
  title TEXT,
  ts tsvector 
   GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(title, '')), 'A')) STORED
)

INSERT INTO assets (id, title) VALUES ('a', 'Hello world!'),
  ('b', 'Hello sir'),
  ('c', 'I am above the world'),
  ('d', 'World hello')