How to import JSON with nested array

Q&A
, ,
1

I am importing some JSON data into AWS Quick Sight. It does some flattening of the file as described here:

Here is a simplified example of what I am importing:

{
   "contact":{
      "id":"1",
      "level2":[
         {
            "name":"test"
         }
      ]
   }
}

The field id is imported with the name contact.id. However the field level2 is ignored, I presume this is because Quick Sight will not flatten a nested array, although there are no error messages to confirm this.

Does anyone have any idea how I can import this data? I have considered using jsonParse to create a calculated field, however I can cannot even address the contact field, let alone the contact.level2 field.

1 Like
2

List attributes and list objects within a JSON record aren’t supported, and will be skipped during import.

3

Hi Stuart,
Currently you are required to unnest JSON data before pulling into Quick Sight. If you prefer UI based unnesting, you may look into Databrew that allows a single click unnesting. If you prefer script based, you may check ‘Relationalize’ in Glue. Then use the result as your input data to Quicksight.
Thanks.