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 am looking a JSON object like this
[ {id:1, name:'ListA', celebrities:[{celebrity_code:AA000297},{celebrity_code:AA000068}]},
{id:2, name:'ListB', celebrities:[{celebrity_code:AA000214},{celebrity_code:AA000348}]}
]
Moved this to an answer since the details were getting long, and I thought the additional references would be useful to future readers.
Since you are using MySQL, check out GROUP_CONCAT. To get your object, you will want to GROUP_CONCAT on a CONCATenated string. If you could live with a schema more like {id:2, name:'ListB', celebrity_codes:['AA000214','AA000348']} you'll have a simpler query. If you make a SQLfiddle of your basic schema (basically your create tables plus the inserts of the above sample data), someone might even write it for you. :-)
To be clear, while GROUP_CONCAT can do this, if you are trying to generate more than a fairly simple schema, it gets to be some pretty messy code and it starts making more and more sense to move it into your application layer both from a code maintenance standpoint as well as performance & scalability considerations.
Also note that SQLLite supports GROUP_CONCAT, for other databases:
string_aggMODEL, as illustrated here.
GROUP_CONCAT. To get your object, you will want toGROUP_CONCATon aCONCATenated string. If you could live with a schema more like{id:2, name:'ListB', celebrity_codes:['AA000214','AA000348']}you'll have a simpler query. If you make a SQLfiddle of your basic schema (basically your create tables plus the inserts of the above sample data), someone might even write it for you. :-)