postgresql 选择超过4个表中嵌套的JSON数组

我有四张table，一张与另一张相关。模式如下所示：

CREATE SCHEMA vehicles;

CREATE TABLE vehicles.car (
    id serial,
    name text,
    description text,

  CONSTRAINT car_pk PRIMARY KEY(id)
);

CREATE TABLE vehicles.car_door (
     id serial,
    name text,    
    car_id serial,

  CONSTRAINT car_door_pk PRIMARY KEY(id),
    CONSTRAINT car_id_fk FOREIGN KEY (car_id) REFERENCES vehicles.car(id) ON DELETE CASCADE
);

CREATE TABLE vehicles.car_door_handle (
   id serial,
    name text,
   car_door_id serial,

  CONSTRAINT car_door_handle_pk PRIMARY KEY(id),
   CONSTRAINT car_door_id_fk FOREIGN KEY (car_door_id) REFERENCES vehicles.car_door(id) ON DELETE     CASCADE

);

CREATE TABLE vehicles.car_door_handle_screw(
    id serial,
    name text,
    car_door_handle_id serial,

  CONSTRAINT  car_door_handle_screw_pk PRIMARY KEY(id),
    CONSTRAINT car_door_handle_id_fk FOREIGN KEY (car_door_handle_id) REFERENCES     vehicles.car_door_handle(id) ON DELETE CASCADE
);

INSERT INTO vehicles.car (name) VALUES ('car 1');
INSERT INTO vehicles.car (name) VALUES ('car 2');
INSERT INTO vehicles.car (name) VALUES ('car 3');
INSERT INTO vehicles.car (name) VALUES ('car 4');

INSERT INTO vehicles.car_door (name, car_id ) VALUES ('car door 1', 1);
INSERT INTO vehicles.car_door (name, car_id ) VALUES ('car door 2', 1);
INSERT INTO vehicles.car_door (name, car_id ) VALUES ('car door 3', 2);
INSERT INTO vehicles.car_door (name, car_id ) VALUES ('car door 4', 3);
INSERT INTO vehicles.car_door (name, car_id ) VALUES ('car door 6', 4);
INSERT INTO vehicles.car_door (name, car_id ) VALUES ('car door 7', 4);

INSERT INTO vehicles.car_door_handle (name, car_door_id ) VALUES ('car door handle 1', 1);
INSERT INTO vehicles.car_door_handle (name, car_door_id ) VALUES ('car door handle 2', 1);
INSERT INTO vehicles.car_door_handle (name, car_door_id ) VALUES ('car door handle 3', 2);
INSERT INTO vehicles.car_door_handle (name, car_door_id ) VALUES ('car door handle 4', 2);
INSERT INTO vehicles.car_door_handle (name, car_door_id ) VALUES ('car door handle 5', 2);
INSERT INTO vehicles.car_door_handle (name, car_door_id ) VALUES ('car door handle 6', 3);
INSERT INTO vehicles.car_door_handle (name, car_door_id ) VALUES ('car door handle 7', 4);
INSERT INTO vehicles.car_door_handle (name, car_door_id ) VALUES ('car door handle 8', 5);

INSERT INTO vehicles.car_door_handle_screw (name, car_door_handle_id ) VALUES ('car handle screw 1',     1);
INSERT INTO vehicles.car_door_handle_screw (name, car_door_handle_id ) VALUES ('car handle screw 2', 1);
INSERT INTO vehicles.car_door_handle_screw (name, car_door_handle_id ) VALUES ('car handle screw 3',  2);
INSERT INTO vehicles.car_door_handle_screw (name, car_door_handle_id ) VALUES ('car handle screw 4', 2);
INSERT INTO vehicles.car_door_handle_screw (name, car_door_handle_id ) VALUES ('car handle screw 5', 3);
INSERT INTO vehicles.car_door_handle_screw (name, car_door_handle_id ) VALUES ('car handle screw 6', 4);
INSERT INTO vehicles.car_door_handle_screw (name, car_door_handle_id ) VALUES ('car handle screw 7', 5);

字符串
现在我想创建一个select语句，它将利用jsonb_agg等工具返回所有嵌套数据，从汽车到汽车把手。
我已经有了一个在汽车内部嵌套car_doors的查询：

select pt.id , pt.name,   jsonb_agg(row_to_json(tlt.*)::jsonb -     'car_id') as     car_doors from vehicles.car pt JOIN vehicles.car_door tlt ON pt.id=tlt.car_id GROUP BY pt.id,     pt.name;

型
我需要升级这个查询，以便包含所有表。感谢任何帮助。谢谢这里是db-fiddle：https://www.db-fiddle.com/f/8JVTZNvHaMYx7erwDyWARB/4
我尝试了一个简单的1级嵌套查询，它工作得很好。我需要将查询的深度一直扩展到car_door_handle_screw。

使用以下模式从内到外构建它：

with cdh_screw_j as (
  select car_door_handle_id, to_jsonb(t) as car_door_handle_screw
    from vehicles.car_door_handle_screw t
), cd_handle_raw as (
  select cdh.id, cdh.car_door_id, cdh.name, jsonb_agg(cdhs.car_door_handle_screw) as car_door_handle_screws
    from vehicles.car_door_handle cdh
         left join cdh_screw_j cdhs on cdhs.car_door_handle_id = cdh.id
   group by cdh.id, cdh.car_door_id, cdh.name
), cd_handle_j as (
  select car_door_id, to_jsonb(t) as car_door_handle
    from cd_handle_raw t
), c_door_raw as (
  select cd.id, cd.name, cd.car_id, jsonb_agg(cdh.car_door_handle) as car_door_handles
    from vehicles.car_door cd
         left join cd_handle_j cdh on cdh.car_door_id = cd.id
   group by cd.id, cd.name, cd.car_id
), c_door_j as (
  select car_id, to_jsonb(t) as car_door
    from c_door_raw t
), car_raw as (
  select c.id, c.name, c.description, jsonb_agg(cd.car_door) as car_doors
    from vehicles.car c
         left join c_door_j cd on cd.car_id = c.id
   group by c.id, c.name, c.description
)
select to_jsonb(car_raw) as result
  from car_raw;

字符串
更新fiddle

postgresql 选择超过4个表中嵌套的JSON数组

1条答案

相关问题

热门标签

最新问答