Create JSON from joined tables

user_1178016 0
Viewed 79

How do I create JSON(B) from this statement:

SELECT *
FROM foo
LEFT JOIN bar
  USING (id)

My solution currently looks like this:

SELECT to_jsonb(foo) || coalesce(to_jsonb(bar), '{}')
FROM foo
LEFT JOIN bar
  USING (id)

This becomes uglier for every joined table, e.g.:

SELECT to_jsonb(foo) || coalesce(to_jsonb(bar), '{}') || coalesce(to_jsonb(baz), '{}')
FROM foo
LEFT JOIN bar
  USING (id)
LEFT JOIN baz
  USING (id)

I'd like something like this:

SELECT to_jsonb(*)
FROM foo
LEFT JOIN bar
  USING (id)

But that gives me:

[42883] ERROR: function to_jsonb() does not exist

jsonb postgresql sql json
1 Answers

The third query produces the cartesian product of all rows from bar and baz with the same id. Regardless whether it is intended, you can use a derived table, moving the conversion to JSON to the outer query.

select to_jsonb(q)
from (
    select *
    from foo
    left join bar using(id)
    left join baz using(id)
    ) q
user_19957380
Related