CODEWITHSUNDEEP
sqljsonpostgresqljsonb
xehpuk
xehpuk

Reputation: 8241

Create JSON from joined tables

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

Upvotes: 3

Views: 89

Answers (1)

klin
klin

Reputation: 121694

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

Upvotes: 3

Related Questions