Firestore: query where clause > nested field

Question

I'm using Flutter and Firestore. Suppose my database looks like this:

[
  {
    Name: 'John',
    Address: {
      Street: "John doe street"
      Postal: "12047"
    }
  },
  {
    Name: 'Mary',
    Address: {
      Street: "Fleet street"
      Postal: "1242B"
    }
  }
]

I now would like to search for all persons whose postal code contains '12'. So that would mean both records in this dummy database.

But how can I filter on the nested field 'Address.Postal'?

var ref = FirebaseFirestore.instance;
var query = '12';
ref.collection('users')
        .where(
          'Address.Postal', // Won't work
          ... // There is no 'like' operator or something that looks alike
        );

FYI the project being in Flutter is irrelevant.

check here https://stackoverflow.com/a/76637467/2126077 – Heshan Sandeepa Jul 07 '23 at 13:36 — Heshan Sandeepa, Jul 07 '23 at 13:36

Okkult · Answer 1 · 2022-11-30T18:09:46.460

The where() function now accepts a FieldPath instance as the first argument. This allows you to reference either nested fields or the documentId() in your query.

Example:

queryText = '12';

postalRef = new FieldPath('Address', 'Postal');
ref.collection('users')
        // Equivalent of LIKE "queryText%"
        .where(postalRef, '>=', queryText)
        .where(postalRef, '<=', queryText+ '\uf8ff');

As for the Firestore equivalent of SQL LIKE statements, this answer explains it better. However, the short explanation is that you can use the >= and <= operators to query your string field by character values.

\uf8ff (Private-Use Area ) is just a commonly used delimiter for this operation since:

it's a very high unicode character
it's outside what you'd expect in a web-based "text search".

score 1 · Answer 2 · answered Feb 02 '22 at 19:23

There is no way to check for a specific property in the array items. All you can do is check whether the entire item is present with array-contains. But for that you need to know the entire array item, so all name and address properties.

If you only have the name, there is no built-in operator to test whether any array item contains that name. Instead you'll want to extract just the names into their own array (say addressNames) and then use array-contains on that.

Firestore: query where clause > nested field

2 Answers2