How to make a query on nested keys in a Firebase Realtime Database?

Question

I'm trying to build a leaderboard for my users inside my realtime db.

That's the structure of my db:

_firebase_db_url {
  users: {
     user1: {
        displayName: "name", 
        ...some_other_infos...
        games: {
           game1: {
               lastScore: 123,
               timePlayed: 120
           }, 
           game2: {
               lastScore: 456,
               timePlayed: 360
           },
           ...
        }
     }
  },
  games: {
      0 : {
         "id": "game1", 
         "description": "..."
      },
      1 : {
         "id": "game1", 
         "description": "..."
      },
      ...
}

What I'm trying to do is making a query on the db to get max 10 player ordered by lastScore on a certain game.

I've tried so far:

db.ref('/users')
  .orderByChild(`lastScore`)
  .limitToFirst(10)
  .get()
  .then((val) => console.log(val.val()));

This prints my users but not ordered by game scores

I've also tried to make a loop on all games and then the query on the db:

   db.ref('/games')
     .get()
     .then(
       (result) => {
         const gamesList = result.val();
         const gamesIdsArray = []
         Object.values(gamesList)
           .forEach(game => {
               gamesIdsArray.push(game.id)
           })
         return gamesIdsArray;
       }).then(   // Once I got entirely my array filled with the games ID, I proceed
         (gamesIdsArray) => {
            gamesIdsArray.forEach(gameId => {
            db.ref('/users')
              .orderByChild(`lastScore`)
              .limitToFirst(10)
              .get()
              .then((val) => console.log(val.val()))
            })
         }
       )

This prints for the number of games all my users not ordered by games. (It prints the users also if them don't have the specific game obj: if I'm looping on game5 the query prints 10 users including users that don't have the filed game5 inside their games section).

Other tries:

 db.ref('/users')
   .child('games')
   .child(`${req.userId}/games/${gameId}`) //passing the userId inside my https.onRequest() function
   .orderByChild(`lastScore`)
   .limitToFirst(10)
   .get()
   .then((val) => console.log(val.val()))

Still nothing useful

db.ref('/users/{userId}/games{gameId}/lastScore')
   .orderByChild(`lastScore`)
   .limitToFirst(10)
   .get()
   .then((val) => console.log(val.val()))

Still nothing useful

I've also included in my realtime db rules all the .indexOn I've tried so far. Nothing helped.

What am I doing wrong? What should I try? Should I switch to Firestore to keep user data?

[ EDITED ]

I've slightly edited my db set as follows based on Frank's answer:

db {
    users: {
        "user1": {
            name: "name1",
            ... some other infos
        },
        "user2": {
            name: "name2",
            ... some other infos
        },
        ...
    }
    games: {
        "game1": {
            "user1": {
                bestScore: 123
            },
            "user2": {
                bestScore: 234
            }
        },
        "game2": {
            "user1": {
                bestScore: 765
            },
            "user2": {
                bestScore: 543
            }
        }
    }

}

Query that prints the users but not ordered:

db.ref("games")
  .child(gameId) //getting my gameId var from request.data.gameId from my onCall function
  .orderByChild("bestScore")
  .once('value', (res) => {
      console.log(res.val())
      return res.val()
   })

And still getting the users not ordered

@AlexMamo as you see in my database I want to make a leaderboard for each game (game1, game2 and so on) — nico_1512, Jul 19 '23 at 07:50
Your database structure is will not scale and for your current query to be excuted correctly you will need index on `lastScores` field according to this [answer](https://stackoverflow.com/a/53302804/20239914) as your `lastScores` resides inside a dynamic node which cannot be indexed statically you will have to change your db structure as explained in that answer. Hope it helps. — Rohit Kharche, Jul 19 '23 at 12:30
@nico_1512 Your 2nd query is getting failed because you need index to be made on `bestScore` which you can do as follows : "games" : { ".indexOn" : ["bestScore"] } inside the database.rules file. — Rohit Kharche, Jul 19 '23 at 15:04

Frank van Puffelen · Accepted Answer · 2023-07-19T15:17:18.470

You're combining data of multiple entity types (users, and game scores) under your users branch, which goes against Firebase's guidance on structuring data, specifically the section on keeping the data structure flat.

To allow your query, add an additional top-level node, where you track the scores of various users for a specific game. Something like this:

game_user_scores: {
  "game1": {
    "user1": {
      lastScore: 123,
      timePlayed: 120
    },
    ...
  },
  "game2": {
    "user1": {
      lastScore: 456,
      timePlayed: 360
    },
    ...
  },
  ...
}

Now you can query the top scorers for a specific game with:

db.ref("users")
  .child("game_user_scores/game1")
  .orderByChild("lastScore")
  .limitToFirst(10)

Your second problem is caused by the fact that you use val() immediately on the result. That gets the result as a JSON value, and there's no room for ordering information in that.

To process the results in order, use the built-in forEach method of the snapshot you get back:

((res) => {
  res.forEach((snapshot) => {
    console.log(snapshot.val())
  });
})

Oh yeah, that's a common mistake. I updated my answer to show how to properly process the results. --- Going forward, note that this is a new, separate problem (even when it's part of the same use-case of your app), so should be a separate question. Also see [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers) — Frank van Puffelen, Jul 19 '23 at 15:19
thanks so much! I've solved the problem with the same code form another answare (also made by you) — nico_1512, Jul 19 '23 at 15:21

How to make a query on nested keys in a Firebase Realtime Database?

1 Answers1