Upsert match using id parameter

Help
, ,
1

I am storing data returned from an external api in our db. The data will consist of new and updated records. I need to insert the new documents and update the existing documents. Since the external documents won’t have a ref value, I need to match on the included _id parameter.

In preparation for this I created a unique index on the collection.data.id field. I’m hoping I can get a sanity check on my upsert query below. While it works, I find it to be incredibly verbose and it’s giving me PTSD flashes from old node style call back hell and react render props.

    q.Map(
      documents,
      q.Lambda(
        'doc',
        q.If(
          q.Exists(q.Match(q.Index(indexName), q.Select(['id'], q.Var('doc')))),

          q.Update(
            q.Select(
              'ref',
              q.Get(
                q.Match(
                  q.Index(indexName),
                  q.Select(['id'], q.Var('doc'))
                )
              )
            ),
            { data: q.Var('doc') }),
            
          q.Create(q.Collection('Collection'), { data: q.Var('doc') }),
        ),
      ),
    ),

I can tolerate the first and third expressions of the If but the Update is brutal. Is there a more concise way to accomplish the Update?

I tried the same Match used in the Exists statement, but it threw an error saying it expected a reference. Please tell me there is a better way.

I come from Mongo and while I like the premise of Fauna, in practice I’m finding the interface very unapproachable. I’m trying very hard to not be set in my ways, but this took me almost 2 full working days to accomplish, thanks largely to this:

Here’s the equivalent operation in Momgo (minus the map):

db.Collection.findAndModify({
  query: { id: doc.id },
  update: doc,
  upsert: true
});
2

Hi @Chris_Bradley and welcome! :wave:

You can make your query more “DRY” by using the Let function. You can stash the Set in a variable and reuse it.

q.Map(
  documents,
  q.Lambda(
    "doc",
    q.Let(
      {
        match: q.Match(q.Index(indexName), q.Select(["id"], q.Var("doc"))),
      },
      q.If(
        q.Exists(q.Var("match")),
        
        q.Update(
          q.Select("ref", q.Get(q.Var("match"))),
          { data: q.Var("doc") }
        ),

        q.Create(q.Collection("Collection"), { data: q.Var("doc") })
      )
    )
  )
)

That’s still pretty verbose. You need to be explicit because Fauna does not assume you have any indexes, let alone the most efficient one for your use case.

Make our own findAndModify function

In the Mongo query example, you gave a Collection, and tell it to look for the value id. The search is either going to be backed by an index, or require a full table scan (we want to avoid those!). With Fauna, we’d rather be explicit about which Index to use.

You can create a findAndModify UDF that can be called in multiple locations.

CreateFunction({
  name: "findAndModify",
  body: Query(
    Lambda(
      ["index", "terms", "update", "upsert"],
      Let(
        {
          collection: Select("source", Get(Var("index"))),
          match: Match(Var("index"), Var("terms"))
        },
        If(
          Exists(Var("match")),
          Update(Select("ref", Get(Var("match"))), Var("update")),
          If(
            Var("upsert"), 
            Create(Var("collection"), Var("update")), 
            null
          )
        )
      )
    )
  ),
})

q.Map(
  documents,
  q.Lambda(
    "doc",
    q.Call(
      "findAndModify",
      q.Index(indexName),
      [q.Select(["id"], q.Var("doc"))],
      { data: q.Var("doc") },
      true
    )
  )
)
3

@ptpaterson Thank you for the tip!

Whats the reason behind needing to get a document by ref for the Update as opposed to using the same index matching convention with Exists?

4

The Get actually fetches data that contains the Ref. Match by itself just produces an abstract Set that has no data.

There are two ways to fetch the Ref for the first document in a Set

Select("ref", Get(Match(...)))

or

Select(["data", 0], Paginate(Match(...)))

Either method fetches the Document so that you actually have the Ref, which is a required parameter for Update.

Get will cause an error if you use it on a Set that doesn’t exist, so we only use it after the Exists call.

5

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.