posgres returns empty rows if json_agg doesn' find nothing

Question

Hello guys i'm facing a problem when using json_agg

i have two tables : docs and subs that look like this

Docs(
id primary key,
title,
name,
code
)
Subs(
id primary key,
title,
name,
code,
idoc fk
)

and i use this query to retrieve them

SELECT *,s.subdocs FROM docs d INNER JOIN(
    SELECT idoc,json_agg(
        json_build_object(
        'id',id,'title',title,'name',name,'code',code,
    )) AS subdocs FROM subs GROUP BY subs.idoc
    ) AS s ON s.idoc = d.idoc
 WHERE d.id = 4

It seem to work when there is a sub doc associated with the doc but when there is not the query doesn't return anything so how can i check if the result of the json agg is empty and to return at least the fields of the docs table?

please provide sample data and desired output

eshirvana
– eshirvana

2021-04-17 18:58:18 +00:00
Commented Apr 17, 2021 at 18:58 — eshirvana
– eshirvana, Commented Apr 17, 2021 at 18:58

eshirvana · Accepted Answer · 2021-04-17 19:01:13Z

1

seems like you need to use left join :

SELECT *,s.subdocs FROM docs d 
LEFT JOIN (
    SELECT idoc,json_agg(json_build_object('id',id,'title',title,'name',name,'code',code)) AS subdocs 
    FROM subs GROUP BY subs.idoc
    ) AS s ON s.idoc = d.idoc
 WHERE d.id = 4

answered Apr 17, 2021 at 19:01

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

posgres returns empty rows if json_agg doesn' find nothing

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related