How do I return nested array from a psql database in json format

Question

I'll start from the top... I have two tables, quotes and comments. One quote 'has many' comments. My end goal is to have two .map() functions on the front end, one that renders all the quotes to my page, and another that renders each comment for the quote that sits above it.

I'm trying to retrieve a json from psql that returns something like this:

[
{
"id": 1,
"content": "quote number 1!",
"name": null,
"num_of_flags": null,
"comments": [
  {
    "id": 1,
    "comment_content": "comment one!",
    "num_of_likes": null,
    "quote_id": 1
  },
  {
    "id": 1,
    "comment_content": "comment 2!",
    "num_of_likes": null,
    "quote_id": 1
  }
]

This way, 'quotes' is an array of objects, and so are the comments that belong to it.

Here is what I actually am getting back:

[
{
"id": 1,
"content": "quote number 1!",
"name": null,
"num_of_flags": null,
"comments": [
  {
    "id": 1,
    "comment_content": "comment 1!",
    "num_of_likes": null,
    "quote_id": 1
  }
]
},
{
"id": 2,
"content": "quote number 2!",
"name": null,
"num_of_flags": null,
"comments": [
  {
    "id": 2,
    "comment_content": "comment 2!",
    "num_of_likes": null,
    "quote_id": 2
  }
]
},
{
"id": 3,
"content": "quote number 3!",
"name": null,
"num_of_flags": null,
"comments": [
  {
    "id": 3,
    "comment_content": "comment 3",
    "num_of_likes": null,
    "quote_id": 1
  }
]
}
]

Notice the third 'quote' object. it contains 'comment 3'. Comment 3 has a quote_id of 1, and quote_id is a foreign key that is supposed to relate to quote number 1 only, so it shouldn't be showing up in quote object 3. Furthermore, my database actually contains multiple comments for all of these quotes, but my json is only returning a single comment, and it's not even related to the quote correctly.

This is my schema:

BEGIN;

DROP TABLE IF EXISTS quotes;
DROP TABLE IF EXISTS comments;

CREATE TABLE quotes (
 id SERIAL PRIMARY KEY,
 name VARCHAR,
 content VARCHAR,
 num_of_flags INTEGER
);

CREATE TABLE comments (
 id SERIAL PRIMARY KEY,
 comment_content VARCHAR,
 num_of_likes INTEGER,
 quote_id INTEGER
);

ALTER TABLE ONLY comments
 ADD CONSTRAINT quotes_id_fkey
 FOREIGN KEY (quote_id)
 REFERENCES quotes(id);

COMMIT;

I looked at this blog and learned a bit about the json_agg aggregate function and was able to piece together a psql call that did indeed turn my 'comments' into an array (as you can see in my second code block of this post). This is the psql call:

SELECT
      q.id,
      q.content,
      q.name,
      q.num_of_flags,
      json_agg(c.*) as comments
      FROM quotes q
      INNER JOIN comments c USING (id)
      GROUP BY q.id, q.content, q.name, q.num_of_flags

I think the key to this is just an adjustment of the psql call, but I can't seem to make the right tweak. I've also tried to apply a lateral join, and the array_to_json methods that I found in this Stack Overflow post, but haven't been able to apply them my project, possibly because of my syntax.

Hopefully this post has been clear and easy to understand! I really appreciate your time, if you happen to come across it and can help me out.

-Bill

Answer 1

The issue with the comments ending up in the wrong quotes is because you link the tables on USING (id); instead you should use ON quote.id = comment.quote_id. That probably also causes only a single comment to show for each quote, even though there might me multiple or none.

The best way to turn a row into a JSON object is to use json_build_object() (PG9.4+). json_agg() will assemble multiple JSON objects into a JSON array. Put together, you get this:

SELECT json_agg(quote_comments) AS json_object
FROM (
    SELECT json_build_object('id', q.id,
                             'name', q.name,
                             'content', q.content,
                             'num_of_flags', q.num_of_flags,
                             'comments', json_agg(c.j)) AS quote_comments
    FROM quotes q
    LEFT JOIN (
        SELECT quote_id, json_build_object('id', id,
                                           'comment_content', comment_content,
                                           'num_of_likes', num_of_likes,
                                           'quote_id', quote_id) AS j
        FROM comments) c ON c.quote_id = q.id
    GROUP BY 1, 2, 3, 4) sub;

JSON uses hierarchical structures for its objects and your query should be built using the same hierarchy; you can do this using sub-queries. At every level you build a JSON object and you aggregate into JSON arrays as required. Since you have two levels of aggregation, you need an additional level of sub-query because you cannot group on different sets in a single (sub-)query.

Answer 2

Postgres version 9.3 has good support for the JSON data type. U can try this:

select q.*,  row_to_json(c.*) as comments from quotes q inner join comments c using(quote_id);

Have edited to use left join instead: select q., row_to_json(c.) as comments from quotes q left join comments c ON q.id = c.quote_id;