如何基于相似度排序JSONB类型同义词数组后返回查询结果？

要实现先对同义词数组按搜索词相似度排序再返回结果，可通过在查询中对每个实体的同义词数组单独执行排序聚合操作来完成，以下是修改后的查询语句：

with names as (
      select b.id, b.name, b.synonyms,
             case
                 when b.id = :search then 0
                 else 1 - similarity(s.disease_name, :search)
                 end as distance
        from disease_registry b
        cross join lateral jsonb_array_elements_text(
                        b.synonyms || jsonb_build_array(b.name)
                ) as s(disease_name)
       where (similarity(s.disease_name, :search) > 0.2
           or b.id = :search
           or :search = '')
  )

select n.id, n.name,
       -- 根据搜索词相似度排序同义词数组，空搜索时返回原数组
       case when :search = '' then n.synonyms::text[]
            else (select array_agg(syn order by similarity(syn, :search) desc)
                  from jsonb_array_elements_text(n.synonyms) as syn)
       end as synonyms
  from names n
 group by n.id, n.name, n.synonyms
 order by (case
               when n.name ilike '%' || :search || '%'
                   then 0
               else min(n.distance)
     end),
          n.name

关键说明 #

1. 同义词数组排序：在最终查询的select阶段，通过子查询将原synonyms数组拆分为单个元素，计算每个元素与搜索词的相似度后，按相似度降序（匹配度最高的在前）重新聚合为新数组。当搜索词为空时，直接返回原数组避免无意义排序。
2. 原有逻辑保留：原有的实体匹配条件、结果排序逻辑完全保留，确保查询的筛选规则和整体结果排序行为与之前一致。

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