Load and SQL Query DynamoDB Map data type in Athena

Question

I have this item in DynamoDB, it contains a Map attribute:

{
  "orgName": {
    "S": "example"
  },
  "typeSavings": {
    "M": {
      "TELECOMS": {
        "N": "50"
      },
      "GAS": {
        "N": "393"
    }
  }
}

I have exported this DynamoDB data to S3 using the DynamoDB export function.

I can create a table Athena like this:

CREATE EXTERNAL TABLE IF NOT EXISTS mytable (
  Item struct <
  orgName:struct<S:string>,
  typeSavings:MAP<string,string>
  >
)
...

I can select the items in the table like this:

SELECT
    Item.orgname,
    Item.typeSavings
FROM mytable

Where the result is:

orgname               typeSavings 
-------               --------------------
{s=Aaron's Pets Ltd}  {m={"telecoms":{"n":"50"},"gas":{"n":"393"}}}

How can I select items where the gas typeSavings are greater than 50?

I can access the orgName simply enough (e.g. WHERE Item.orgname.s = 'example'), but I am unsure how to access the map data.

I would be interested in solutions where this is solved at the SQL query step, or at the step where I load the data into Athena. For example, I would be happy to load TELECOMS savings and GAS savings into different columns, at which point the SQL query is simple.

Answer 1

I used element_at to access the map and then json_extract to access the json values.

SELECT Item.orgname.S as name,
CAST(json_extract(element_at(Item.typeSavings,'m'),'$.gas.n') AS DOUBLE) AS gassavings
FROM mytable
WHERE gassavings > 50