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.