'Duplicate keys with Amazon Athena and Open JSONx Serde

I have the following data due to an error:

{
  "eventType": "something",
  "details": {
    "userName": "NotSet",
    "username": "[email protected]"
  },
  "createdAt": 3
}

Creating the table works:

CREATE EXTERNAL TABLE tbl (
         eventType string,
         `createdAt` string,
         details string 
) 
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
 LOCATION 's3://xx/yy'

However when I query (tried details to string, struct, map, always same) I get the duplicate key error:

HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key "username"

They are duplicate if you use them as row columns, but not as map keys, or even string, why does it fail? The org.apache.hive.hcatalog.data.JsonSerDe can skip but I do not like it since %99.5 data is like this. The org.apache.hive.hcatalog.data.JsonSerDe fails always.

Solution 1:^[1]

Presto does not support case sensitive column names (they are always converted to lowercase), so it’s not possible to multiple columns that differ only by case.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	David Phillips

'Duplicate keys with Amazon Athena and Open JSONx Serde

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]