node.js fetch mysql data using two tables -

table lists

id | user_id | name

1 | 3 | lista
2 | 3 | listb

table celebrities

id | user_id | list_id | celebrity_code

1 | 3 | 1 | aa000297
2 | 3 | 1 | aa000068
3 | 3 | 2 | aa000214
4 | 3 | 2 | aa000348

i looking json object

[ {id:1, name:'lista', celebrities:[{celebrity_code:aa000297},{celebrity_code:aa000068}]},

{id:2, name:'listb', celebrities:[{celebrity_code:aa000214},{celebrity_code:aa000348}]}
]

moved answer since details getting long, , thought additional references useful future readers.

since using mysql, check out group_concat. object, want group_concat on concatenated string. if live schema more {id:2, name:'listb', celebrity_codes:['aa000214','aa000348']} you'll have simpler query. if make sqlfiddle of basic schema (basically create tables plus inserts of above sample data), might write you. :-)

to clear, while group_concat can this, if trying generate more simple schema, gets pretty messy code , starts making more , more sense move application layer both code maintenance standpoint performance & scalability considerations.

also note sqllite supports group_concat, other databases:

• postgres user should @ string_agg
• sql server users should check out this project on codeplex.
• oracle users can use model, illustrated here.