Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table "houses"
(
    "id"      number(10) not null,
    "address" varchar2(255) not null
);
create table "users"
(
    "id"       number(10) not null,
    "name"     varchar2(255) not null,
    "house_id" number(10) not null
);
create table "todos"
(
    "id"          number(10) not null,
    "description" varchar2(255) not null,
    "user_id"     number(10) not null
);
INSERT INTO "houses"
VALUES (1, '123 Main St');
1 rows affected

INSERT INTO "houses"
VALUES (2, '456 Ocean Ave');
1 rows affected

INSERT INTO "users"
VALUES (1, 'Alice', 1);
1 rows affected

INSERT INTO "users"
VALUES (2, 'Bob', 1);
1 rows affected

INSERT INTO "users"
VALUES (3, 'Carol', 2);
1 rows affected

INSERT INTO "todos"
VALUES (1, 'Buy milk', 1);
1 rows affected

INSERT INTO "todos"
VALUES (2, 'Buy eggs', 1);
1 rows affected

INSERT INTO "todos"
VALUES (3, 'Buy bread', 2);
1 rows affected

INSERT INTO "todos"
VALUES (4, 'Vacuum', 3);
1 rows affected

SELECT
    "houses"."id" AS "id",
    "houses"."address" AS "address",
    (
        SELECT
            JSON_ARRAYAGG(JSON_OBJECT(
                KEY 'id' VALUE "users"."id",
                KEY 'name' VALUE "users"."name",
                KEY 'todos' VALUE (
                    SELECT
                        JSON_ARRAYAGG(JSON_OBJECT(
                            KEY 'id' VALUE "todos"."id",
                            KEY 'description' VALUE "todos"."description"
                        ))
                    FROM
                        "todos"
                    WHERE
                        "todos"."user_id" = "users"."id"
                )
            ))
        FROM
            "users"
        WHERE
            "users"."house_id" = "houses"."id"
    ) "users"
FROM
    "houses"
GROUP BY
    "houses"."id",
    "houses"."address"


id
address
users


1
123 Main St
[{"id":1,"name":"Alice","todos":[{"id":1,"description":"Buy milk"},{"id":2,"description":"Buy eggs"}]},{"id":2,"name":"Bob","todos":[{"id":3,"description":"Buy bread"}]}]

2
456 Ocean Ave
[{"id":3,"name":"Carol","todos":[{"id":4,"description":"Vacuum"}]}]

id	address	users
1	123 Main St	[{"id":1,"name":"Alice","todos":[{"id":1,"description":"Buy milk"},{"id":2,"description":"Buy eggs"}]},{"id":2,"name":"Bob","todos":[{"id":3,"description":"Buy bread"}]}]
2	456 Ocean Ave	[{"id":3,"name":"Carol","todos":[{"id":4,"description":"Vacuum"}]}]