2b2a 2b2a - 1 month ago 8
JSON Question

Nest Data Under Column in Associative Table

I am trying to produce a nested JSON file with a PostgreSQL database. I have three tables: Person_tbl, Log_tbl, Person_Log_tbl, where the latter is an associative table containing Person_id and Log_id as foreign keys to the previous tables. I want the end result to look something like this:

{
username:
log1 {
log_id: 1,
activity_id: 1,
logdate: 2016-10-25
}
log2 {
log_id: 2,
activity_id: 1,
logdate: 2016-10-26
}
username2:
....
}


Right now I have a SQL query that returns 1 log under each user, with the same user being returned for however many logs they have. I can't figure out how to join all logs together under the correct user. Here is my SQL query:

SELECT p.username, row_to_json(l.*)
FROM person_log_tbl pl
INNER JOIN person_tbl p on pl.person_id = p.person_id
INNER JOIN log_tbl l on pl.log_id = l.log_id
GROUP BY p.username, l.log_id


Thanks

Answer

I think you need to aggregate the values together and remove l.log_id from the group by:

SELECT p.username, json_agg(l.*)
FROM person_log_tbl pl INNER JOIN
     person_tbl p
     ON pl.person_id = p.person_id INNER JOIN
     log_tbl l 
     ON pl.log_id = l.log_id
GROUP BY p.username;