Firestore dashboards – StructuredQuery cheat sheet

Gists
, ,
1

SquaredUp is an easy way to dashboard and monitor data in Firestore (the document database in Firebase, also available as a GCP service). However, anything beyond a simple collection query in Firestore requires a JSON-formatted StructuredQuery.

The JSON is quite verbose and fussy, so here’s a cheat sheet for some common queries.

These examples can be pasted into the ‘Firestore Structured Query’ data stream in SquaredUp. See the official docs

Every query will need a from clause to select the collection. The official docs say this should be an array, but it takes a single object and it’s very unusual to query across multiple collections in Firestore. This example uses accounts as the collection name. It’s not yet possible to query subcollections in SquaredUp (a collectionId with / does not work).

{
  "from": {
    "collectionId": "accounts"
  }   
}

Collections can be very large, so you probably want to add a limit:

{
  "from": {
    "collectionId": "accounts"
  },
  "limit": 100
}

To sort, use orderBy. The default is ASCENDING. As with from this supports an array or a single object (more common):

{
  "from": {
    "collectionId": "accounts"
  },
  "orderBy": {
    "field": {
      "fieldPath": "name"
    }
  },
  "limit": 100
}

To sort DESCENDING:

{
  "from": {
    "collectionId": "accounts"
  },
  "orderBy": {
    "field": {
      "fieldPath": "created"
    },
    "direction": "DESCENDING"
  },
  "limit": 100
}

Finally, some common where clauses.

It is important to remember that Firestore automatically creates an index for every field, but if you want to filter and sort across multiple fields in the same query you will need to create an index.

A simple string equality filter. The only other string operator is NOT_EQUAL. Firebase does not support a ‘contains’ string filter or RegEx.

{
  "from": {
    "collectionId": "accounts"
  },
  "where": {
   "fieldFilter": {
      "field": {
          "fieldPath": "name"
      },
      "op": "EQUAL",
      "value": {
          "stringValue": "SquaredUp"
      }
    }
  }
}

A number field filter requires you to know whether the field type is integer or double (use doubleValue in place of integerValue below). Supported operators are LESS_THAN, LESS_THAN_OR_EQUAL, GREATER_THAN, GREATER_THAN_OR_EQUAL, EQUAL, NOT_EQUAL.

{
  "from": {
    "collectionId": "accounts"
  },
  "where": {
   "fieldFilter": {
      "field": {
          "fieldPath": "score"
      },
      "op": "EQUAL",
      "value": {
          "integerValue": 100
      }
    }
  }
}

A boolean filter:

{
  "from": {
    "collectionId": "accounts"
  },
  "where": {
    "fieldFilter": {
      "field": {
        "fieldPath": "isActive"
      },
      "op": "EQUAL",
      "value": {
        "booleanValue": true
      }
    }
  }
}

A logical AND query. OR can be used in place of AND.

{
  "from": {
    "collectionId": "accounts"
  },
  "where": {
    "compositeFilter": {
      "op": "AND",
      "filters": [
        {
          "fieldFilter": {
            "field": {
              "fieldPath": "score"
            },
            "op": "EQUAL",
            "value": {
              "integerValue": 100
            }
          }
        },
        {
          "fieldFilter": {
            "field": {
              "fieldPath": "isActive"
            },
            "op": "EQUAL",
            "value": {
              "booleanValue": true
            }
          }
        }
      ]
    }
  }
}

A logical OR filter against the same property can also be achieved using the IN filter:

{
  "from": {
    "collectionId": "accounts"
  },
  "where": {
    "fieldFilter": {
      "field": {
        "fieldPath": "country"
      },
      "op": "IN",
      "value": {
        "arrayValue": {
          "values": [
            {
              "stringValue": "United Kingdom"
            },
            {
              "stringValue": "Canada"
            }
          ]
        }
      }
    }
  }
}

Filtering array fields using ARRAY_CONTAINS. Replace with ARRAY_CONTAINS_ANY and specify an array value for an OR filter against an array.

{
  "from": {
    "collectionId": "accounts"
  },
  "where": {
    "fieldFilter": {
      "field": {
        "fieldPath": "tags"
      },
      "op": "ARRAY_CONTAINS",
      "value": {
        "stringValue": "paid"
      }
    }
  }
}

The last filter to mention is the unary IS_NOT_NULL which is useful to check the existence of a field (specifically that it exists and is not null).

{
  "from": {
    "collectionId": "accounts"
  },
  "where": {
    "unaryFilter": {
      "field": {
        "fieldPath": "subscriptionId"
      },
      "op": "IS_NOT_NULL"
    }
  }
}

I hope this helps to get up and running with Firestore StructuredQuery. The full docs for StructuredQuery are here

1 Like