如何加入嵌套的jsonb数组元素?

本文介绍了如何加入嵌套的jsonb数组元素?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的问题与此类似:如何在Postgres中加入jsonb数组元素?

但是我需要填写一些嵌套数组.为简单起见，我只有1个表:

But I have some nested arrays I need to fill in. To keep it simple I only have 1 table:

CREATE table tester( id int, name text, d jsonb ) INSERT INTO tester(id, name, d) VALUES ('1', 'bob', '[ { "employees": [{"id":2},{"id":3},{"id":4}], "coworkers": [{"id":5},{"id":6}] }, { "employees": [{"id":3},{"id":4}], "coworkers": [{"id":5}] } ]'::jsonb), ('2', 'barb', '[ { "employees": [{"id":3}], "coworkers": [] }, { "employees": [{"id":3},{"id":4}], "coworkers": [{"id":5, "id":3}] } ]'::jsonb), ('3', 'ann', '[]'::jsonb), ('4', 'jeff', '[]'::jsonb), ('5', 'rachel', '[]'::jsonb), ('6', 'ryan', '[]'::jsonb);

请参阅: sqlfiddle/#!17/7c7ef/12/0

我试图将名称简单地添加到每个同事和雇员中，以便鲍勃看起来像这样:

I am trying to add simply the name to each of the coworkers and employees so that bob would look like:

[ { "employees": [{"id":2, "name":"barb"},{"id":3, "name":"ann"},{"id":4, "jeff"}], "coworkers": [{"id":5, "name":"rachel"},{"id":6, "name":"ryan"}] }, { "employees": [{"id":3, "name":"ann"},{"id":4, "name":"jeff"}], "coworkers": [{"id":5, "name":"rachel"}] } ]

到目前为止，我有:

SELECT c.person person FROM tester LEFT JOIN LATERAL( SELECT jsonb_agg( jsonb_build_object( 'employees', c.wrk->'employees', 'coworkers', c.wrk->'coworkers' ) ) AS person FROM jsonb_array_elements(tester.d) AS c(wrk) ) c ON true

除了名称外，哪个都将返回:

Which returns everything but the names:

[{"coworkers": [{"id": 5}, {"id": 6}], "employees": [{"id": 2}, {"id": 3}, {"id": 4}]}, {"coworkers": [{"id": 5}], "employees": [{"id": 3}, {"id": 4}]}] [{"coworkers": [], "employees": [{"id": 3}]}, {"coworkers": [{"id": 3}], "employees": [{"id": 3}, {"id": 4}]}] (null) (null) (null) (null)

请注意对象列表:它们是单独的对象，而不仅仅是一个大对象.

Please take note of the list of objects: they are separate objects and not just one big object.

(null)"是空白数组"[]".

The "(null)" s/b a blank array "[]".

推荐答案

假设 tester.id 是PK，以简化聚合:

Assuming that tester.id is the PK, to simplify the aggregation:

SELECT t.id, t.name, COALESCE(t1.d, t.d) FROM tester t LEFT JOIN LATERAL ( SELECT jsonb_agg(jsonb_build_object('coworkers', COALESCE(c.coworkers, jsonb '[]')) || jsonb_build_object('employees', COALESCE(e.employees, jsonb '[]'))) AS d FROM jsonb_array_elements(t.d) AS d1(p) CROSS JOIN LATERAL ( SELECT jsonb_agg(p.id || jsonb_build_object('name', n.name)) AS coworkers FROM jsonb_array_elements(d1.p ->'coworkers') AS p(id) LEFT JOIN tester n ON n.id = (p.id->>'id')::int ) c CROSS JOIN LATERAL ( SELECT jsonb_agg(p.id || jsonb_build_object('name', n.name)) AS employees FROM jsonb_array_elements(d1.p ->'employees') AS p(id) LEFT JOIN tester n ON n.id = (p.id->>'id')::int ) e GROUP BY t.id ) t1 ON t.d <> '[]';

SQL小提琴.

解释很像您在我的旧答案中所引用的:

Explanation is much like in my old answer you referenced:

• 如何在Postgres中加入jsonb数组元素?

一个特殊的困难是保留空的JSON数组'[]'，在该数组中聚合将返回NULL值，我通过策略性地使用了 COALESCE()来解决了这个问题.

One special difficulty is to retain the empty JSON array '[]' where the aggregation would returns NULL values, I solved this with the strategic use of COALESCE().

另一种是您希望将嵌套数组分开.通过将未嵌套的数组重新聚合回JSON数组，在针对同事和员工的两个单独的 LATERAL 连接中解决了该问题.

Another one is that you want to keep nested arrays apart. Solved that with aggregating unnested arrays right back into JSON arrays, in two separate LATERAL joins for coworkers and employees.

请注意数据中的倒钩陷阱:同事":[{"id":5，"id":3}]

Note the trap in your data for barb: "coworkers": [{"id":5, "id":3}]

SELECT jsonb'[{"id":5，"id":3}]'产生'[{"id":3}]'.也许您是想写'[{"id":5}，{"id":3}]'?

SELECT jsonb '[{"id":5, "id":3}]' results in '[{"id": 3}]'. Maybe you meant to write '[{"id":5}, {"id":3}]'?