Firebase Realtime Database: orderByChild().equalTo() on 4th level nesting

Question

I'm trying to run a firebase realtime database query to filter data based on value nested at 4th level from root. Below is the data structure and my query is:

let ordersSnapshot = await admin.database().ref(`orders`).orderByChild(`info/infoStatus`)
            .equalTo("In Process")
            .once('value');

But this query is returning no data. I have enabled indexing and there is no warning for it. I have also changed my query to .orderByChild('infoStatus') as well but no result.

If I set my ref one level below i.e. admin.database().ref('orders/userId1').orderByChild('info/infoStatus').equalTo("In Process") then it gets the result successfully. But in my scenario I don't have the user id which I can use in orderByChild.

Is this the limitation of realtime database query i.e. I need to update my data structure or is there any error in my query ?

Orders Node:

{
    "userId1": {
        "orderId1": {
            "info": {
                "infoStatus": "In Process"
            }
        },
        "orderId2": {
            "info": {
                "infoStatus": "Complete"
            }
        }
    },
    "userId2": {
        "orderId1": {
            "info": {
                "infoStatus": "In Process"
            }
        }
    },
    "userId3": {
        "orderId1": {
            "info": {
                "infoStatus": "In Process"
            }
        }
    }


}

Frank van Puffelen · Accepted Answer · 2019-04-24T15:54:24.153

The property that you order/filter on must be at a fixed path under each child. That means that in your current data structure, you can't filter on orders across all users, only on a specific user.

The typical solution is to flatten the data, storing the orders in a flat list, and adding a lookup list of order IDs to each user if that's still needed. Something like:

users: {
    "userId1": {
        "orderId1": true,
        "orderId2": true
    },
    "userId2": {
        "orderId3": true
    }
    "userId3": {
        "orderId4": true
    }
}
orders: {
    "orderId1": {
        "info": {
            "infoStatus": "In Process"
        }
    },
    "orderId2": {
        "info": {
            "infoStatus": "Complete"
        }
    },
    "orderId3": {
        "info": {
            "infoStatus": "In Process"
        }
    },
    "orderId4": {
        "info": {
            "infoStatus": "In Process"
        }
    }
}

Also see: Firebase Query Double Nested

Thanks for the explanation. In my case I'm going to maintain a separate node to temporarily store userId, orderId and the infoStatus fields, which will be enough for my use case. As other order info in filter query is not required by my functionality. — Umar Hussain, Apr 24 '19 at 15:39
btw I'm going to highlight the first two lines of your explanation. — Umar Hussain, Apr 24 '19 at 15:39

Firebase Realtime Database: orderByChild().equalTo() on 4th level nesting

1 Answers1