Add MongoDB $elemMatch equivalent for array field queries

[vqhdev]

Problem Statement

PayloadCMS currently lacks support for MongoDB's $elemMatch operator equivalent, which is essential for querying arrays where multiple conditions must match the same array element. This limitation significantly impacts complex data querying scenarios.

Current Limitation

When querying array fields with multiple conditions, PayloadCMS treats each condition independently, which can return incorrect results. For example:

// Current PayloadCMS query - INCORRECT BEHAVIOR
where: {
	and: [{
			'variants.size': {
				equals: 'L'
			}
		},
		{
			'variants.color': {
				equals: 'red'
			}
		},
		{
			'variants.inStock': {
				equals: true
			}
		}
	]
}

This query might return a product where:

• One variant has size: 'L' and color: 'blue'
• Another variant has size: 'M' and color: 'red' and inStock: true

Even though no single variant matches all three conditions.

Use Cases

This affects many real-world scenarios:

1. E-commerce: Find products with variants matching specific size, color, and availability
2. User Management: Query users with specific permissions within a particular role
3. Content Management: Find posts with tags meeting multiple criteria simultaneously
4. Inventory Systems: Locate items with specific attributes in the same record

Proposed Solution

Add support for an elemMatch operator similar to MongoDB:

// Proposed PayloadCMS syntax
where: {
  variants: {
    elemMatch: {
      size: { equals: 'L' },
      color: { equals: 'red' },
      inStock: { equals: true }
    }
  }
}

Current Workarounds and Their Problems

1. Client-side filtering: Poor performance and increased bandwidth usage
2. Multiple separate queries: Complex logic and potential race conditions
3. MongoDB native queries: Breaks PayloadCMS abstraction and type safety
4. Data structure changes: Often impractical for existing applications

Technical Considerations

• Multi-database support: Should work with both MongoDB and PostgreSQL adapters
• GraphQL compatibility: Must integrate with existing GraphQL API
• Type safety: Maintain TypeScript support for complex query structures
• Performance: Leverage database-native optimizations when possible

Impact

This feature would:

• Enable accurate complex array queries
• Improve application performance by reducing client-side processing
• Maintain data query accuracy and integrity
• Enhance PayloadCMS competitiveness with other headless CMS solutions

References

• MongoDB $elemMatch documentation: https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch/
• Related community discussions: ArrayField operations #615, Support for an Every Operator (like $elemMatch) #1751

This feature would significantly improve PayloadCMS query capabilities and make it more suitable for complex enterprise applications requiring precise array filtering.