Node Express和SQLite3:将多对多数据作为一个对象返回

假设您的Sqlite版本具有 JSON1 extension 启用,类似于:

PRAGMA foreign_keys = on;
CREATE TABLE movies(id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE genres(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE genres_movies(id INTEGER PRIMARY KEY
                         , movie_id INTEGER REFERENCES movies(id)
                         , genre_id INTEGER REFERENCES genres(id)
                         );
CREATE INDEX gm_idx_movie_id ON genres_movies(movie_id);
INSERT INTO movies(id, title) VALUES (1, 'Catapult: The Movie')
                                   , (2, 'Deadpool')
                                   , (3, 'Dune');
INSERT INTO genres(id, name) VALUES (1, 'documentary'), (2, 'comedy');
INSERT INTO genres_movies(movie_id, genre_id) VALUES (1, 1), (1, 2), (2, 2);
SELECT json_object('id', m.id
                 , 'title', m.title
                 , 'genres',
                   CASE WHEN g.id ISNULL THEN json_array()
                        ELSE json_group_array(json_object('id', g.id
                                                        , 'name', g.name))
                   END)
       AS movie
FROM movies AS m
LEFT JOIN genres_movies AS gm ON m.id = gm.movie_id
LEFT JOIN genres AS g ON g.id = gm.genre_id
GROUP BY m.id
ORDER BY m.id;

生产

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":[]}