1

I have a data dump from Plaid API in DynamoDB. Each transaction has transaction_id, pending(bool), and pending_transaction_id (the FK basically to the older pending transaction it replaces)

{
 "account_id": "acct1", // partition key
 "transaction_id": "txn100", // sort key
 "category_id": "22001000",
 "pending": false,
 "pending_transaction_id": "txn1",
 "amount": 500,
},
{
 "account_id": "acct1",
 "transaction_id": "txn1",
 "category_id": "22001000",
 "pending": true,
 "pending_transaction_id": null,
 "amount": 500,
},

Is it possible to query in a single query only pending transactions that don't have a permanent replacement yet?

In other words, if it was relational DB it would be along the lines select * from txn where pending == false and transaction_id not in (select pending_transaction_id from txn) (or whatever flavor of CTE or left join you prefer).

How do I do this in dynamo db in a single query?

Vitalik
  • 2,724
  • 4
  • 32
  • 43
  • It doesn't help anything. You should share the dynamodb table scheme, so we can analyze from. – Mahdi Ridho Jan 12 '21 at 14:04
  • 1
    DynamoDB isn't designed with comparing (joining) documents (rows) in mind. https://stackoverflow.com/questions/36753861/how-to-join-tables-in-aws-dynamodb – MatBailie Jan 12 '21 at 16:50
  • @MatBailie Thank you Mat, that's what I learned now too after watching numerous tutorials. I will either do this in code or redesign the tables. Thanks – Vitalik Jan 12 '21 at 22:41

1 Answers1

0

We can have a GSI here to solve this problem.

PK (pending) SK (pending_transaction_id) ..
false txn1 ..
true null ..

We can then query over records which PK and get our records.

Points to consider/ observe:

  1. Since SK is null here, record will not be created. This works for us as we don't need those records.
  2. We can include pending = true records in our GSI if required, however that means having "NULL" attribute value.

The advantage with GSI I see here is (considering only pt. 1), we are keeping duplicate records only which we need as part of our query.

Anshul Saraswat Kaul
  • 327
  • 3
  • 11