I want to join two collections in faunadb using common field

Question

I have two collections with users and another with jobs.

Users Collection:

 {
  "ref": Ref(Collection("users"), "365794904000954449"),
  "ts": 1685108055020000,
  "data": {
    "name": "srinivas",
    "location": "sattenapalli"
  }
}

Jobs Collection:

{
  "ref": Ref(Collection("jobs"), "366712312325734481"),
  "ts": 1685982963785000,
  "data": {
    "name": "srinivas",
    "Job": "Senior Architect"
  }
}

I want to finally retrieve the data by joining the above two collection documents by the field name and the output should be an object with name,job and location.

Answer 1

Your question is asked in reference to a prior version of Fauna's query language, so I'll answer in Fauna's current, and far more easy to use version, FQL v10. This query will work on your existing dataset, even if you created it with v4

In your example, you are establishing a foreign key relationship between a Job object and a User object. Creating this relationship is very easy in v10

let u = User.byId( "376254402363654212")
Job.byId("376254443038965825").update( { user: u})

{
  id: "376254443038965825",
  coll: Job,
  ts: Time("2023-09-19T00:25:42.980Z"),
  job: "Senior Architect",
  user: User.byId("376254402363654212")
}

Fauna autoresolves relationships. So when you project the user field, the entire document is returned, completing the join

Job.where( .job == "Senior Architect" ){ user }

{
  data: [
    {
      user: {
        id: "376254402363654212",
        coll: User,
        ts: Time("2023-09-19T00:23:29.970Z"),
        name: "srinivas",
        location: "sattenapalli"
      }
    }
  ]
}

Here is an example where I am only interested in projecting specific fields. Again, the relationship is automatically resolved for you

Job.where( .job == "Senior Architect" ){ user{ name, location } }

{
  data: [
    {
      user: {
        name: "srinivas",
        location: "sattenapalli"
      }
    }
  ]
}