How to nested map for res.json() in express

I am working on an express application (server-side) that provides movie information to users but I am trying to send a JSON response like this:

{
  "title": "Star Trek: First Contact",
  "year": 1996,
  "runtime": 111,
  "genres": [
    "Action",
    "Adventure",
    "Drama"
  ],
  "country": "United States",
  "principals": [
    {
      "id": "nm0005772",
      "category": "cinematographer",
      "name": "Matthew F. Leonetti",
      "characters": []
    },
    {
      "id": "nm0001772",
      "category": "actor",
      "name": "Patrick Stewart",
      "characters": [
        "Picard"
      ]
    },
    {
      "id": "nm0000408",
      "category": "actor",
      "name": "Jonathan Frakes",
      "characters": [
        "Riker"
      ]
    },
    {
      "id": "nm0000653",
      "category": "actor",
      "name": "Brent Spiner",
      "characters": [
        "Data"
      ]
    },
    {
      "id": "nm0000996",
      "category": "actor",
      "name": "LeVar Burton",
      "characters": [
        "Geordi"
      ]
    },
    {
      "id": "nm0734472",
      "category": "writer",
      "name": "Gene Roddenberry",
      "characters": []
    },
    {
      "id": "nm0075834",
      "category": "writer",
      "name": "Rick Berman",
      "characters": []
    },
    {
      "id": "nm0103804",
      "category": "writer",
      "name": "Brannon Braga",
      "characters": []
    },
    {
      "id": "nm0601822",
      "category": "writer",
      "name": "Ronald D. Moore",
      "characters": []
    },
    {
      "id": "nm0000025",
      "category": "composer",
      "name": "Jerry Goldsmith",
      "characters": []
    }
  ],
  "boxoffice": 92027888,
  "poster": "https://m.media-amazon.com/images/M/MV5BYzMzZmE3MTItODYzYy00YWI5LWFkNWMtZTY5NmU2MDkxYWI1XkEyXkFqcGdeQXVyMjUzOTY1NTc@._V1_SX300.jpg",
  "plot": "The Borg travel back in time intent on preventing Earth's first contact with an alien species. Captain Picard and his crew pursue them to ensure that Zefram Cochrane makes his maiden flight reaching warp speed."
}

The problem is that I can't seem to get the principals data to output as it would return {} instead:

{
  "title": "Star Trek: First Contact",
  "year": 1996,
  "runtime": 111,
  "genres": [
    "Action",
    "Adventure",
    "Drama"
  ],
  "country": "United States",
  "principals": {},
  "boxoffice": 92027888,
  "poster": "https://m.media-amazon.com/images/M/MV5BYzMzZmE3MTItODYzYy00YWI5LWFkNWMtZTY5NmU2MDkxYWI1XkEyXkFqcGdeQXVyMjUzOTY1NTc@._V1_SX300.jpg",
  "plot": "The Borg travel back in time intent on preventing Earth's first contact with an alien species. Captain Picard and his crew pursue them to ensure that Zefram Cochrane makes his maiden flight reaching warp speed."
}

The objects outside such as title, year, runtime, genres, etc. are all in a table called basics, meanwhile every object inside principals are in another table called principals.

Here is what I've done so far:

router.get('/movies/data/:imdbID', function(req, res, next) {

  const queryMovie = req.db.from('basics').select(
      'primaryTitle', 
      'year',
      'runtimeMinutes', 
      'genres', 
      'country', 
      'boxoffice', 
      'poster', 
      'plot'
    ).where('tconst', req.params.imdbID)
  
  const queryPrincipals = req.db.from('principals').select('nconst', 'category', 'name', 'characters').where('tconst', req.params.imdbID)

  queryMovie.then((movieData) => {
    const movie = movieData.map(data => {
      return {
        title: data.tconst,
        year: data.year,
        runtime: data.runtimeMinutes,
        country: data.country,
        principals: queryPrincipals.then((principals) => {
          principals.map(principal => {
            return {
              id: principal.nconst,
              category: principal.category,
              name: principal.name,
              characters: principal.characters
            }
          })
        }),
        boxoffice: data.genres,
        poster: data.genres,
        plot: data.plot
      }
    })
    res.json(movie)
  })
});

You are building your principals object in an async function. This means that the return statement that returns the object is running before the async function returns and you're getting an empty object instead of actual data.

Use async/await to ensure that your data is available before the return.

router.get('/movies/data/:imdbID', async function(req, res, next) {

  const queryMovie = req.db.from('basics')
    .select(
      'primaryTitle', 
      'year',
      'runtimeMinutes', 
      'genres', 
      'country', 
      'boxoffice', 
      'poster', 
      'plot'
    )
    .where('tconst', req.params.imdbID)
  
  const queryPrincipals = req.db.from('principals')
    .select('nconst', 'category', 'name', 'characters')
    .where('tconst', req.params.imdbID)

  const movieData = await queryMovie();
  
  const movie = movieData.map(({ tconst:title, year, runtimeMinutes:runtime, country, genres, plot }) => {
    const principals = await queryPrincipals().map(
      ({nconst:id, category, name, characters}) => ({ id, category, name, characters })
    );
    return {
      title,
      year,
      runtime,
      country,
      principals,
      boxoffice: genres,
      poster: genres,
      plot 
    }
  })
  res.json(movie)
  })
});