Nested filtering in array fields when querying

sevetseh283 years ago

9

• 

  jarrod_not_jared

  3 years ago

  Does this thread help?

  https://discord.com/channels/967097582721572934/967097582721572937/1031287576507723948
• 

  sevetseh283 years ago

  in that post they want to filter the documents returned by the API based on an array field but what I meant is filtering whats returned in that field, for example assume this is the full document:

  
  

  {
    "name": "Hello",
    "descriptions"; [
     {
        "language": "spanish",
        "text": "Hola"    
      },
      {
        "language": "english",
        "text": "Hello"    
      }
    }
  }

  
  
  

  I would want to tell the API to give me the document, but only the description that is in english (ie. filter, sort, limit, on the array field):

  
  

  {
    "name": "Hello",
    "descriptions"; [
      {
        "language": "english",
        "text": "Hello"    
      }
    }
  }

• 

  jarrod_not_jared

  3 years ago

  Like they are doing, but a bit

  different

  , right?

  
  
  

  const query = {
    where: {
      'descriptions.language': {
        equals: 'english'
      }
    }
  }

• 

  sevetseh283 years ago

  that where clause you are showing is done to filter the results, but I dont want to filter the results, I just want to filter an array field on each result (on a nested array field) .

  
  
  

  so,for example, I want to get ALL posts (no where clause) .. but for each result, I just want the description that is in english

• 

  jmikrut

  3 years ago

  @834016981093974036

  i see what you're saying, and I believe this GitHub discussion is asking for the same thing:

  
  https://github.com/payloadcms/payload/discussions/428
  
  

  this to me would probably be better done on the frontend though, because right now our querying syntax is meant to be converted to Mongo-specific querying, and in this case, filtering on arrays is not Mongo, but rather just straight JavaScript done against the results

  returned from Mongo
  
  

  so I don't think that this will be supported in the near term as it comes with a lot of complexity and can be done after you retrieve the document itself from the DB (that's the expensive part)

  
  
  

  how many array rows do you have?

• 

  sevetseh283 years ago

  Our use case wants to specifically avoid returning everything to the client (frontend) since we expect the array to contain lots of data. I also expect Document based databases like mongo to leverage this, unlike relational dbs, they are actually really good at doing this so I dont agree with the fact that this is not mongo. We dont want to model relationships through references to other tables/collections, otherwise we would use a relational database. For me the whole purpose of using a document db is that we could have a whole aggregate in a single document (fully denormalized). In the case of Mongo this specific query could done with a query like this:

  
  

  db.getCollection("pages").find({},
      {
          descriptions:
              {
                  $elemMatch:
                      {
                          "human_language": { $eq: "de-DE" }
                      }
              }
      })

  
  

  or even with the Mongo aggregation pipeline is extremely powerful for these cases and even crazier ones that would be unachievable using relational DBs .

• 

  jmikrut

  3 years ago

  fair enough

  
  
  

  could you solve your use case with field hooks?

  
  
  

  at least, until we got a bit further down the line with implementing an idea like this?

  
  
  

  you could also open up a custom endpoint, and leverage the Mongoose model directly to query your data

  
  
  

  you can access the Mongoose models for each collection by doing

  
  
  payload.collections[collectionSlug].Model
• 

  sevetseh283 years ago

  Im gonna take a look at that option! Thanks for the info 🙏 the tool is awesome btw, would love to find some time to contribute on features like this , I believe using mongo opens up so many really nice opportunities

• 

  jmikrut

  3 years ago

  I agree! and thank you!

  
  
  

  I think that this feature could quite easily go to the core at some point too