How to get an array of data from PostgreSQL immediately in Json format for Symfony?

Question

PostgreSQL

Is it possible from PostgreSQL to get data from related tables immediately in J'son format for Symfony? Now the server converts them and sends them to the front via the API, I think this would reduce the request processing time

Answer 1

Can.
I had something similar:

SELECT u.id,
    u.login,
    u.fullname,
    to_json(ARRAY( SELECT r.*::record AS r
           FROM ( SELECT r_1.id,
                    r_1.code,
                    r_1.name
                   FROM (users_roles ur
                     JOIN roles r_1 ON (((r_1.isdeleted = false) AND (ur.role_id = r_1.id))))
                  WHERE (ur.user_id = u.id)) r)) AS role_info
   FROM users u
  WHERE (u.isdeleted = false)
  ORDER BY u.fullname, u.login;

You can see it here: https://postgrespro.ru/docs/postgrespro/9.5/functi...

Answer 2

Postgresql has a regular aggregation function json_agg. Therefore, it is possible even without the involvement of storage facilities.

Answer 3

in any case, you have a direct road to the documentation.
there are several articles on habré about working with json in postgresql.
in the documentation, not all functions are described for working with json, you can familiarize yourself with the rest through such a crutch (pgadmin 3, directories, postgresql, functions and find json functions in alphabetical order there).
I will draw your attention to the following, in postgresql there are two types of json (json, jsonb), I don’t remember what the difference is in them, I use jsonb, and I recommend you to use it.
in a nutshell, it is very convenient to work with jsonb in the database itself.