postgresql 排序JSONB字符串数组

92dk7w1h 于 2023-02-22 发布在 PostgreSQL

关注(0)|答案(1)|浏览(185)

我有一列包含jsonb类型的字符串，例如：

["Stomach Mixed Adenocarcinoma", "Gastric Mixed Adenocarcinoma", "Mixed Breast Carcinoma"]

下面的查询将“search”作为参数，并根据与搜索模式的相似性返回实体。

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, n.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

现在我想根据与搜索模式的相似性对同义词数组进行排序，然后才返回结果。

postgresql

来源：https://stackoverflow.com/questions/75494944/sort-jsonb-array-of-strings

1条答案

按热度按时间

gkl3eglg1#

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,
           (
               select jsonb_agg(elem order by similarity(elem::text, :search) desc)
                 from jsonb_array_elements_text(n.synonyms) as elem
           ) 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;

这就是我的成就。

赞(0）回复(0）举报 2023-02-22

我来回答

postgresql 排序JSONB字符串数组

1条答案

相关问题

热门标签

最新问答