Index tutorials

This section assumes that you have successfully completed the Quick start with FaunaDB tutorial, and that you still have the FaunaDB Console open in a browser tab/window.

If your FaunaDB Console session has expired, log in again.

Indexes are the fundamental tool for querying your documents in FaunaDB.

Since FaunaDB does not currently provide table scans, indexes are required for any queries where you do not already know all of the Refs involved.

Indexes are also key to searching, sorting, and combining results from multiple collections (typically called "joins").

This section provides a number of tutorials that demonstrate how to use indexes in various ways.

Preparation

Each of the tutorials uses a common set of collections and documents.

Create collections

A collection is like an SQL table: it groups similar kinds of documents together. Unlike SQL, the documents in a FaunaDB collection are not required to include the same fields.

For our tutorials, we’re going to create a Letters collection, storing the letters of the alphabet plus some counting fields, plus a People collection, storing person documents that include first and last names, plus a letter identifier.

Let’s create the collections:

  1. In the FaunaDB Console, make sure that you are using the my_db database.

    If you do not see the Console’s Home screen, click the Console Home button at the top left.

    Click my_db in the list of databases.

  2. Click the NEW COLLECTION button.

  3. Specify Letters for the Collection Name field.

  4. Click the SAVE button.

  5. Click the NEW COLLECTION button.

  6. Specify People for the Collection Name field.

  7. Click the SAVE button.

The collections have been created, and the Console has helpfully created the all_letters and all_people collection indexes.

If you were using the Shell (either in the Console, or fauna-shell in a terminal window), the following queries would create the collections:

CreateCollection({ name: "Letters" })
CreateCollection({ name: "People" })

And the following queries would create the collection indexes:

CreateIndex({ name: "all_letters", source: Collection("Letters") })
CreateIndex({ name: "all_people", source: Collection("People") })

Create documents

A document is equivalent to an SQL record; it stores values for named fields (in SQL, "columns"). The notable differences are:

  • FaunaDB documents do not have to have the same field structure as any other document in the same collection.

  • Fields in FaunaDB documents can contain documents/nested data.

Let’s create some documents for the Letters collection. Copy the following query, paste it into your Shell, and run it:

Map(
  [
    [ "101", { letter: "A", extra: "First" } ],
    [ "102", { letter: "B", extra: "second" } ],
    [ "103", { letter: "C", extra: "third" } ],
    [ "104", { letter: "D", extra: "4th" } ],
    [ "105", { letter: "E", extra: "fifth" } ],
    [ "106", { letter: "F", extra: "sixth" } ],
    [ "107", { letter: "G", extra: "seventh" } ],
    [ "108", { letter: "H", extra: "eighth" } ],
    [ "109", { letter: "I", extra: "9th" } ],
    [ "110", { letter: "J", extra: "tenth" } ],
    [ "111", { letter: "K", extra: 11 } ],
    [ "112", { letter: "L", extra: "" } ],
    [ "113", { letter: "M" } ],
    [ "114", { letter: "N", extra: "14th" } ],
    [ "115", { letter: "O", extra: "fifteenth" } ],
    [ "116", { letter: "P", extra: "16th" } ],
    [ "117", { letter: "Q", extra: "seventeenth" } ],
    [ "118", { letter: "R", extra: "18th" } ],
    [ "119", { letter: "S", extra: "19th" } ],
    [ "120", { letter: "T", extra: "20th" } ],
    [ "121", { letter: "U", extra: "21st" } ],
    [ "122", { letter: "V", extra: "22nd" } ],
    [ "123", { letter: "W", extra: "twenty-third" } ],
    [ "124", { letter: "X", extra: 24 } ],
    [ "125", { letter: "Y", extra: "24 + 1" } ],
    [ "126", { letter: "Z" } ]
  ],
  Lambda(
    ["id", "data"],
    Create(Ref(Collection("Letters"), Var("id")), { data: Var("data") })
  )
)

The points of interest for this query:

  • We’re using the Map function to process an array of arrays, with the inner array containing an id and an object representing the document we wish to create in FaunaDB.

    We’re using the id to create documents with a known id, rather than the id that FaunaDB would auto-generate for us. Specified ids can make working with documents notably easier.

  • We’re using a Lambda function to process each item in the document array, where each item contains an id and the data for the corresponding document.

  • Each invocation of the Lambda function creates a new document in the Letters collection, using the Var function to apply the value of the id variable as the document’s Ref id, and the data variable as the document’s data.

When you run this query, the result should be similar to:

