Go Behind the Experience to see how Tecovas brings the West to life with Sanity 🤠 July 18th

Querying nested arrays in GROQ for filtering results

9 replies
Last updated: Apr 20, 2023
I cannot figure out how to query based on a property of an item in an array in another array. Great that the GROQ arcade has a relevant dataset: https://groq.dev/d2463396-6d5e-4085-a32e-5e3b1bd33624
1. How to only retrieve people who have a prize which has an affiliation to the city of "Leiden" for example?
2. Let's imagine that many people have many prizes (could not find more than 1 prize at first sight), and many prizes have many affiliations. How to make sure that in my results, the people object only has relevant prize with relevant affiliations (filter out affiliations that are not from the city of Leiden, and filter out prizes that do not have the right affiliation at all)
Apr 20, 2023, 8:28 AM
Probably
user L
has some god level way to do it, but I would have done it like this:
*[count(prizes[count(affiliations[city == "Leiden"])>0])>0]

Apr 20, 2023, 8:47 AM
I need the people more than the count though. You got me thinking and I tried
*[prizes[].affiliations[].city == "Leiden"]
but it did not return any results.
Apr 20, 2023, 8:54 AM
I haven't looked at your original query yet, just wanted to point out that your last one will be comparing the array of cities, i.e. becomes
[..., ...,] == "Leiden"]
. The operators don't associate the way you think.
You would need to count here:

*[count(prizes[].affiliations[city == "Leiden"]) > 0]
Apr 20, 2023, 8:55 AM
I think the above answers your first question, at least.
Apr 20, 2023, 8:59 AM
Yes, getting somewhere, thanks! I just tried in Vision with in our studio, and it's unexpected (to me) that it returns results where some properties (like
pricingPlans
in this case) are NOT defined. As we can see on the right, the first item in the results does have a productVariants property with one item, but this one does not have any pricingPlan so it cannot have one with quantityPolicy set to "unlimited".
Apr 20, 2023, 9:14 AM
With this filter you do get the people tho? The count is just the filter used
Apr 20, 2023, 9:14 AM
Perfect, thanks
user A
. This is what I needed for 1. indeed! I don't have all the right reflex when writing GROQ queries yet.
Looks like I could resolve my 2nd point with this query:

*[
  _type == "productHead" &&
  count(productVariants[count(pricingPlans[quantityPolicy == "unlimited"]) > 0]) > 0
  
]{
  _id,
  internalName,
  "productVariants": productVariants[count(pricingPlans[quantityPolicy == "unlimited"]) > 0]{
    ...,
    "pricingPlans": pricingPlans[quantityPolicy == "unlimited"]
  }
}
With this request, my result only contains products which, deep down, have pricing plan with the quantity policy set to unlimited. But each product data is also "cleaned" of all pricing plans that are NOT unlimited and all variants that do not have any unlimited plans. I needed this projection because when a product has 10 variants, and each variant has 10 pricing plans it was too time consuming to find out WHY the GROQ filter returned this product. I wanted to quickly find "Which plan was matched by the filter?". Thanks both of you!
Apr 20, 2023, 9:58 AM
Great, I would also consider doing a more document based approach with this content model – separating variations and pricing plans into documents would make this quite easier I think
Apr 20, 2023, 10:02 AM
It may, but that ship has sailed a long time ago for our project. Also, "dangling" variants and pricing plans is not something that makes sense for us (we think). They would never be reused elsewhere (that's more or less how I decide to embed data in a document or to create a new document type which will be referenced).
Apr 20, 2023, 10:04 AM

Sanity– build remarkable experiences at scale

Sanity is a modern headless CMS that treats content as data to power your digital business. Free to get started, and pay-as-you-go on all plans.

Was this answer helpful?