Check if row contains a nested item in DynamoDB?

Question

I am trying to use PartiQL with DynamoDB to perform SQL queries to check if a device is inactive and contains an error. Here's is the query I am using:

SELECT * 
FROM "table" 
WHERE "device"."active" = 0 AND "device"."error" IS NOT NULL

However I've noticed that even if a device doesn't have the error item, the query still returns a row. How can I query a device that only contains the error item?

With error item

{
    "id": "value",
    "name": "value,
    "device": {
        "active": 0,
        "error": {
            "reason": "value"
        }
    }
}

Without error item

{
    "id": "value",
    "name": "value,
    "device": {
        "active": 0
    }
}

score 2 · Accepted Answer · answered Sep 03 '21 at 10:40

You're looking for IS NOT MISSING :) That's the partiql version of the filter expression operator function attribute_exists.

Given a table with a primary key PK, sort key SK, and the following data:

PK	SK	myMap
foo	1	{}
foo	2	{"test": {}}

-- Returns both foo 1 and foo 2
SELECT * 
FROM "my-table"
WHERE "PK" = 'foo' AND "myMap"."test" IS NOT NULL

-- Returns just foo 2
SELECT * 
FROM "my-table"
WHERE "PK" = 'foo' AND "myMap"."test" IS NOT MISSING

Also made sure my example specifies the PK in the WHERE clause - otherwise, your query will be a full scan. Maybe that's what you want, though. Just something to be aware of.

Check if row contains a nested item in DynamoDB?

1 Answers1