There is such a request.

SELECT memberships.project_id, users.id, users.id, email, last_name, first_name, image FROM users INNER JOIN memberships ON memberships.user_id = users.id WHERE memberships.project_id IN (SELECT id FROM projects WHERE user_id = 1) AND users.id != 1

How can I change it to return an array of projects for one user? The result of this query is in json format.

 [ { "id": 2, "email": "daw@", "last_name": "dwadwa", "first_name": "dawdwa", "project_id": 1 }, { "id": 2, "email": "daw", "last_name": "dwadwa", "first_name": "dawdwa", "project_id": 2 } ]

The result I need is:

 [ { "id": 2, "email": "daw@ex.ua", "last_name": "dwadwa", "first_name": "dawdwa", "image": { "url": null, "thumbnail": { "url": null } }, "project_id": [1, 2] } ]

Database pg.

  • The sql query can return only flat structures, you should already convert json to the desired form on the client. Although you can try to use grouping and array_agg postgrespro.ru/docs/postgresql/9.6/functions-aggregate, but will your driver understand that returning an array to him is a big question - Mike

0