sql - 从表中选择数组并加入另一个表中的 jsonb 对象

Question

不确定这是怎么可能的，但我发现没有一个答案是遥不可及的。

假设我有表 A 和表 B。表 B 有一个 jsonb 列，其中键和值都是整数。例如{"10000":"12345678","10002":"12345680}

我想查询表 A 和 A 的结果，检查其中一个结果是否在该 json 对象中，并返回 val。这些值是一个 unix 时间戳，我想对其进行排序。

我已经尝试了各种 jsonb 函数，但没有一个与我正在尝试的相近

我尝试过类似的操作，但得到了一个错误。

SELECT a.from_id, b.*, info.id, info.date 
FROM table a, 
     table b, 
     jsonb_to_recordset(jsonb_each_text(b.mem_ids)) AS info(id int, date int) 
WHERE a.from_id = info.id 
ORDER BY info.date 
DESC LIMIT 1

我只获取它在与表 A 中的 id 匹配的 json 对象中找到的第一个结果。不确定如何使用 jsonb 列来完成。

更新:示例表

table a
|from_id | to_id |
 100001  | 100005
 100002  | 100005
 100003  | 100005


table b
|   id       |            mem_ids|
     1       | {"100075":"12345678","100003":"12345680}

所以我想做的是查询或某种连接，它将根据在案例 100005 中提供的 to_id 从表 a 中获取结果 from_id，并从这些结果中查看是否有 json obejct使用与其中一个 from_ids 匹配的键。如果是，则从匹配的键中返回该值。看起来有点复杂，但不幸的是，这就是现在的结构。

Answer 1

您可以使用子查询将 jsonb 列转换为表外观

select jsonb_object_keys(mem_ids) as id, mem_ids->>jsonb_object_keys(mem_ids) as date  
    from (
         select '{"10000":123,"10002":456}'::jsonb as mem_ids
         union 
         select '{"20000":987,"20002":159}'::jsonb as mem_ids
    ) as b;

当然你可以简单地通过 INNER JOIN 将它的结果与你的主表连接

select from_id, to_id, date from (
        select 100001 as from_id, 100005 as to_id
        union 
        select 100002 as from_id, 100005 as to_id
        union 
        select 100003 as from_id, 100005 as to_id) as a
    inner join  (
        select jsonb_object_keys(mem_ids)::int as id, mem_ids->>jsonb_object_keys(mem_ids) as date  from (
            select 1 as id, '{"100075":12345678,"100003":12345680}'::jsonb as mem_ids
            union 
            select 2 as id, '{"200075":987654321,"200003":1592648}'::jsonb as mem_ids
        ) as b
    ) as b on from_id = id
ORDER BY date desc
LIMIT 1;