Grab Sub Data with Sub Select in PostgreSQL

Question

I have a PostgreSQL database that looks like this:

content
id     title      content
1      Home       Welcome to the homepage

-

fields
id    contentID   value
1     1           Field Value 1
2     1           Field Value 2
3     2           Field value in other content

I'm trying to write a query which combines the data for both and ultimately comes out with:

[ { title: 'Home', content: 'Welcome to the homepage', fields: [ { id: 1, contentID: 1, value: 'Field Value 1' }, { id: 2, contentID: 1, value: 'Field Value 2' } ] } ]

Here is the query I tried, want to do it all in one query for performance:

let data = await query(`SELECT *, (SELECT array(SELECT * FROM stuff.fields WHERE stuff.fields.contentID=stuff.content.id)) as fields FROM stuff.content`);

Note that stuff is the schema name, I'm super new to PostgreSQL but I like it's data flexibility and speed compared to MySQL, and I don't think MySQL can do something like this.

Answer 1

The expected output looks like json. Use jsonb functions:

select to_jsonb(c)- 'id' || jsonb_build_object('fields', jsonb_agg(to_json(f)))
from content c
join fields f on c.id = f.content_id
group by c.id;

to get this json:

{
    "title": "Home",
    "fields": [
        {
            "id": 1,
            "value": "Field Value 1",
            "content_id": 1
        },
        {
            "id": 2,
            "value": "Field Value 2",
            "content_id": 1
        }
    ],
    "content": "Welcome to the homepage"
}