如何加入嵌套的jsonb数组元素? [英] How to join nested jsonb array elements?

问题描述

我的问题与此类似:
如何在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);

请参阅: http://sqlfiddle.com/#!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}]'?

这篇关于如何加入嵌套的jsonb数组元素?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！