Query nested values and return the doc and only the matching nested docs

rmcdevlast year

5

• 

  reepicheep05last year

  I believe you can. Would have to see a bit more of the architecture to get you a better answer. But I would think you could just query the products with an AND query that filters on shops and price.

  
  

  
  

  Something like this:

  
  
  

  // custom product endpoint at /price/:price
  // query like /api/products/price/10?shops=id1&shops=id2&shops=id3
  // there is probably better ways to pass an array here, just a quick example.
  
  let price = Number(query.params.price)
  let shops = query.params.shops;
  
  const products = await payload.find({
    collection: "pages", // required
    depth: 10,
    where: {
        and: [
          {
            'path.to.price.field': {
              less_than_equal: price,
            },
          },
          {
            "path.to.shop.id": {
              in: shops,
            },
          },
        ],
      },
  });

• 

  rmcdevlast year

  The product is related to the shop not the other way around. So its collection:"shops". I think the query would then return all the shops that match the query and they would have the full list of products within them so I'd have to filter through them manually which is my back up. I've got it structured this way because the relationship field lets our marketer easily reorder items within each shop and they required strict custom ordering of the products in the shop.

• 

  jarrod_not_jared

  last year

  Yes that’s what would happen 👆

  
  
  

  You can easily open a custom endpoint though! That is what I would do for a more complex query like this. Then just filter out the shops you don’t want and return the shops

• 

  rmcdevlast year

  Cool, yep that's what I think we're going to go with. I thought it might be the solution but was just doing some wishful thinking there might be a one step solution.

• 

  jarrod_not_jared

  last year

  It would require aggregations which can come at a query response time cost. I think custom endpoint is the move