'Declare datatype for nested json in presto

I'm trying to declare a table that reads json files from a s3 bucket. The format of these json files is like these -

[{'metadata': [{'commands': '/test.py',
    'cwds': '/test',
    'service': 'test',
    'hosts': 'test123',
    'name': 'test.py',
    'path': 'test/python3',
    'id': '123',
    'project': 'test'}]}]

I declared like these -

CREATE TABLE IF NOT EXISTS schema.test ( metadata array ) WITH (format = 'JSON', external_location = 's3://test/');```

but when I query the table, the dictionary object is rendered as a list and not as key,value pairs therefore making it useless. I tried declaring it as ARRAY<MAP<VARCHAR>> and got this error - mismatched input '>'. Expecting: '(', ',', 'ARRAY' Also tried this one - array(MAP(varchar)) which also didn't work with this error - Unknown type 'array(MAP(varchar))' for column metadata

sqljsonpresto


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions