Values

The values field of an index is available to specify sorting criteria and direction, plus the field values to return. It’s possible to sort by one field or several, in ascending or descending order.

Let’s add some documents to a collection named cities:

The C# version of this example is not currently available.
The Go version of this example is not currently available.
The Java version of this example is not currently available.
const schemas = []
const cities = [
  { name: 'Boise', state: 'Idaho', population: 240380 },
  { name: 'Pittsburgh', state: 'Pennsylvania', population: 302971 },
  { name: 'San Francisco', state: 'California', population: 873965 },
  { name: 'Scranton', state: 'Pennsylvania', population: 76328 },
  { name: 'Modesto', state: 'California', population: 218464 },
]
var counter = 1

cities.forEach((city) => {
  schemas.push(q.Create(q.Ref(q.Collection('cities'), counter), { data: city }))
  counter++
})
client.query(
  q.Do(schemas, 'Cities created!')
)
.then((res) => console.log(res))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))
Cities created!
The Python version of this example is not currently available.
The Shell version of this example is not currently available.
Query metrics:

  •    bytesIn:   784

  •   bytesOut:    30

  • computeOps:     1

  •    readOps:     0

  •   writeOps:     5

  •  readBytes:    70

  • writeBytes: 1,162

  •  queryTime:  42ms

  •    retries:     0

Sort by a single field

If our goal is to sort the documents in the cities collection by population, we can create an index for that purpose. The following example creates an index with the population`field in the `values definition, including the reverse attribute to specify descending order. The values field also includes each document’s Reference, so that we can easily retrieve the document.

The C# version of this example is not currently available.
The Go version of this example is not currently available.
The Java version of this example is not currently available.
client.query(
  q.CreateIndex(
    {
      name: 'cities_sort_by_population_desc',
      source: q.Collection('cities'),
      values: [
        { field: ['data', 'population'], reverse: true },
        { field: ['ref'] },
      ],
    },
  )
)
.then((ret) => console.log(ret))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))
{
  ref: Index("cities_sort_by_population_desc"),
  ts: 1642026842090000,
  active: true,
  serialized: true,
  name: "cities_sort_by_population_desc",
  source: Collection("cities"),
  values: [
    { field: ["data", "population"], reverse: true },
    { field: ["ref"] }
  ],
  partitions: 8
}
The Python version of this example is not currently available.
The Shell version of this example is not currently available.
Query metrics:

  •    bytesIn:   200

  •   bytesOut:   370

  • computeOps:     1

  •    readOps:     0

  •   writeOps:     6

  •  readBytes: 1,960

  • writeBytes: 1,272

  •  queryTime:  41ms

  •    retries:     0

A best practice when creating indexes is to be descriptive with the name, so it’s easy to tell the purpose of the index and the order of its sorting (if any).

We can read out the contents of the index with the Paginate and Match functions:

The C# version of this example is not currently available.
The Go version of this example is not currently available.
The Java version of this example is not currently available.
client.query(
  q.Paginate(q.Match(q.Index('cities_sort_by_population_desc')))
)
.then((ret) => console.log(ret))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))
{
  data: [
    [ 873965, Ref(Collection("cities"), "3") ],
    [ 302971, Ref(Collection("cities"), "2") ],
    [ 240380, Ref(Collection("cities"), "1") ],
    [ 218464, Ref(Collection("cities"), "5") ],
    [ 76328, Ref(Collection("cities"), "4") ]
  ]
}
The Python version of this example is not currently available.
The Shell version of this example is not currently available.
Query metrics:

  •    bytesIn:   76

  •   bytesOut:  572

  • computeOps:    1

  •    readOps:    8

  •   writeOps:    0

  •  readBytes:  436

  • writeBytes:    0

  •  queryTime: 16ms

  •    retries:    0

The cities_sort_by_population_desc index is automatically updated any time a new document is added to the cities collection, provided that the new document includes a population field. If the population field of any existing document is updated, the cities_sort_by_population_desc is automatically updated as well.

The above example shows the contents of the cities_sort_by_population_desc index, but it’s not very helpful because the city’s name is not included, only its population. Fortunately, each index entry includes the Reference of the document it refers to, so we can modify the query to retrieve each document and display its contents.

