How to get nested documents in FaunaDB with a filter?

Question

The following query:

  Paginate(Documents(Collection("backyard"))),
  Lambda(
    "f",
    Let(
      {
        backyard: Get(Var("f")),
        user: Get(Select(["data", "user"], Var("backyard")))
      },
      {
        backyard: Var("backyard"),
        user: Var("user")
      }
    )
  )
)

results to:

{
  data: [
    {
      backyard: {
        ref: Ref(Collection("backyard"), "333719283470172352"),
        ts: 1654518359560000,
        data: {
          user: Ref(Collection("user"), "333718599460978887"),
          product: "15358",
          date: "2022-06-06",
          counter: "1"
        }
      },
      user: {
        ref: Ref(Collection("user"), "333718599460978887"),
        ts: 1654517707220000,
        data: {
          email: "<email>",
          name: "Paolo"
        }
      }
    },
    {
      backyard: {
        ref: Ref(Collection("backyard"), "333747850716381384"),
        ts: 1654545603400000,
        data: {
          user: Ref(Collection("user"), "333718599460978887"),
          product: "15358",
          date: "2022-06-08",
          counter: "4"
        }
      },
      user: {
        ref: Ref(Collection("user"), "333718599460978887"),
        ts: 1654517707220000,
        data: {
          email: "<email>",
          name: "Paolo"
        }
      }
    }
  ]
}

How can I filter backyard by date without losing the nested users?

I tried:

Map(
  Paginate(Range(Match(Index("backyard_by_date")), "2022-05-08", "2022-06-08")),
  Lambda(
    "f",
    Let(
      {
        backyard: Get(Var("f")),
        user: Get(Select(["data", "user"], Var("backyard")))
      },
      {
        backyard: Var("backyard"),
        user: Var("user")
      }
    )
  )
)

However, the resultset is an empty array and the following already returns an empty array:

Paginate(Range(Match(Index("backyard_by_date")), "2022-05-08", "2022-06-08"))

My index:

{
  name: "backyard_by_date",
  unique: false,
  serialized: true,
  source: "backyard"
}

Maybe I have to adjust my index? The following helped me a lot:

Can you update your question to include the definition of the "backyard_by_date" index? — eskwayrd, Jun 07 '22 at 16:18
@eskwayrd: I updated the question and added the index definition. — paolo, Jun 08 '22 at 07:27
That index definition has no `terms` specified. If that's what you're actually using, that's the problem. An index with no `terms` or `values` is called a "collection index": all of a collection's documents are included, with no matching `terms` to subset the group, and the default result includes document references, which cannot be used for date comparisons. Perhaps ask your question in the Fauna Forums, which is more conducive to a conversation that eventually arrives at solution. — eskwayrd, Jun 08 '22 at 18:50

score 0 · Answer 1 · answered Jul 15 '22 at 21:08

Your index definition is missing details. Once that gets fixed, everything else you were doing is exactly right.

In your provided index, there are no terms or values specified, which makes the backyard_by_date index a "collection" index: it only records the references of every document in the collection. In this way, it is functionally equivalent to using the Documents function but incurs additional write operations as documents are created or updated within the backyard collection.

To make your query work, you should delete your existing index and (after 60 seconds) redefine it like this:

CreateIndex({
  name: "backyard_by_date",
  source: Collection("backyard"),
  values: [
    {field: ["data", "date"]},
    {field: ["ref"]}
  ]
})

That definition configures the index to return the date field and the reference for every document.

Let's confirm that the index returns what we expect:

> Paginate(Match(Index("backyard_by_date")))
{
  data: [
    [ '2022-06-06', Ref(Collection("backyard"), "333719283470172352") ],
    [ '2022-06-08', Ref(Collection("backyard"), "333747850716381384") ]
  ]
}

Placing the date field's value first means that we can use it effectively in Range:

> Paginate(Range(Match(Index("backyard_by_date")), "2022-05-08", "2022-06-08"))
{
  data: [
    [ '2022-06-06', Ref(Collection("backyard"), "333719283470172352") ],
    [ '2022-06-08', Ref(Collection("backyard"), "333747850716381384") ]
  ]
}

And to verify that Range is working as expected:

> Paginate(Range(Match(Index("backyard_by_date")), "2022-06-07", "2022-06-08"))
{
  data: [
    [ '2022-06-08', Ref(Collection("backyard"), "333747850716381384") ]
  ]
}

Now that we know the index is working correctly, your filter query needs a few adjustments:

> Map(
  Paginate(
    Range(Match(Index("backyard_by_date")), "2022-05-08", "2022-06-08")
  ),
  Lambda(
    ["date", "ref"],
    Let(
      {
        backyard: Get(Var("ref")),
        user: Get(Select(["data", "user"], Var("backyard")))
      },
      {
        backyard: Var("backyard"),
        user: Var("user")
      }
    )
  )
)
{
  data: [
    {
      backyard: {
        ref: Ref(Collection("backyard"), "333719283470172352"),
        ts: 1657918078190000,
        data: {
          user: Ref(Collection("user"), "333718599460978887"),
          product: '15358',
          date: '2022-06-06',
          counter: '1'
        }
      },
      user: {
        ref: Ref(Collection("user"), "333718599460978887"),
        ts: 1657918123870000,
        data: { name: 'Paolo', email: '<email>' }
      }
    },
    {
      backyard: {
        ref: Ref(Collection("backyard"), "333747850716381384"),
        ts: 1657918172850000,
        data: {
          user: Ref(Collection("user"), "333718599460978887"),
          product: '15358',
          date: '2022-06-08',
          counter: '4'
        }
      },
      user: {
        ref: Ref(Collection("user"), "333718599460978887"),
        ts: 1657918123870000,
        data: { name: 'Paolo', email: '<email>' }
      }
    }
  ]
}

Since the index returns a date string and a reference, the Lambda inside the Map has to accept those values as arguments. Aside from renaming f to ref, the rest of your query is unchanged.

How to get nested documents in FaunaDB with a filter?

1 Answers1