How to get all results that has a specific value in an array?

Question

I want to get all employees that have a Java certification, but I have been struggling to create a query that works:

This is my schema:

{
  Employees: {
    randomId {
      name: john smith,
      certifications: 
        0: Swift,
        1: Android
    },
    randomId {
      name: richard williams,
      certifications: 
        0: java,
        1: Android
    }
  }
}

I did the following but I doubt that it is the most efficient way to solve this problem, (my thinking is that I do not want to query the entire database for all employees each time I'm checking for a certification)

DatabaseReference reference = database.getReference("Employees");
reference.addValueEventListener(new ValueEventListener() {
            @Override
            public void onDataChange(DataSnapshot dataSnapshot) {
                
                for (DataSnapshot snapshot : dataSnapshot.getChildren()){
                    Employee e = snapshot.getValue(Employee.class);
                    for (String cert : e.getCertifications()) {
                        if (cert.equals("java"))
                            return true;
                    }
                }
            }
        
            @Override
            public void onCancelled(DatabaseError error) {
                // Failed to read value
                Log.w("W", "Error", error.toException());
            }
        });

"Documents" term is generally used in document based NoSQL databases such as Firestore. Also do checkout [Best practices: Arrays in Firebase](https://firebase.blog/posts/2014/04/best-practices-arrays-in-firebase) If you delete any key, that won't be an array anymore as mentioned in the post. Firestore might be a better choice for such queries. Also checkout [Firebase query if child of child contains a value](https://stackoverflow.com/questions/40656589/firebase-query-if-child-of-child-contains-a-value) — Dharmaraj, Oct 16 '22 at 14:07

score 0 · Answer 1 · answered Oct 25 '22 at 18:05

Using your actual structure it's not possible. However, if you are allowed to change the structure a little bit, then you should consider storing those certificates into a Map like this:

{
  Employees: {
    randomId {
      name: john smith,
      certifications: 
        Swift: true, //
        Android: true //
    },
    randomId {
      name: richard williams,
      certifications: 
        Java: true, //
        Android: true //
    }
  }
}

To get for example all Employees with an Android certificate, then please use the following lines of code:

DatabaseReference db = FirebaseDatabase.getInstance().getReference();
DatabaseReference employeesRef = db.child("Employees");
employeesRef.orderByChild("certifications/Android").equalTo(true).get().addOnCompleteListener(new OnCompleteListener<DataSnapshot>() {
    @Override
    public void onComplete(@NonNull Task<DataSnapshot> task) {
        if (task.isSuccessful()) {
            for (DataSnapshot ds : task.getResult().getChildren()) {
                String name = ds.child("name").getValue(String.class);
                Log.d(TAG, name);
            }
        } else {
            Log.d(TAG, task.getException().getMessage()); //Never ignore potential errors!
        }
    }
});

The result in the logcat will be:

john smith
richard williams

If you consider at some point in time trying to use Cloud Firestore, then using whereArrayContains will help you achieve this in a simpler way.

my thinking is that I do not want to query the entire database for all employees each time I'm checking for a certification

Yes, that is correct. It doesn't make sense to download an entire node of employees and search for them on the client. That's considered an anti-pattern and a waste of resources and bandwidth.

Hey there. Can I help you with other information? – Alex Mamo Oct 26 '22 at 08:06 — Alex Mamo, Oct 26 '22 at 08:06

How to get all results that has a specific value in an array?

1 Answers1