How to get one Item by searching from non-key attribute in DynamoDB?

Question

I have a table in DynamoDB and have partition key and a sort key. For example, the table structure is as follows.

// Primary Keys
id -> PK
name -> SK
Info -> json Formatted Attributes

So let's say there are two items in the table,

// Item 1
{
    id: 10245-25-45,
    name: "Name 1",
    info: {
        userId: 1012, // PK
        userName: "ABC", // SK
        type: "type 1",
        date: "2020-01-03 04:05:12"
    }
}

// Item 2
{
    id: 10245-65-70, // pK
    name: "Name 2", // SK
    info: {
        userId: 1013,
        userName: "ABCD",
        type: "type 2",
        date: "2020-01-03 04:10:12"
    }
}

I am using Java as the language and using DynamoDBMapper to do CRUD operations. So I know I can use DynamoDBMapper.load() to get one item by providing PK and SK, but I was wondering how to get data by searching non-key attributes. For example, I need to get One Item at a time by providing,

// userId, userName, type, data are unique values so it will give only one record if exists
find item by userId;
find item by userId and userName;
find item by type and date;

So above are some of my access patterns and basically, I need to get data without providing PK and SK and also I can't use global secondary indexes. So I know there is Query. but I am wondering without that is there any way to do what I need? I really appreciate it if anybody can help me. Thanks in advance.

Answer 1

This is not possible to do in dynamoDB , because of the distributed nature of the data and a full table scan negates the use of this storage option .

if data needs to be searchable a search DB should be used , if the amount of data is small then a SQL type DB can be used.

for a robust solution I would use elastic search (AWS has a managed solution) , in elastic populate only the fields that are searchable , in your case userID, name, date etc and store the id of the document in dynamoDB

so a search API , would query elastic retrieve the item id's , and then retrieve the item from dynamoDB