dynamodb: how to filer all items which do not have a certain attribute?

Question

I have a table of users with a primary hash key of userId. each user may/may not have a string attribute called "environment". I would like to get all the users which have "environment"="xyz" or which do not have the "environment" attribute.

The following code will filter those users with environment=xyz, but how do I filter those items with no environment at all? Dynamo API will not allow to filter on an empty String.

    AmazonDynamoDBClient client = DbClientManager.getDynamoDbClient();

    ArrayList<AttributeValue> avList = new ArrayList<AttributeValue>();
    avList.add(new AttributeValue().withS("xyz"));

    Condition scanFilterCondition = new Condition()
            .withComparisonOperator(ComparisonOperator.EQ.toString())
            .withAttributeValueList(avList);
    Map<String, Condition> conditions = new HashMap<>();
    conditions.put("environment", scanFilterCondition);

    ScanRequest scanRequest = new ScanRequest()
            .withTableName("users")
            .withAttributesToGet(
                    "userId", 
                    "environment");
            .withScanFilter(conditions);

    ScanResult result = client.scan(scanRequest);

For now I just dropped the scan filter, and I do the filtering client-side. Bit is there any way to do it server side?

Thanks, Aliza

Answer 1

Hope I'm not too late. I've found useful function which you could use in the query. I did not check with ScanRequest but with QueryRequest works as charm.

QueryRequest queryRequest = new QueryRequest()
            .withTableName("YouTableName")
    queryRequest.setFilterExpression(" attribute_not_exists(yourAttributeName) ")
    queryRequest.setExpressionAttributeValues(expressionAttributeValues)
    queryRequest.setExclusiveStartKey(ifYouHave)
    queryRequest.setSelect('ALL_ATTRIBUTES')
    queryRequest.setExpressionAttributeNames(youNames)

attribute_not_exists(yourAttributeName) works with ":aws-sdk:1.11.11" also you could use attribute_exists(yourAttributeName)