By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE "organisations"
AS
SELECT 1::integer "id",
'[{"id":23,"role":"admin"},{"id":24,"role":"default"}]'::jsonb "members";
SELECT 1
CREATE TABLE "users"
AS
SELECT 23::integer "id",
'Max'::text "name",
'max@gmail.com'::text "email"
UNION ALL
SELECT 24::integer "id",
'Joe'::text "name",
'joe@gmail.com'::text "email";
SELECT 2
SELECT jsonb_agg(jsonb_build_object('id', "u"."id",
'name', "u"."name",
'email', "u"."email",
'role', "m"."role"))
FROM "organisations" "o"
CROSS JOIN LATERAL jsonb_to_recordset(o."members") "m" ("id" integer,
"role" text)
INNER JOIN "users" "u"
ON "u"."id" = "m"."id";
| jsonb_agg |
|---|
| [{"id": 24, "name": "Joe", "role": "default", "email": "joe@gmail.com"}, {"id": 23, "name": "Max", "role": "admin", "email": "max@gmail.com"}] |
SELECT 1