The C# version of this example is not currently available.
The Go version of this example is not currently available.
The Java version of this example is not currently available.
client.query(
  q.Map(
    q.Paginate(q.Match(q.Index('cities_sort_by_population_desc'))),
    q.Lambda(
      ['pop', 'ref'], q.Get(q.Var('ref'))
    )
  )
)
.then((ret) => console.log(ret))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))
{
  data: [
    {
      ref: Ref(Collection("cities"), "2"),
      ts: 1645054235215000,
      data: {
        name: "San Francisco",
        state: "California",
        population: 873965
      }
    },
    {
      ref: Ref(Collection("cities"), "3"),
      ts: 1645054235215000,
      data: {
        name: "Pittsburgh",
        state: "Pennsylvania",
        population: 302971
      }
    },
    {
      ref: Ref(Collection("cities"), "1"),
      ts: 1645054235215000,
      data: {
        name: "Boise",
        state: "Idaho",
        population: 240380
      }
    },
    {
      ref: Ref(Collection("cities"), "5"),
      ts: 1645054235215000,
      data: {
        name: "Modesto",
        state: "California",
        population: 218464
      }
    },
    {
      ref: Ref(Collection("cities"), "4"),
      ts: 1645054235215000,
      data: {
        name: "Scranton",
        state: "Pennsylvania",
        population: 76328
      }
    },
  ]
}
The Python version of this example is not currently available.
The Shell version of this example is not currently available.
Query metrics:

  •    bytesIn:   151

  •   bytesOut: 1,019

  • computeOps:     1

  •    readOps:    13

  •   writeOps:     0

  •  readBytes:   918

  • writeBytes:     0

  •  queryTime:   8ms

  •    retries:     0

Sort by multiple fields

Some data access patterns require sorting data by more than one field, and you can do that by specifying multiple fields in the values field of an index. The following example creates an index on the cities collection which sorts documents first by the state field, then by the population field in descending order.

The C# version of this example is not currently available.
The Go version of this example is not currently available.
The Java version of this example is not currently available.
client.query(
  q.CreateIndex(
    {
      name: 'cities_sort_by_state_asc_population_desc',
      source: q.Collection('cities'),
      values: [
        { field: ['data', 'state'] },
        { field: ['data', 'population'], reverse: true },
        { field: ['ref'] },
      ],
    },
  )
)
.then((ret) => console.log(ret))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))
{
  ref: Index("cities_sort_by_state_asc_population_desc"),
  ts: 1642030085290000,
  active: true,
  serialized: true,
  name: "cities_sort_by_state_asc_population_desc",
  source: Collection("cities"),
  values: [
    {
      field: ["data", "state"]
    },
    {
      field: ["data", "population"],
      reverse: true
    },
    {
      field: ["ref"]
    }
  ],
  partitions: 8
}
The Python version of this example is not currently available.
The Shell version of this example is not currently available.
Query metrics:

  •    bytesIn:   248

  •   bytesOut:   417

  • computeOps:     1

  •    readOps:     0

  •   writeOps:     6

  •  readBytes: 2,022

  • writeBytes: 1,429

  •  queryTime:  59ms

  •    retries:     0

We can adapt the previous example to get our city documents ordered alphabetically by state, and then by population in descending order for states with multiple entries.

The C# version of this example is not currently available.
The Go version of this example is not currently available.
The Java version of this example is not currently available.
client.query(
  q.Map(
    q.Paginate(q.Match(q.Index('cities_sort_by_state_asc_population_desc'))),
    q.Lambda(
      ['state', 'pop', 'ref'], q.Get(q.Var('ref'))
    )
  )
)
.then((ret) => console.log(ret))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))
{
  data: [
    {
      ref: Ref(Collection("cities"), "3"),
      ts: 1645054235215000,
      data: {
        name: "San Francisco",
        state: "California",
        population: 873965
      }
    },
    {
      ref: Ref(Collection("cities"), "5"),
      ts: 1645054235215000,
      data: {
        name: "Modesto",
        state: "California",
        population: 218464
      }
    },
    {
      ref: Ref(Collection("cities"), "1"),
      ts: 1645054235215000,
      data: {
        name: "Boise",
        state: "Idaho",
        population: 240380
      }
    },
    {
      ref: Ref(Collection("cities"), "2"),
      ts: 1645054235215000,
      data: {
        name: "Pittsburgh",
        state: "Pennsylvania",
        population: 302971
      }
    },
    {
      ref: Ref(Collection("cities"), "4"),
      ts: 1645054235215000,
      data: {
        name: "Scranton",
        state: "Pennsylvania",
        population: 76328
      }
    }
  ]
}
The Python version of this example is not currently available.
The Shell version of this example is not currently available.
Query metrics:

  •    bytesIn:   169

  •   bytesOut: 1,019

  • computeOps:     1

  •    readOps:    13

  •   writeOps:     0

  •  readBytes:   972

  • writeBytes:     0

  •  queryTime:  13ms

  •    retries:     0

If another document is added to the cities collection which includes the state and population fields, or if one of the existing documents is modified, the two indexes we’ve created here are automatically updated. If a new document is added which does not include any fields included in the indexes, the indexes are not updated.

Is this article helpful? 

Tell Fauna how the article can be improved:
 Visit Fauna's forums or email docs@fauna.com

Thank you for your feedback!