Select in JSON Object?

Question

JSON

Hello everyone, help who knows ..

There is a PL\SQL function similar to this

FUNCTION myfunc(p_id varchar2) return in number
BEGIN

    SELECT t_id, t_name, t_phone FROM clients where t_id = p_id;

    SELECT t_id, t_name, t_phone FROM trainers where t_id < p_id;       

    RETURN;

END myfunc;

The first Select returns only one row of data, while the second Select returns multiple rows of data.

You need to make sure that the data of the first select is written as a JSON object,
and the data of the second select is written as a JSON array that belongs to the object.

Like this,
Json_obj(Value1, Value2, json_arr);

All this is necessary in order to return this data when called. (return)
A function can only return one value, because of this it is important that json_arr was as one of the elements of json_obj.

Answer 1

Below is a query variant (PostgreSQL) that collects everything into one JSON:

SELECT row_to_json(client.*)
FROM (
  SELECT 
  clients.*,
  json_agg(
    row_to_json(trainers.*)
  ) trainers
  FROM 
  clients 
  JOIN trainers ON trainers.t_id < clients.t_id
  where 
  clients.t_id = 10
  GROUP BY clients.t_id, clients.t_name, clients.t_phone
) client;

SQL fiddle
PL\SQL Variant:

SELECT json_object(
      't_id' VALUE client.t_id,
      't_name' VALUE client.t_name,
      't_phone' VALUE client.t_phone,
      't_trainers' VALUE client.trainers
  ) FROM (
  SELECT 
  clients.*,
  json_arrayagg(json_object(
      't_id' VALUE trainers.t_id,
      't_name' VALUE trainers.t_name,
      't_phone' VALUE trainers.t_phone
  )) trainers
  FROM 
  clients 
  JOIN trainers ON trainers.t_id < clients.t_id
  where 
  clients.t_id = 10
  GROUP BY clients.t_id, clients.t_name, clients.t_phone
  ) client;

Answer 2

Is it even possible?
Or are there other ways?