Working with nested JSON files

Say you'd like to read from a JSON file that holds more JSON in it.

In such cases, you can create the ext_table as a single row:

CREATE EXTERNAL TABLE nested_json_ext(src TEXT NOT NULL)[CREDENTIALS = (<awsCredentials>)]URL = 's3://<bucket_name>[/<folder>][/...]/'OBJECT_PATTERN = '<object_pattern>'[, '<object_pattern>'[, ...n]]TYPE = (JSON PARSE_AS_TEXT = TRUE)[COMPRESSION = <compression_type>]

Then extract the fields and the values as the following:

SELECTJSON_EXTRACT_RAW(src,'/nested_field_name_1'),[ JSON_EXTRACT_RAW(src,'/nested_field_name_2'),  JSON_EXTRACT_RAW(src,'/nested_field_name_x')  ]FROM nested_json_ext

If there are fields that are not nested, you can use JSON_EXTRACT for these values instead of JSON_EXTRACT_RAW.