JSON Tables

The JSON serde is useful for parsing data stored as JSON. The JSON implementation has been borrowed from rcongiu.


{"n_nationkey":"5", "n_name":"ETHIOPIA", "n_regionkey":"0", "n_comment":"ven packages wake quickly. regu" }
{"n_nationkey":"6", "n_name":"FRANCE", "n_regionkey":"3", "n_comment":"refully final requests. regular, ironi" }
{"n_nationkey":"7", "n_name":"GERMANY", "n_regionkey":"3", "n_comment":"l platelets. regular accounts x-ray: unusual, regular acco" }

DDL Statement (AWS Example)

CREATE EXTERNAL TABLE nation_s3_json_openx(
n_nationkey string,
n_name string,
n_regionkey string,
n_comment string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION  's3://qtest-qubole-com/datasets/presto/functional/nation_s3_json';


The features of JSON tables are:

  • Entire JSON document must fit in a single line of the text file.
  • Read the data stored in the JSON format.
  • Convert the data to the JSON format when INSERT INTO table.
  • Arrays and maps are supported.
  • Nested data structures are also supported.

Nested JSON Elements

If your data contains nested JSON elements like this:


You can declare languages as an array<string> and religions as a map<string,array<int> like this (location omitted).

CREATE EXTERNAL TABLE json_nested_test (
     country string,
     languages array<string>,
     religions map<string,array<int>>)
 ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
 LOCATION '<scheme>..'

<scheme> is the Cloud-specific URI and path: for example, s3:// (AWS), wasb:// (Azure blob storage).

You can access a nested element like this

select religions['catholic'][0] from json_nested_test;

Which produces the result