Reading distinct local secondary index keys of a DynamoDB table

Question

Assume I have staff table with primary key composing of organization_id as partition key and staff_id as sort key. On the other hand I have department local secondary index with department_id as sort key.

{
  ...
  KeySchema: [
    { AttributeName: "organization_id", KeyType: "HASH"},
    { AttributeName: "staff_id", KeyType: "RANGE" }
  ],
  LocalSecondaryIndexes: [
    {
        IndexName: "department",
        KeySchema: [
            { AttributeName: "organization_id", KeyType: "HASH"},
            { AttributeName: "department_id", KeyType: "RANGE" }
        ],
        Projection: {
            ProjectionType: "KEYS_ONLY"
        }
    }
  ],
  AttributeDefinitions: [
    { AttributeName: "organization_id", AttributeType: "S" },
    { AttributeName: "staff_id", AttributeType: "S" },
    { AttributeName: "department_id", AttributeType: "S" }
  ]
  ...
}

As one may easily figure out, there are many items with different staff_id sharing same department index key. I need to query list of departments of an organization with given organization_id. Is there a way to retrieve this list from staff table? I do not prefer to maintain another departments table. I am new to DynamoDB, so if you have any comments/advices on overall table design, all is very welcome.

Harshal Bulsara Harshal Bulsara · Accepted Answer · 2017-09-12T04:41:23

As of now, there is no direct feature of distinct in DynamoDB, but you can achieve this by following ways.

Query you LSI and get all records for organizationId and then at application level you find out distinct values.(This is very common practice in NoSql DB's)
As you have mention create another table, now I would recommend that you create another table that way you can directly retrieve only selected values
CloudSearch: Can be integrated into DynamoDB tables then you can directly search in Cloudsearch instead of a table, but this is useful when you have multiple search queries and millions of records.

Thanks

Reading distinct local secondary index keys of a DynamoDB table

1 Answers