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:
If there are fields that are not nested, you can use
JSON_EXTRACT for these values instead of