Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 15.18 (Debian 15.18-1.pgdg12+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14+deb12u1) 12.2.0, 64-bit



SELECT 1


CREATE TABLE person
(
  id integer NOT NULL,
  name character varying(30),
  CONSTRAINT person_pk PRIMARY KEY (id)
);

CREATE TABLE car
(
  id integer NOT NULL,
  type character varying(30),
  personid integer,
  CONSTRAINT car_pk PRIMARY KEY (id)
);

CREATE TABLE wheel
(
  id integer NOT NULL,
  whichone character varying(30),
  serialnumber integer,
  carid integer,
  CONSTRAINT "Wheel_PK" PRIMARY KEY (id)
);

INSERT INTO person(id, name)
VALUES (1, 'Johny'),
       (2, 'Freddy');

INSERT INTO car(id, type, personid)
VALUES (1, 'Toyota', 1),
       (2, 'Fiat', 1),    
       (3, 'Opel', 2);     

INSERT INTO wheel(id, whichone, serialnumber, carid)
VALUES (1, 'front', '11', 1),
       (2, 'back', '12', 1),
       (3, 'front', '21', 2),
       (4, 'back', '22', 2),
       (5, 'front', '3', 3);

CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 2



INSERT 0 3



INSERT 0 5


SELECT * FROM car;

SELECT * FROM wheel;

SELECT * FROM person;


id
type
personid


1
Toyota
1

2
Fiat
1

3
Opel
2



SELECT 3




id
whichone
serialnumber
carid


1
front
11
1

2
back
12
1

3
front
21
2

4
back
22
2

5
front
3
3



SELECT 5




id
name


1
Johny

2
Freddy



SELECT 2


select json_build_object(
	'persons', (
       SELECT json_agg(
	       json_build_object(
        	'person_id',id,
       		'cars', (
       		 	SELECT json_agg(
       		    	json_build_object(
       		      		'car_id', car.id,
       		      		'wheels', (
       		         		SELECT json_agg(
	       		         	 	json_build_object(
	       		         			'wheel_id', wheel.id,
	       		         			'whichone', wheel.whichone,
	       		         			'serialnumber', wheel.serialnumber,
	       		         			'car_id', wheel.carid
	       		         	    )
       		         		)
       		        		FROM wheel WHERE wheel.carid = car.id
       		    		)    
       		        )
       		   ) FROM car WHERE id = person.id
       		)
       	  )
       ) FROM person
	) 
);


json_build_object


{"persons" : [{"person_id" : 1, "cars" : [{"car_id" : 1, "wheels" : [{"wheel_id" : 1, "whichone" : "front", "serialnumber" : 11, "car_id" : 1}, {"wheel_id" : 2, "whichone" : "back", "serialnumber" : 12, "car_id" : 1}]}]}, {"person_id" : 2, "cars" : [{"car_id" : 2, "wheels" : [{"wheel_id" : 3, "whichone" : "front", "serialnumber" : 21, "car_id" : 2}, {"wheel_id" : 4, "whichone" : "back", "serialnumber" : 22, "car_id" : 2}]}]}]}



SELECT 1