FQL Equivalent of SQL WHERE column_name IN (value1, value2, ...);"

Help
1

Here is my FQL

Query(
  Lambda(
    ["keys"],
    Map(
      Paginate(
        Match(
          Index("my_index"),
         Var("keys")
        )
      ),
      Lambda("account", Get(Var("account")))
    )
  )
)

This doesn’t seem to do what I’m expecting, and I think it’s because I can’t match on an array. Is there an equivalent to the SQL:

SELECT * WHERE my_index IN (value1, value2, …);"

2

You need to create an appropriate index for the field (or “column”) that you want to search:

CreateIndex({
  name: "search_people_by_first",
  source: Collection("People"),
  terms: [
    { field: ["data", "first"] }
  ]
})

When you search an index, the matching entries are returned as a set. You can use set functions to get the union, difference, etc. In this case, we want to combine the set of results for each term that you want to search for, so we’lll use Union:

Map(
  Paginate(
    Union(
      Match(Index("search_people_by_first"), "Alan"),
      Match(Index("search_people_by_first"), "Grace")
    )
  ),
  Lambda("X", Get(Var("X")))
)

The result would look like:

{
  data: [
    {
      ref: Ref(Collection("People"), "293318393615876608"),
      ts: 1615989068650000,
      data: {
        first: 'Alan',
        last: 'Perlis',
        age: 97,
        degrees: [ 'BA', 'MA', 'PhD' ],
        letter: 'A'
      }
    },
    {
      ref: Ref(Collection("People"), "293318393615877632"),
      ts: 1615989068650000,
      data: {
        first: 'Alan',
        last: 'Turing',
        age: 107,
        degrees: [ 'BA', 'MA', 'MS', 'PhD' ],
        letter: 'B'
      }
    },
    {
      ref: Ref(Collection("People"), "293318393617973760"),
      ts: 1615989068650000,
      data: {
        first: 'Grace',
        last: 'Hopper',
        age: 119,
        degrees: [ 'BA', 'MA', 'PhD' ],
        letter: 'C'
      }
    }
  ]
}

The data you see here is from our indexing tutorials: Index tutorials | Fauna Documentation

4

Thanks for the reply, this is effectively where I got stuck. My input is single array, so how would I do something like this:

Query(
  Lambda(
    ["keys"],
    Map(
      Paginate(Union(Match(Index("my_index"), Var("keys")))),
      Lambda("account", Get(Var("account")))
    )
  )
)

Where keys is an array

5

The Match function compares the values that you provide with the terms fields specified in the associated index. You cannot just pass an array of things you want to search for. You need to use the index and query composition that I provided.

In the query you provided, you used: Match(Index("my_index"), Var("keys")). There will only be results when the keys value matches one or more index entries in the my_index index.

Additionally, Union combines sets. Your query only has one set, so Union isn’t doing anything. You need to use multiple Match functions for Union to be helpful.