CODEWITHSUNDEEP
jsonamazon-web-servicesamazon-dynamodb
user375868
user375868

Reputation: 1378

Is it possible to query JSON data in DynamoDB?

Let's say my JSON looks like this (example provided here) -

{
    "year" : 2013,
    "title" : "Turn It Down, Or Else!",
    "info" : {
        "directors" : [
            "Alice Smith",
            "Bob Jones"
        ],
        "release_date" : "2013-01-18T00:00:00Z",
        "rating" : 6.2,
        "genres" : [
            "Comedy",
            "Drama"
        ],
        "image_url" : "http://ia.media-imdb.com/images/N/O9ERWAU7FS797AJ7LU8HN09AMUP908RLlo5JF90EWR7LJKQ7@@._V1_SX400_.jpg",
        "plot" : "A rock band plays their music at high volumes, annoying the neighbors.",
        "rank" : 11,
        "running_time_secs" : 5215,
        "actors" : [
            "David Matthewman",
            "Ann Thomas",
            "Jonathan G. Neff"
       ]
    }
}

I would like to query all movies where genres contains Drama.

I went through all of the examples but it seems that I can query only on hash key and sort key. I can't have JSON document as key itself as that is not supported.

Upvotes: 13

Views: 28696

Answers (3)

user3381943
user3381943

Reputation: 131

One example that I took from AWS Developer Forums is as follows.

We got some hints for you from our team. Filter/condition expressions for maps have to have key names at each level of the map specified separately in the expression attributeNames map.

Your expression should look like this:

{
    "TableName": "genericPodcast",
    "FilterExpression": "#keyone.#keytwo.#keythree = :keyone",
    "ExpressionAttributeNames": {
      "#keyone": "attributes",
      "#keytwo": "playbackInfo",
      "#keythree": "episodeGuid"
    },
    "ExpressionAttributeValues": {
      ":keyone": {
        "S": "podlove-2018-05-02t19:06:11+00:00-964957ce3b62a02"
      }
    }
}

Upvotes: 3

user8070240
user8070240

Reputation: 

    String filterExpression = "coloumnname.info.genres= :param";
    Map valueMap = new HashMap();
    valueMap.put(":param", "Drama");
    ItemCollection scanResult = table
        .scan(new ScanSpec().
            withFilterExpression(filterExpression).
            withValueMap(valueMap));

Upvotes: 4

Udo Held
Udo Held

Reputation: 12538

You cannot. DynamoDB requires that all attributes you are filtering for have an index.

As you want to query independently of your main index, you are limited to Global Secondary Indexes.

The documentation lists on what kind of attributes indexes are supported:

The index key attributes can consist of any top-level String, Number, or Binary attributes from the base table; other scalar types, document types, and set types are not allowed.

Your type would be an array of Strings. So this query operation isn't supported by DynamoDB at this time.

You might want to consider other NoSQL document based databases which are more flexible like MongoDB Atlas, if you need this kind of querying functionality.

Upvotes: 13

Related Questions