[
  {
    "ref": Ref(Collection("Letters"), "101"),
    "ts": 15652991764850000,
    "data": {
      "letter": "A",
      "extra": "First"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "102"),
    "ts": 15652991764850000,
    "data": {
      "letter": "B",
      "extra": "second"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "103"),
    "ts": 15652991764850000,
    "data": {
      "letter": "C",
      "extra": "third"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "104"),
    "ts": 15652991764850000,
    "data": {
      "letter": "D",
      "extra": "4th"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "105"),
    "ts": 15652991764850000,
    "data": {
      "letter": "E",
      "extra": "fifth"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "106"),
    "ts": 15652991764850000,
    "data": {
      "letter": "F",
      "extra": "sixth"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "107"),
    "ts": 15652991764850000,
    "data": {
      "letter": "G",
      "extra": "seventh"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "108"),
    "ts": 15652991764850000,
    "data": {
      "letter": "H",
      "extra": "eighth"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "109"),
    "ts": 15652991764850000,
    "data": {
      "letter": "I",
      "extra": "9th"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "110"),
    "ts": 15652991764850000,
    "data": {
      "letter": "J",
      "extra": "tenth"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "111"),
    "ts": 15652991764850000,
    "data": {
      "letter": "K",
      "extra": 11
    }
  },
  {
    "ref": Ref(Collection("Letters"), "112"),
    "ts": 15652991764850000,
    "data": {
      "letter": "L",
      "extra": ""
    }
  },
  {
    "ref": Ref(Collection("Letters"), "113"),
    "ts": 15652991764850000,
    "data": {
      "letter": "M"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "114"),
    "ts": 15652991764850000,
    "data": {
      "letter": "N",
      "extra": "14th"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "115"),
    "ts": 15652991764850000,
    "data": {
      "letter": "O",
      "extra": "fifteenth"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "116"),
    "ts": 15652991764850000,
    "data": {
      "letter": "P",
      "extra": "16th"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "117"),
    "ts": 15652991764850000,
    "data": {
      "letter": "Q",
      "extra": "seventeenth"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "118"),
    "ts": 15652991764850000,
    "data": {
      "letter": "R",
      "extra": "18th"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "119"),
    "ts": 15652991764850000,
    "data": {
      "letter": "S",
      "extra": "19th"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "120"),
    "ts": 15652991764850000,
    "data": {
      "letter": "T",
      "extra": "20th"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "121"),
    "ts": 15652991764850000,
    "data": {
      "letter": "U",
      "extra": "21st"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "122"),
    "ts": 15652991764850000,
    "data": {
      "letter": "V",
      "extra": "22nd"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "123"),
    "ts": 15652991764850000,
    "data": {
      "letter": "W",
      "extra": "twenty-third"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "124"),
    "ts": 15652991764850000,
    "data": {
      "letter": "X",
      "extra": 24
    }
  },
  {
    "ref": Ref(Collection("Letters"), "125"),
    "ts": 15652991764850000,
    "data": {
      "letter": "Y",
      "extra": "24 + 1"
    }
  },
  {
    "ref": Ref(Collection("Letters"), "126"),
    "ts": 1565299176485000,
    "data": {
      "letter": "Z"
    }
  }
]

Now, let’s create some documents for the People collection. Copy the following query, paste it into your Shell, and run it:

Map(
  [
    { first: "Alan",    last: "Perlis",   letter: "A" },
    { first: "Alan",    last: "Turing",   letter: "B" },
    { first: "Grace",   last: "Hopper",   letter: "C" },
    { first: "Leslie",  last: "Lamport" },
    { first: "Marvin",  last: "Minsky",   letter: 1 },
    { first: "Stephen", last: "Cook",     letter: "F" },
    { first: "Tim",     last: "Cook",     letter: "G" }
  ],
  Lambda(
    "person",
    Create(Collection("People"), { data: Var("person") })
  )
)

This query is very similar to the query we used to create documents in the Letters collection. The People documents that we have created just differ in structure.

You may have noticed that both queries vary the definition of the last field. These variations are used in the tutorials to highlight behavioral differences.

When you run this query, the result should be similar to:

[
  {
    "ref": Ref(Collection("People"), "240166254282805769"),
    "ts": 1565299238420000,
    "data": {
      "first": "Alan",
      "last": "Perlis",
      "letter": "A"
    }
  },
  {
    "ref": Ref(Collection("People"), "240166254282801673"),
    "ts": 1565299238420000,
    "data": {
      "first": "Alan",
      "last": "Turing",
      "letter": "B"
    }
  },
  {
    "ref": Ref(Collection("People"), "240166254282806793"),
    "ts": 1565299238420000,
    "data": {
      "first": "Grace",
      "last": "Hopper",
      "letter": "C"
    }
  },
  {
    "ref": Ref(Collection("People"), "240166254282803721"),
    "ts": 1565299238420000,
    "data": {
      "first": "Leslie",
      "last": "Lamport"
    }
  },
  {
    "ref": Ref(Collection("People"), "240166254282804745"),
    "ts": 1565299238420000,
    "data": {
      "first": "Marvin",
      "last": "Minsky",
      "letter": 1
    }
  },
  {
    "ref": Ref(Collection("People"), "240166254282807817"),
    "ts": 1565299238420000,
    "data": {
      "first": "Stephen",
      "last": "Cook",
      "letter": "F"
    }
  },
  {
    "ref": Ref(Collection("People"), "240166254282802697"),
    "ts": 1565299238420000,
    "data": {
      "first": "Tim",
      "last": "Cook",
      "letter": "G"
    }
  }
]

Now that we have our collections and documents created, we’re all set to start working through the index tutorials.

Continue to the tutorials

The indexing tutorials include:

The tutorials are listed in order of increasing complexity, but they can be followed in any order.