DynamoDB intersection select with pagination

Question

I have following DB schema and I'd like to find the best way how to select list of Sorted keys which are common for PK_A and PK_B:

+---------------+---------+
|      PK       | SortKey |
+---------------+---------+
|               | SK_A    |
| PK_A          | SK_B    |
|               | SK_C    |
| - - - - - - - |         |
|               | SK_B    |
| PK_B          | SK_C    |
|               | SK_D    |
+---------------+---------+

so when I do select by PK_A and PK_B it should return me only SK_B and SK_C?

Any help is appreciated.

Are you trying to get PK_A and PK_B items in the same call? – NoSQLKnowHow Aug 17 '20 at 19:53 — NoSQLKnowHow, Aug 17 '20 at 19:53
yes, intersection of their SortKeys actually – Alex Aug 18 '20 at 09:58 — Alex, Aug 18 '20 at 09:58

score 1 · Accepted Answer · answered Aug 18 '20 at 13:17

1

Simple answer, you can't do it (in one call).

Dynamo is not a relational database, operations such as intersection are not supported.

You'd need to query() once for each partition key and then calculate the intersect yourself.

answered Aug 18 '20 at 13:17

Charles

21,637
1
20
44

Gotcha, that was my idea to but wanted to receive some confirmation from other guys:) Do I understand correctly that one of the options is to create another secondary index where key will be PK_A#PK_B and values will be those intersected sorted keys? – Alex Aug 18 '20 at 14:01
I suppose that would work, assuming you could create it. Seems to me if you could create it, you'd already have your answer and wouldn't need it. – Charles Aug 18 '20 at 14:20
Actually I can but the problem is that PK_A and PK_B are parts of search queries and if I want to implement it in this way - number of records will grow exponentially.... – Alex Aug 18 '20 at 16:16

DynamoDB intersection select with pagination

1 Answers1