How to find empty array values in DynamoDB GSI

Question

I have implemented a Global Secondary Index to retrieve all the records from DynamoDB using the given parameters. According to the implementation, I was able to get all the records that are related to the INSTRUCTOR role. But there are some records without having any role. Then I need to get all the records including the INSTRUCTOR role and empty role details.

How can I implement this scenario using Java and how to define to consider the empty array value on filter expression? My code is as follows.

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

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

According to the above implementation, I'm getting only instructor related records only. but the response should include the empty role values as well.

The records have been saved in my table as the following structure and roles can be saved as "roles": []. Then both records should be in the result.

{
  "endDate": "2022-04-21T00:00:00.000Z",
  "roles": [
    "INSTRUCTOR",
    "STUDENT"
  ],
  "createdDate": "2021-09-27T06:46:41.284Z",
  "modifiedDate": "2021-09-27T06:46:41.284Z",
  "id": "1",
  "lifeCycle": "PUBLISHED",
  "startDate": "2021-09-27T00:00:00.000Z"
},

{
  "endDate": "2022-04-21T00:00:00.000Z",
  "roles": [],
  "createdDate": "2021-09-27T06:46:41.284Z",
  "modifiedDate": "2021-09-27T06:46:41.284Z",
  "id": "2",
  "lifeCycle": "PUBLISHED",
  "startDate": "2021-09-27T00:00:00.000Z"
}

it looks like to me that the only reason you're getting results back that have the INSTRUCTOR role is by coincidence. your KeyConditionExprsesion will return results that have the PUBLISHED lifecycle and a certain startDate (you haven't provided the startDate value). you cannot use CONTAINS in a FilterExpression, which i've already explained in this answer — bd_
– bd_, Commented Mar 1, 2022 at 19:30

Sandaka Geethaj Wijesinghe · Accepted Answer · 2022-03-02 06:34:34Z

I was able to get all the records as per the conditions I have mentioned in the question. For that, I have used size option in PartiQL and considered roles are empty which means the attribute's size is 0 and roles can have particular value as well.

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

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

Collectives™ on Stack Overflow

How to find empty array values in DynamoDB GSI

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related