Get a table as a column in SQL-CodePudding

Lets say we have 2 tables: books and chapters. Each book has many chapters.

books

id,title

chapters

id,title,book_id

How can I can get all the book titles and also an array of objects with all the chapters belonging to that book? For example:

{
id:1,
title: Book 1
chapters:[
  {
   id:1,
   title: Chapter 1,
   book_id: 1,
   },
  {
   id:2,
   title: Chapter 2,
   book_id: 1,
   }
 ]
}

CodePudding user response：

If you just need the SQL query, you can just go for a simple join between the two tables:

SELECT * FROM books b INNER JOIN chapters c
ON b.id=c.book_id

then, if you need to convert the result in an object you should specify which language, framework, orm, etc... you're using.

CodePudding user response：

If your requirement is just to build json with given structure, try the following code:

SELECT b.id as id, b.title as title, c.js as Chapters 
FROM books b 
OUTER APPLY (
    SELECT id, title, book_id 
    FROM chapters c 
    WHERE b.id = c.book_id
    FOR JSON PATH
)c(js)
FOR JSON PATH