How to set Sort key as Global secondary index (Another Partition key) in DynamoDB..?

Question

I used DynamoDB table with the following fields:

Primary Partition key => user_id

Primary Sort key => conversation_id

Sample table data

+---------+--------------------+ | user_id | conversation_id | +---------+--------------------+ | 10 | aaaa | | 10 | bbbb | | 10 | cccc |
| 11 | aaaa | | 11 | bbbb | | 11 | cccc | +---------+--------------------+

I have two separate queries in dynamodb:

1. To fetch all conversation_id by particular user_id.
   If Input 10 => Output => aaaa, bbbb, cccc
2. How to fetch all user_id from particular conversation_id? If Input aaaa => Output => 10,11

I can get the result for 1st query, but how to fetch the 2nd query result.?

Is it a good practice to fetch data by using Primary Sort key(conversation_id) or

How to assign or create conversation_id as Global secondary index (Another Partition key)..?

Note: I am using PHP (Codeigniter framework)

Answer 1

1) You need to use query to get all the sort keys of a partition key. Please refer the below link.

Query API

Query sample code

2) Create the GSI using AWS CLI command.

Local DynamoDB:-

You may need to delete the endpoint url and include the appropriate region --region us-east-1. Also, please change the table name accordingly.

aws dynamodb update-table --table-name Movies --attribute-definitions file://create_gsi_attributes.json --global-secondary-index-updates file://create_gsi.json --endpoint-url http://localhost:8000

create_gsi_attributes.json:-

Please change the attribute names (and type) to conversation_id and user_id

[{
    "AttributeName": "title",
    "AttributeType": "S"
},
{
    "AttributeName": "yearkey",
    "AttributeType": "N"
}]

create_gsi.json:-

Please change the key schema attribute names to conversation_id and user_id

[{
    "Create": {
        "IndexName": "Movies_Gsi",
        "KeySchema": [{
            "AttributeName": "title",
            "KeyType": "HASH"
        },
        {
            "AttributeName": "yearkey",
            "KeyType": "RANGE"
        }],
        "Projection": {
            "ProjectionType": "ALL"
        },
        "ProvisionedThroughput": {
            "ReadCapacityUnits": 100,
            "WriteCapacityUnits": 100
        }
    }
}]

EDIT:-

Command:-

aws dynamodb update-table --table-name message_participants_tbl --attribute-definitions file://create_gsi_attributes_conversation.json --global-secondary-index-updates file://create_gsi_conversation.json --endpoint-url http://localhost:8000

create_gsi_conversation.json:-

[{
    "Create": {
        "IndexName": "message_participants_tbl_gsi",
        "KeySchema": [{
            "AttributeName": "conversation_id",
            "KeyType": "HASH"
        },
        {
            "AttributeName": "user_id",
            "KeyType": "RANGE"
        }],
        "Projection": {
            "ProjectionType": "ALL"
        },
        "ProvisionedThroughput": {
            "ReadCapacityUnits": 100,
            "WriteCapacityUnits": 100
        }
    }
}]

create_gsi_attributes_conversation.json:-

[{
    "AttributeName": "user_id",
    "AttributeType": "S"
},
{
    "AttributeName": "conversation_id",
    "AttributeType": "S"
}]