sql - 从 postgres 查询构建 JSON 对象层次结构。这个对吗？

Question

我不知道如何从 json 查询中获取分层对象。

我的样本数据是：

CREATE TABLE
    "postgres"."public"."owner"
    (
        owner_id INTEGER,
        name text,
        mail text
    )
;

insert into owner (owner_id, name, mail) values
  (1, 'Owner1', '[e-mail]'),
  (2, 'Owner2', '[e-mail]')
;

CREATE TABLE
    "postgres"."public".ip
    (
        owner_id INTEGER,
        status text,
        ip inet,
        host text
    )
;
insert into ip (owner_id, status, ip, host) values
  (1, 'active', '12.34.56.78', 'host78.owner1.do.main'),
  (1, 'active', '12.34.56.79', 'host79.owner1.do.main'),
  (1, 'active', '12.34.56.80', 'host80.owner1.do.main'),
  (1, 'tests failed', '12.34.56.81', 'host81.owner1.do.main'),
  (1, 'tests failed', '12.34.56.82', 'host82.owner1.do.main'),
  (2, 'active', '34.56.78.1', 'host1.owner2.do.main'),
  (2, 'active', '34.56.78.2', 'host2.owner2.do.main'),
  (2, 'not listed', '34.56.78.3', 'host3.owner2.do.main'),
  (2, 'not listed', '34.56.78.4', 'host4.owner2.do.main')
;

我的预期结构是：

{
  "Owner1": {
    "mail": "[e-mail]",
    "ips": {
      "active": {
        "12.34.56.78": "host78.owner1.do.main",
        "12.34.56.79": "host79.owner1.do.main",
        "12.34.56.80": "host80.owner1.do.main"
      },
      "tests failed": {
        "12.34.56.81": "host81.owner1.do.main",
        "12.34.56.82": "host82.owner1.do.main"
      }
    }
  },
  "Owner2": {
    "mail": "[e-mail]",
    "ips": {
        "active": {
          "34.56.78.1": "host1.owner2.do.main",
          "34.56.78.2": "host2.owner2.do.main"
        },
        "not listed": {
          "34.56.78.3": "host3.owner2.do.main",
          "34.56.78.4": "host4.owner2.do.main"
        }
    }
  }
}

经过大量尝试，我想出了这个查询：

select json_object_agg(name, status_ips)
from (
        select name, json_build_object('mail', mail, 'ips', json_object_agg(status, ips)) as status_ips
        from (
                select owner_id, status, json_object_agg(ip, host) as ips
                from ip
                group by owner_id, status
        ) IPS
        join owner
        using (owner_id)
        group by name, mail
) OWNER_IPS;

由于我有 2 个子查询，我的问题是：这是否过于复杂？有没有更好的方法来获得我的结果？

标签： sqljsonpostgresql