Node Express And Sqlite3: Return Related Many-to-many Data As One Object

I have the following tables: movies genres genres_movies ------ ------ ------------- id id id title name genre_id m

Solution 1:

Assuming your Sqlite version has the JSON1 extension enabled, something like:

PRAGMA foreign_keys =on;
CREATETABLE movies(id INTEGERPRIMARY KEY, title TEXT);
CREATETABLE genres(id INTEGERPRIMARY KEY, name TEXT);
CREATETABLE genres_movies(id INTEGERPRIMARY KEY
                         , movie_id INTEGERREFERENCES movies(id)
                         , genre_id INTEGERREFERENCES genres(id)
                         );
CREATE INDEX gm_idx_movie_id ON genres_movies(movie_id);
INSERTINTO movies(id, title) VALUES (1, 'Catapult: The Movie')
                                   , (2, 'Deadpool')
                                   , (3, 'Dune');
INSERTINTO genres(id, name) VALUES (1, 'documentary'), (2, 'comedy');
INSERTINTO genres_movies(movie_id, genre_id) VALUES (1, 1), (1, 2), (2, 2);
SELECTjson_object('id', m.id
                 , 'title', m.title
                 , 'genres',
                   CASEWHEN g.id ISNULL THENjson_array()
                        ELSE json_group_array(json_object('id', g.id
                                                        , 'name', g.name))
                   END)
       AS movie
FROM movies AS m
LEFTJOIN genres_movies AS gm ON m.id = gm.movie_id
LEFTJOIN genres AS g ON g.id = gm.genre_id
GROUPBY m.id
ORDERBY m.id;

produces

movie                                                                                                  
--------------------------------------------------------------------------------------------------------
{"id":1,"title":"Catapult: The Movie","genres":[{"id":1,"name":"documentary"},{"id":2,"name":"comedy"}]}
{"id":2,"title":"Deadpool","genres":[{"id":2,"name":"comedy"}]}
{"id":3,"title":"Dune","genres":[]}