Is there any way to get all records by given value in a list using CrudRepository from DynamoDB?

Question

I'm trying to get all the records from 'Messages' table but the problem is 'roles' are saving as an array in dynamo DB and I need to get all records which having the "INSTRUCTOR" role of each record.

Available records are as follows in dynamoDB.

Record 1

{ "product": "Maths", "messageSummary": "test message", "roles": [ "ADMIN", "INSTRUCTOR" ], "title": "My course1", "createdBy": 0, "authorName": "test author", "id": "1" }

Record 2

{ "product": "Maths", "messageSummary": "test message", "roles": [ "STUDENT" ], "title": "My course2", "createdBy": 0, "authorName": "test author", "id": "2" }

Record 3

{ "product": "Maths", "messageSummary": "test message", "roles": [ "INSTRUCTOR", "STUDENT" ], "title": "My course3", "createdBy": 0, "authorName": "test author", "id": "3" }

Message model class as follows which referring "Messages" table

@DynamoDBTable(tableName = "Messages")
public class Message {

    @Id
    @ApiModelProperty(accessMode = ApiModelProperty.AccessMode.READ_ONLY, position = 1)
    private String id;

    /* authorName, messageSummary, title .. attributes goes here */

    @ApiModelProperty(required = true, allowableValues = "maths", position = 6)
    private Product product;

    @ApiModelProperty(allowableValues = "student, instructor, admin", position = 7)
    private List<Role> roles;


    // getters and setters
}

Message repository as follows which extends CrudRepository

@EnableScan
public interface MessageRepository extends CrudRepository<Message, String> {

    List<Message> findMessageByProductAndRoles(Product product, List roles); // Need to filter by given role
}

Since I need to get all the records that have INSTRUCTOR role, Record 1 and Record 3 should be in the result list. However, I can filter it only using the product value but am not able to filter using roles.

Additionally, I tried this using some keywords such as Contains, Like, In etc. but those are not succeeded for me. as per my observation, those keywords are not supporting to filter a specific value from an array.

And getting this error:

{
    "timestamp": "2022-01-27T08:42:54.786+0000",
    "status": 500,
    "error": "Internal Server Error",
    "message": "Request processing failed; nested exception is com.amazonaws.services.dynamodbv2.model.AmazonDynamoDBException: One or more parameter values were invalid: ComparisonOperator CONTAINS is not valid for SS AttributeValue type (Service: AmazonDynamoDBv2; Status Code: 400; Error Code: ValidationException; Request ID: xxxxxxx)",
    "path": "/api/xxx/my route"
}

How can I achieve that and implement such conditions using the CrudRepository?

Answer 1

After trying many options to query for the database, I was able to get relevant records lists by implementing GSI in DynamoDB. Since I'm using java I have used QuerySpec to get them.

QuerySpec spec = new QuerySpec()
                .withKeyConditionExpression("lifeCycle = :life_cycle AND startDate < :s_date")
                .withFilterExpression("contains (#user_roles, :roles))
                .withNameMap(expressionAttributeNames)
                .withValueMap(new ValueMap()
                        .withString(":life_cycle", lifeCycle)
                        .withString(":roles", userRole)
                        .with(":s_date", startDate)
                );

        ItemCollection<QueryOutcome> items = index.query(spec);