PartiQL return all rows that match a single row userId

Question

I'm trying to run a PartiQL query in DynamoDB that will return all items in a table that match the userId of 1 item in that table. I tried running the below query, but I'm getting the following error. How can I get this to work?

ValidationException: IN operator must have a left hand argument of type Variable Reference and right hand argument of type Seq with at least one member

SELECT id FROM "mytable" WHERE userId IN (SELECT * FROM "mytable" WHERE "id" = 'a1')

These are the items in my table.

After running the query, I'm only expecting the ids: a1 and a2 to be returned.

Answer 1

PartiQL for DynamoDB is very parti-cular. It doesn’t include nested selects. That’s what that exception is telling you.

DynamoDB isn’t a relational database despite the SQL support, so to wrap your head around how it works it’d be good to search YouTube for Alex DeBrie videos on design patterns.