GROQ: How to apply filters to array of references

I am currently building a website using Sanity and Sanity Shopify Connect and came across an issue with collections. The main problem was that collections that are automatically synchronized with the Sanity Shopify App do not directly contain a relation to the products included in it. The way to do it would be to query the Shopify API directly.

However, I want to be able to query everything through Sanity to make the frontend of my project as easy as possible. So, my solution was to create a custom collection in Sanity and not use Shopify's collections. This worked well at first. The user creates a collection in Sanity and can add products through an array of references, perfect! I ensured they could not add any inactive or deleted products with the filter options on Sanity arrays.

In our case each collection has a small amount of products so this is a manageable solution, I would not recommend if you have thousand of products.

The issue was that once a product has been added to a collection, if it is later removed or becomes inactive, it still stays on the frontend. This appears as an array filter only to control what is shown to the editor when making the collection, not to control what can get a query from it.

The solution

The solution was to perform a filter on the array of references. However, that was a challenging task.

First Attempt: using joins

The idea was not to directly use the reference array and propagate the product but to directly query and filter through all the products.

// This is a first attempt, not an ideal solution
*[_type == "collectionSanity" && slug.current == $slug][0] {
  "products": *[_type == "product" && _id in ^.products[]._ref && store.status == "active" && !store.isDeleted]
}

This works, as we don't see deleted and inactive products anymore. Regardless, one of the cool features of custom collections is that our editors can choose the ordering of the products, and here we lose that.

Final solution: expanding references and pipe operator

Here, the idea is to first load all the referenced products and then use GROQ's pipe operator to filter them down.

*[_type == "collectionSanity" && slug.current == $slug][0] {
  "rawProducts": products[]->
} | {
  "rawProducts": null,
  "products": rawProducts[store.status == "active" && !store.isDeleted]
}

This solution is also way more efficient as it does not require loading all the products and then filtering them down. In our dataset, with only 38 products, the first solution took around 700ms and the second one 20ms.