create_nested_json_table.sql

ADD JAR s3://YOUR_BUCKET_NAME/path/to/json-serde-1.3.7-jar-with-dependencies.jar;

CREATE EXTERNAL TABLE IF NOT EXISTS test.nested_json_table (
  column1 string,
  column2 array<int>,
  column3 struct<
    nestedcolumn1: array<string>,
    nestedcolumn2: array<
      struct<
        morenestedarraycolumn1: int,
        morenestedarraycolumn2: array<string>
      >
    >,
    nestedcolumn3: struct<
      morenestedcolumn1: double,
      morenestedcolumn2: string
    >
  >
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' STORED AS TEXTFILE
LOCATION 's3://YOUR_JSON_DATA_LOCATION/';

select_nested_json_table1.sql

SELECT
  column3.nestedcolumn3.morenestedcolumn1,
  column3.nestedcolumn3.morenestedcolumn2
FROM test.nested_json_table;

select_nested_json_table2.sql

SELECT
  t.morenestedarraycolumn1,
  t.morenestedarraycolumn2
FROM test.nested_json_table
LATERAL VIEW explode(column3.nestedcolumn2) nestedcolumn2 as t;

test_data1.json

{
    "column1": "value",
    "column2": [1, 2, 3, 4, 5],
    "column3": {
        "nestedColumn1": ["value1", "value2", "value3"],
        "nestedColumn2": [
            {
                "moreNestedArrayColumn1": 1,
                "moreNestedArrayColumn2": ["a", "b"]
            },
            {
                "moreNestedArrayColumn1": 2,
                "moreNestedArrayColumn2": ["c", "d"]
            }
        ],
        "nestedColumn3": {
            "moreNestedColumn1": 1.0,
            "moreNestedColumn2": "value"
        }
    }
}

test_data2.json

{"column1":"value","column2":[1,2,3,4,5],"column3":{"nestedColumn1":["value1","value2","value3"],"nestedColumn2":[{"moreNestedArrayColumn1":1,"moreNestedArrayColumn2":["a","b"]},{"moreNestedArrayColumn1":2,"moreNestedArrayColumn2":["c","d"]}],"nestedColumn3":{"moreNestedColumn1":1.0,"moreNestedColumn2":"value"}}}
{"column1":"value","column2":[1,2,3,4,5],"column3":{"nestedColumn1":["value1","value2","value3"],"nestedColumn2":[{"moreNestedArrayColumn1":1,"moreNestedArrayColumn2":["a","b"]},{"moreNestedArrayColumn1":2,"moreNestedArrayColumn2":["c","d"]}],"nestedColumn3":{"moreNestedColumn1":1.0,"moreNestedColumn2":"value"}}}
{"column1":"value","column2":[1,2,3,4,5],"column3":{"nestedColumn1":["value1","value2","value3"],"nestedColumn2":[{"moreNestedArrayColumn1":1,"moreNestedArrayColumn2":["a","b"]},{"moreNestedArrayColumn1":2,"moreNestedArrayColumn2":["c","d"]}],"nestedColumn3":{"moreNestedColumn1":1.0,"moreNestedColumn2":"value"}}}