FaunaDB: how to fetch a custom column

I'm just learning FaunaDB and FQL and having some trouble (mainly because I come from MySQL). I can successfully query a table (eg: users) and fetch a specific user. This user has a property users.expiry_date which is a faunadb Time() type.

What I would like to do is know if this date has expired by using the function LT(Now(), users.expiry_date), but I don't know how to create this query. Do I have to create an Index first?

So in short, just fetching one of the users documents gets me this:

{
  id: 1,
  username: 'test',
  expiry_date: Time("2022-01-10T16:01:47.394Z")
}

But I would like to get this:

{
  id: 1,
  username: 'test',
  expiry_date: Time("2022-01-10T16:01:47.394Z"),
  has_expired: true,
}

I have this FQL query now (ignore oauthInfo):

Query(
  Let(
    {
     oauthInfo: Select(['data'], Get(Ref(Collection('user_oauth_info'), refId))),
     user: Select(['data'], Get(Select(['user_id'], Var('oauthInfo'))))
    },
    Merge({ oauthInfo: Var('oauthInfo') }, { user: Var('user') })
  )
)

How would I do the equivalent of the mySQL query SELECT users.*, IF(users.expiry_date < NOW(), 1, 0) as is_expired FROM users in FQL?

Your use of Let and Merge show that you are thinking about FQL in a good way. These are functions that can go a long way to making your queries more organized and readable!

I will start with some notes, but they will be relevant to the final answer, so please stick with me.

The Query function

https://docs.fauna.com/fauna/current/api/fql/functions/query

First, you should not need to wrap anything in the Query function, here. Query is necessary for defining functions in FQL that will be run later, for example, in the User-Defined Function body. You will always see it as Query(Lambda(...)).

Fauna IDs

https://docs.fauna.com/fauna/current/learn/understanding/documents

Remember that Fauna assigns unique IDs for every Document for you. When I see fields named id, that is a bit of a red flag, so I want to highlight that. There are plenty of reasons that you might store some business-ID in a Document, but be sure that you need it.

Getting an ID

A Document in Fauna is shaped like:

{
  ref: Ref(Collection("users"), "101"), // <-- "id" is 101
  ts: 1641508095450000,
  data: { /* ... */ }
}

In the JS driver you can use this id by using documentResult.ref.id (other drivers can do this in similar ways)

You can access the ID directly in FQL as well. You use the Select function.

Let(
  {
   user: Get(Select(['user_id'], Var('oauthInfo')))
   id: Select(["ref", "id"], Var("user"))
  },
  Var("id")
)

More about the Select function.

https://docs.fauna.com/fauna/current/api/fql/functions/select

You are already using Select and that's the function you are looking for. It's what you use to grab any piece of an object or array.

Here's a contrived example that gets the zip code for the 3rd user in the Collection:

Let(
  {
   page: Paginate(Documents(Collection("user")),
  },
  Select(["data", 2, "data", "address", "zip"], Var("user"))
)

Bring it together

That said, your Let function is a great start. Let's break things down into smaller steps.

Let(
  {
   oauthInfo_ref: Ref(Collection('user_oauth_info'), refId)
   oauthInfo_doc: Get(Var("oathInfoRef")),
 
   // make sure that user_oath_info.user_id is a full Ref, not just a number
   user_ref: Select(["data", "user_id"], Var("oauthInfo_doc"))
   user_doc: Get(Var("user_ref")),
   user_id: Select("id", Var("user_ref")),

   // calculate expired
   expiry_date: Select(["data", "expiry_date"], Var("user_doc")),
   has_expired: LT(Now(), Var("expiry_date"))
  },

  // if the data does not overlap, Merge is not required.
  // you can build plain objects in FQL
  { 
    oauthInfo: Var("oauthInfo_doc"), // entire Document
    user: Var("user_doc"),           // entire Document
    has_expired: Var("has_expired")  // an extra field
  }
)

Instead of returning the auth info and user as separate points if you do want to Merge them and/or add additional fields, then feel free to do that

  // ...
  Merge(
    Select("data", Var("user_doc")),  // just the data
    {
      user_id: Var("user_id"),        // added field
      has_expired: Var("has_expired") // added field
    }
  )
)