How to return the client datas with contacts data and if clients don't have contacts return the clients data

At my application, I have two tables called clients and contacts and clients can have many contacts about this we can know that inside the contacts have the client_id and I would like to create a query to a specific route called /clients/:id that will return the clients data join with the all contacts this clients have. So I would like to return something

{
    client: [
     {
      name_client:...,
      email_client:...,
      telephone_client:....,
      contacts: [
        allcontacts
      ]
     }
    ]
}

But I not getting what I want and I found other problem that with this query

select
    distinct clients.id as client_id,
    clients.name as client_name,
    clients.email as client_email,
    clients.telephone as client_telephone,
    contacts.id as contact_id,
    contacts.name as contact_name
from
    contacts
 inner join
    clients
    on contacts.client_id = clients.id
where
    contacts.client_id = 'd9935b6d-0497-4a45-b472-ed3b4f85cd60'

If my client do not have the contact(s), will return an empty object, but I wouldn't this, I would like to return the client data.

Solution

Please change your query to this:

select distinct clients.id as client_id,
       clients.name as client_name,
       clients.email as client_email,
       clients.telephone as client_telephone,
       contacts.id as contact_id,
       contacts.name as contact_name
  from clients
       left join contacts
              on contacts.client_id = clients.id
 where clients.client_id = 'd9935b6d-0497-4a45-b472-ed3b4f85cd60'

The left join returns all matching clients rows (presumably only one) along with any contacts rows with a matching client_id. If there are no such rows, then contacts.id and contacts.name will be null.

If you would like to have PostgreSQL construct a json document for you from this, then use jsonb functions to accomplish that for you:

with base as (
  select distinct clients.id as client_id,
         clients.name as client_name,
         clients.email as client_email,
         clients.telephone as client_telephone,
         contacts.id as contact_id,
         contacts.name as contact_name
    from clients
         left join contacts
                on contacts.client_id = clients.id
   where clients.client_id = 'd9935b6d-0497-4a45-b472-ed3b4f85cd60'
), aggregate_contacts as (
  select client_id, client_name, client_email, client_telephone,
         jsonb_agg(
           case 
             when contact_id is null then '{}'::jsonb
             else jsonb_build_object( 
                'contact_id', contact_id, 'contact_name', contact_name
             )
           end
         ) as contacts
    from base
   group by client_id, client_name, client_email, client_telephone
)
select to_jsonb(aggregate_contacts) as result
  from aggregate_contacts;

Using a lateral join:

select to_jsonb(q) as result
  from (select clients.id as client_id,
               clients.name as client_name,
               clients.email as client_email,
               clients.telephone as client_telephone,
               case
                 when jsonb_agg(x) = '[null]'::jsonb then '[]'::jsonb
                 else jsonb_agg(x)
               end as contacts
          from clients 
               left join lateral 
                    (select id as contact_id, name as contact_name
                       from contacts
                      where id = clients.id) x on true
         where client_id = 'd9935b6d-0497-4a45-b472-ed3b4f85cd60'
         group by clients.id, clients.name, clients.email, clients.telephone
       ) as q