Postgres 9.6

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

CREATE TABLE organisations(id int PRIMARY KEY, data JSONb);

CREATE TABLE


INSERT INTO organisations(id, data) VALUES
  (1, '[{"id":23,"role":"admin"}, {"id":24,"role":"admin"}]'),
  (2, '[{"id":24,"role":"user"}]')
;

INSERT 0 2


create table users (id int, name text, email text);

CREATE TABLE


insert into users values
  (23, 'Max', 'max@gmail.com'),
  (24, 'Joe', 'joe@gmail.com')
;

INSERT 0 2


SELECT o.id, u.*
FROM organisations o
INNER JOIN LATERAL JSONB_ARRAY_ELEMENTS(o.data) AS e(usr) ON TRUE
INNER JOIN users u ON (e.usr->'id')::text::int = u.id


id
id
name
email


2
24
Joe
joe@gmail.com

1
24
Joe
joe@gmail.com

1
23
Max
max@gmail.com



SELECT 3


SELECT o.id,  
JSON_AGG(
    JSON_BUILD_OBJECT('id', u.id, 'name', u.name, 'email', u.email, 'role', e.usr->'role')
)
FROM organisations o
CROSS JOIN LATERAL JSONB_ARRAY_ELEMENTS(o.data) AS e(usr)
INNER JOIN users u ON (e.usr->'id')::text::int = u.id
GROUP BY o.id


id
json_agg


1
[{"id" : 23, "name" : "Max", "email" : "max@gmail.com", "role" : "admin"}, {"id" : 24, "name" : "Joe", "email" : "joe@gmail.com", "role" : "admin"}]

2
[{"id" : 24, "name" : "Joe", "email" : "joe@gmail.com", "role" : "user"}]



SELECT 2

id	json_agg
1	[{"id" : 23, "name" : "Max", "email" : "max@gmail.com", "role" : "admin"}, {"id" : 24, "name" : "Joe", "email" : "joe@gmail.com", "role" : "admin"}]
2	[{"id" : 24, "name" : "Joe", "email" : "joe@gmail.com", "role" : "user"}]