JSON Tables

A JSON table is a collection of JSON documents stored in an optimized format in HPE Ezmeral Data Fabric Database. JSON tables support complex schema, like JSON files including nested and repeated types, but with additional support for more data types.

JSON tables leverage the OJAI API to natively support Drill data types making it possible for HPE Ezmeral Data Fabric Database to recognize, store, and interpret each of the Drill data types. This alleviates the need to encode data when an application writes to tables or use conversion functions when running queries against tables. For example, if a number or date is stored in a JSON table, you do not need to use the CAST or CONVERT functions for the query to return the actual values.

HPE Ezmeral Data Fabric Database's native support for Drill data types enables Drill to push down filters and projections to HPE Ezmeral Data Fabric Database which optimizes performance.

Querying a JSON Table

Querying JSON tables is simpler than querying binary tables because you do not have to include conversion functions in the queries to change the byte sequences into specific data types, and you do not have to include column families.

The following query examples show query results on a JSON table named “students” in HPE Ezmeral Data Fabric Database. Note that Drill returns human readable values without having to include the CAST or CONVERT functions in the queries.

Example 1

SELECT * FROM dfs.`/user/root/json/students`;
+-----------+-------------+--------+--------+-------------------+----------+
|    _id    |    date     |  name  | state  |      street       | zipcode  |
+-----------+-------------+--------+--------+-------------------+----------+
| student1  | 2016-01-15  | Alice  | CA     | 123 Ballmer Av    | 12345    |
| student2  | 2016-03-08  | Bob    | CA     | 1 Infinite Loop   | 12345    |
| student3  | 2015-12-22  | Frank  | CA     | 435 Walker Ct     | 12345    |
| student4  | 2015-09-15  | Mary   | CA     | 56 Southern Pkwy  | 12345    |
+-----------+-------------+--------+--------+-------------------+----------+
4 rows selected (0.233 seconds)

Example 2

SELECT _id, `date`, name, state, street, zipcode FROM dfs.`/user/root/json/students`;
+-----------+-------------+--------+--------+-------------------+----------+
|    _id    |    date     |  name  | state  |      street       | zipcode  |
+-----------+-------------+--------+--------+-------------------+----------+
| student1  | 2016-01-15  | Alice  | CA     | 123 Ballmer Av    | 12345.0  |
| student2  | 2016-03-08  | Bob    | CA     | 1 Infinite Loop   | 12345.0  |
| student3  | 2015-12-22  | Frank  | CA     | 435 Walker Ct     | 12345.0  |
| student4  | 2015-09-15  | Mary   | CA     | 56 Southern Pkwy  | 12345.0  |
+-----------+-------------+--------+--------+-------------------+----------+
4 rows selected (1.033 seconds)