如何将两个jsonb对象与ids组合在一个select语句中？

Question

我对PostgresSQL上的jsonb操作很陌生。我的数据库中有两个表，一个包含一个带有另一个表it的json，嵌套在其中：

Table A
id | content
1  | { oneKey: "something", params: {anotherKey: "something", tableBEntries: [1,2]}}
2  | { oneKey: "something2", params: {anotherKey: "something2", tableBEntries: [3]}}
...

Table B
id | content
1  | {someKeysB: "values", anotherKeyB: "values"}
2  | {someKeysB: "values2", anotherKeyB: "values2"}
3  | {someKeysB: "values3", anotherKeyB: "values3"}

我希望从表A中选择条目，并将它们与表B结合起来，这样结果如下：

Result
id | content
1  | { oneKey: "something", params: {anotherKey: "something", tableBEntries: [{someKeysB: "values", anotherKeyB: "values"}, {someKeysB: "values2", anotherKeyB: "values2"}]}}
2  | { oneKey: "something2", params: {anotherKey: "something2", tableBEntries: [{someKeysB: "values3", anotherKeyB: "values3"}]}}

但是，我找不到一种方法，在一个简单的select statement...only上找到了提取tableBEntries的方法。

select jsonb_array_elements(tableA.content->'params'->'tableBEntries')

谢谢你的帮助！

Answer 1

step-by-step demo:db<>fiddle

SELECT
    jsonb_set(content, ARRAY['params','tableBEntries'], agg)                         -- 4
FROM (
    SELECT 
        a.*,
        jsonb_agg(b.content) AS agg                                                  -- 3
    FROM
        a,
        jsonb_array_elements_text(content -> 'params' -> 'tableBEntries') as entries -- 1
    JOIN                                                                             -- 2
        b ON entries.value::int = b.id
    GROUP BY a.id, a.content 
) s

1. 将数组展开为一行每个元素。
2. 将这些元素与id从b连接起来
3. 相关a记录的b组内容
4. 用聚合的b值替换tableBEntries元素