Converting select statement directly into json array

Question

From here, and here I have figured out that if I want to aggregate a set of related rows into an array of objects I have to use this syntax:

(select to_json(C) from ( /* subquery */ ) C)

So, if I have three tables: user, creature and their junction table user_creature:

And I want to retrieve each user, and each creature that belongs to this user, I would have to do something like this:

select to_json(T)
from (
         select "user".id        as user_id,
                (select to_json(C) -- !!! There it is
                 from (
                          select name, height
                          from creature
                                   inner join "user_creature" uc on creature.id = "uc".creature_id
                                   inner join "user" u on "uc".user_id = u.id
                          where u.id = user_id
                      ) C)       as "creatures" -- !!! There it is
         from "user"
     ) T;

This query successfully retrieves a list of users and their related creatures:

Is there a way to drop select and from keywords from the query, so that I can write my query like this:

select to_json(T)
from (
         select "user".id        as user_id,
                to_json( -- !!! Calling to_json directly on select statement
                      select name, height
                      from creature
                               inner join "user_creature" uc on creature.id = "uc".creature_id
                               inner join "user" u on "uc".user_id = u.id
                      where u.id = user_id
                  )  as "creatures"
         from "user"
     ) T;

Your question is not clear. Please post some sample data and JSON output you want by editing your question — Akhilesh Mishra, Jun 27 '21 at 11:18
Why do you use a junction table? Your query suggests that each user can have only a single creature - it would fail with multiple creatures. Or did you plan to use `json_agg`? — Bergi, Jun 27 '21 at 12:58
It's just an example for testing. I use it to solve a problem where I actually need a junction table — Ruslan Plastun, Jun 27 '21 at 13:09

score 1 · Accepted Answer · edited Jun 28 '21 at 10:25

It is possible to use a subquery as the argument to to_json, but not practical:

You need to wrap the subquery in a grouping parenthesis: to_json( (SELECT … FROM …) )
The subquery must return exactly one row (but that's normal)
The subquery must return exactly one column. This is a bit harder - you can return a record, but if you build it dynamically (e.g. from a selection of columns, you can hardly control the field names)

(See a demo here).

Instead, use json_build_object if you want to write a single SELECT query only:

SELECT json_build_object(
  'user_id', u.id,
  'creatures', (
    SELECT json_build_object(
      'name', c.name,
      'height', c.height
    )
    FROM creature c
    INNER JOIN "user_creature" uc ON c.id = uc.creature_id
    WHERE uc.user_id = u.id
  )
)
FROM "user" u;

And, if you want to be able to retrieve multiple rows use SELECT json_agg(json_build_object(…)) FROM … or ARRAY(SELECT json_build_object(…) FROM …):

SELECT json_build_object(
  'user_id', u.id,
  'creatures', (
    SELECT json_agg(json_build_object(
      'name', c.name,
      'height', c.height
    ))
    FROM creature c
    INNER JOIN "user_creature" uc ON c.id = uc.creature_id
    WHERE uc.user_id = u.id
  )
)
FROM "user" u;

Thanks! But what if I have more than one row returned? If I run this query against a user table that has multiple creatures, then I get an error saying: "more than one row returned by a subquery used as an expression" — Ruslan Plastun, Jun 28 '21 at 05:45
@RuslanPlastun That's what I asked in the question comments :-) I had assumed you weren't getting multiple rows, and got the expected JSON result with a single creature object. If what you actually want to get is an array of creature objects, use `SELECT json_agg(json_build_object(…)) FROM …` or `ARRAY(SELECT json_build_object(…) FROM …)`. — Bergi, Jun 28 '21 at 08:07

Converting select statement directly into json array

1 Answers1