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
– Akhilesh Mishra, Commented Jun 27, 2021 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
– Bergi, Commented Jun 27, 2021 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
– Ruslan Plastun, Commented Jun 27, 2021 at 13:09

Ruslan Plastun · Accepted Answer · 2021-06-28 10:25:03Z

1

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;

edited Jun 28, 2021 at 10:25

Ruslan Plastun

2,2724 gold badges28 silver badges54 bronze badges

answered Jun 27, 2021 at 13:47

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

2 Comments

Ruslan Plastun Over a year ago

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"

Bergi Over a year ago

@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 …).

Collectives™ on Stack Overflow

Converting select statement directly into json array

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related