Spring Boot CrudRepository如何查询数组属性匹配的对象列表

Hey there, let's work through this issue you're hitting with Spring Boot's CrudRepository and Azure Cosmos DB. The error you're seeing happens because Spring Data's auto-generated query for nested list properties creates parameter names with dots (like @childShipToAccounts.primaryAccountId), which Cosmos DB doesn't allow in parameter identifiers.

Here's how to fix this properly: #

1. Use a Custom @Query with Cosmos DB's ARRAY_CONTAINS Function

Instead of relying on the auto-generated method name, define a custom query using Cosmos DB's built-in ARRAY_CONTAINS function. This lets you explicitly control the parameter naming and query logic.

Update your UserREFRepository with this method:

import org.springframework.data.cosmos.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import java.util.List;

public interface UserREFRepository extends CrudRepository<UserREF, String> {

    @Query("SELECT * FROM c WHERE ARRAY_CONTAINS(c.childShipToAccounts, {\"primaryAccountId\": @primaryAccountId}, true)")
    List<UserREF> findAllByChildShipToAccountsPrimaryAccountId(@Param("primaryAccountId") String primaryAccountId);
}

• ARRAY_CONTAINS checks if any element in the childShipToAccounts array matches the object we pass.
• The third parameter true tells Cosmos DB to match on the specified property (primaryAccountId) instead of requiring the entire ShipToAccounts object to be identical.
• @Param("primaryAccountId") ensures the parameter uses a valid identifier without dots, which complies with Cosmos DB's rules.

2. Verify Your Entity Mappings

Make sure your entity classes are correctly mapped for Cosmos DB:

import com.azure.spring.data.cosmos.core.mapping.Document;
import com.azure.spring.data.cosmos.core.mapping.Field;
import org.springframework.data.annotation.Id;
import java.util.List;

@Document(collection = "userRefs") // Replace with your actual collection name
public class UserREF {
    @Id
    private String id;
    
    @Field("childShipToAccounts")
    private List<ShipToAccounts> childShipToAccounts;

    // Getters and setters
}

// Embedded entity (no @Document annotation needed)
public class ShipToAccounts {
    private String primaryAccountId;
    private String accountId;

    // Getters and setters
}

Why the Auto-Generated Method Failed #

Spring Data Cosmos DB's automatic query generation for nested list properties uses the full property path (like childShipToAccounts.primaryAccountId) as part of the parameter name. Cosmos DB rejects parameter names with dots, hence the Invalid query error you encountered. Using a custom @Query avoids this by using a clean parameter name.

Once you implement this, calling findAllByChildShipToAccountsPrimaryAccountId with your desired primaryAccountId will return all UserREF records where any element in the childShipToAccounts list matches that ID.

内容的提问来源于stack exchange，提问作者adbdkb