Store and retrieve nested JSON in DynamoDB with PHP SDK

Question

I have created the following table in DynamoDB:

Field1: messageId / Type: String / Example value: 4873dd28-190a-4363-8299-403c535e160f

Field2: microtime / Type: Number / Example value: 14143960092414

Field3: data / Type: nested JSON-Array / Example value: {"foo":"bar","other":{"nested":1}}

I am performing the following request using PHP SDK for DynamoDB to create an entry

$raw = '{"foo":"bar","other":{"nested":1}}';
$result = $client->putItem(array(
    'TableName' => 'requests2',
    'Item' => array(
        'messageId'   => array('S' => '4873dd28-190a-4363-8299-403c535e160f'),
        'microtime' => array('N' => microtime(true)*10000),
        'data'   => array('S' => $raw),
    )
));

I want then to query the table and filter using variables within the JSON-array data field. Is my above solution to entering the data the right approach? The JSON-array gets stored as string, as to my understanding. Do we need another datatype? Basically, I can already query the table like below to retrieve messages that were added within the last minute:

$iterator = $client->getIterator('Query', array(
    'TableName'     => 'requests2',
    'KeyConditions' => array(
        'messageId' => array(
            'AttributeValueList' => array(
                array('S' => '4873dd28-190a-4363-8299-403c535e160f')
            ),
            'ComparisonOperator' => 'EQ'
        ),
        'microtime' => array(
            'AttributeValueList' => array(
                array('N' => strtotime("-1 minutes")*10000)
            ),
            'ComparisonOperator' => 'GT'
        )
    )
));

foreach ($iterator as $item) {
    echo $item['messageId']['S']." ";
}

But how can I modify my request to allow querying by ANY value within the data-field? For example, filter by only those who have [data][other][nested]=1

I've spent the past hours on this issue and I can't get it to work... I am very grateful for any tips, thanks in advance!

Answer 1

I don't think AWS PHP SDK for DynamoDB has yet implemented the support for JSON based document storage. Their recent notification published on their blog on 8th October 2014, mentions about the support of this new feature only in Java, .NET, Ruby and JS SDK.

Answer 2

I know this was posted in 2014, but I was looking exactly for this answer and so I'd like to share the result in my search to anyone that will land on this question in the future.

Best practice is to store a JSON as a string, but use a Marshaler object to turn the JSON into something that DynamoDB can digest, and that you will be able to query too:

Using marshalJSON method you turn a JSON, as you can see described in this amazon link

For the ones that are looking for a quick example, I add here below the key parts of the procedure:

If you have a JSON like the following

{
  "id": "5432c69300594",
  "name": {
    "first": "Jeremy",
    "middle": "C",
    "last": "Lindblom"
  },
  "age": 30,
  "phone_numbers": [
    {
      "type": "mobile",
      "number": "5555555555",
      "preferred": true
    },
    {
      "type": "home",
      "number": "5555555556",
      "preferred": false
    }
  ]
} 

stored in a string variable $json, you can simply do

use AwsDynamoDbDynamoDbClient;
use AwsDynamoDbMarshaler;

$client = DynamoDbClient::factory(/* your config */);
$marshaler = new Marshaler();

$client->putItem([
    'TableName' => 'YourTable',
    'Item'      => $marshaler->marshalJson($json)
]);