get postgresql中jsonb数组的并集和交集

nnsrf1az 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(600)

我有一个数据库的人与jsonb列 interests . 在我的应用程序中，用户可以通过提供一些预定义的值来搜索人们的爱好。我想给他一场最好的比赛，为了做到这一点，我想把这场比赛算作利益的交汇点/结合点。这样一来，我的数据库里就不会有那么多爱好的人了。例子：
数据库记录：

name    interests::jsonb
Mary    ["swimming","reading","jogging"]
John    ["climbing","reading"]
Ann     ["swimming","watching TV","programming"]
Carl    ["knitting"]

应用程序中的用户输入：

["reading", "swimming", "knitting", "cars"]

我的脚本应该输出以下内容：

Mary    0.4
John    0.2
Ann     0.16667
Carl    0.25

现在我在用

SELECT name 
  FROM people 
 WHERE interests @> 
   ANY (ARRAY ['"reading"', '"swimming"', '"knitting"', '"cars"']::jsonb[])

但这给了我很多兴趣，甚至没有办法订购它的记录。有没有什么方法可以在合理的时间内实现这一点-比如说在数据库中最多5秒，大约有40万条记录？
编辑：我添加了另一个例子来澄清我的计算。我的计算需要过滤有很多爱好的人。因此，匹配应计算为交集（input，db\u记录）/并集（input，db\u记录）。
示例：输入= ["reading"] 数据库记录：

name    interests::jsonb
Mary    ["swimming","reading","jogging"]
John    ["climbing","reading"]
Ann     ["swimming","watching TV","programming"]
Carl    ["reading"]

玛丽的匹配将被计算为 (LENGTH(["reading"]))/(LENGTH(["swimming","reading","jogging"])) 对卡尔来说是0.3333 (LENGTH(["reading"]))/LENGTH([("reading")]) 哪个是1
更新：我设法做到了这一点

SELECT result.id, result.name, result.overlap_count/(jsonb_array_length(persons.interests) + 4 - result.overlap_count)::decimal as score 
FROM (SELECT t1.name as name, t1.id, COUNT(t1.name) as overlap_count
      FROM (SELECT name, id, jsonb_array_elements(interests)
            FROM persons) as t1
      JOIN (SELECT unnest(ARRAY ['"reading"', '"swimming"', '"knitting"', '"cars"'])::jsonb as elements) as t2 ON t1.jsonb_array_elements = t2.elements 
      GROUP BY t1.name, t1.id) as result 
JOIN persons ON result.id = persons.id ORDER BY score desc

这是我的小提琴https://dbfiddle.uk/?rdbms=postgres_12&fiddle=b4b1760854b2d77a1c7e6011d074a1a3
但是速度不够快，如果有任何改进我都会很感激。

sql postgresql union Arrays Jsonb

来源：https://stackoverflow.com/questions/62497561/get-union-and-intersection-of-jsonb-array-in-postgresql

2条答案

按热度按时间

pb3s4cty1#

一个选项是取消对参数的声明并使用 ? 操作员检查每个元件 jsonb 数组：

select
    t.name,
    x.match_ratio
from mytable t
cross join lateral (
    select avg( (t.interests ? a.val)::int ) match_ratio
    from unnest(array['reading', 'swimming', 'knitting', 'cars']) a(val)
) x

你所展示的结果背后的规则不是很清楚。这将为您提供一个比率，该比率表示可以在中找到的参数数组中的值的百分比 interests 每个人的（所以mary得到0.5，因为她有两个与search参数相同的兴趣，所有其他名字都得到0.25）。
db小提琴演示

赞(0）回复(0）举报 2021-08-09

yc0p9oo02#

一种选择是使用 jsonb_array_elements() 要取消jsonb列的嵌套，请执行以下操作：

SELECT name, count / SUM(count) over () AS ratio
  FROM(
       SELECT name, COUNT(name) AS count
         FROM people 
         JOIN jsonb_array_elements(interests) AS j(elm) ON TRUE
        WHERE interests @> 
          ANY (ARRAY ['"reading"', '"swimming"', '"knitting"', '"cars"']::jsonb[])
        GROUP BY name ) q

演示

赞(0）回复(0）举报 2021-08-09

我来回答

get postgresql中jsonb数组的并集和交集

2条答案

相关问题

热门标签

最新问答