Using SQL Functions, Clauses, and Joins

You can use standard SQL clauses, such as WHERE and ORDER BY, to elaborate on this kind of simple query:

0: jdbc:drill:zk=local> select id, type from dfs.`/Users/brumsby/drill/donuts.json`
where id>0
order by id limit 1;

|------------|------------|
|     id     |    type    |
|------------|------------|
| 0001       | donut      |
|------------|------------|

1 row selected (0.318 seconds)

You can also join files (or tables, or files and tables) by using standard syntax:

0: jdbc:drill:zk=local> select tbl1.id, tbl1.type from dfs.`/Users/brumsby/drill/donuts.json` as tbl1
join
dfs.`/Users/brumsby/drill/moredonuts.json` as tbl2
on tbl1.id=tbl2.id;

|------------|------------|
|     id     |    type    |
|------------|------------|
| 0001       | donut      |
|------------|------------|

1 row selected (0.395 seconds)

Equivalent USING syntax and joins in the WHERE clause are also supported.

Standard aggregate functions work against JSON data. For example:

0: jdbc:drill:zk=local> select type, avg(ppu) as ppu_sum from dfs.`/Users/brumsby/drill/donuts.json` group by type;

|------------|------------|
|    type    |  ppu_sum   |
|------------|------------|
| donut      | 0.55       |
|------------|------------|

1 row selected (0.216 seconds)

0: jdbc:drill:zk=local> select type, sum(sales) as sum_by_type from dfs.`/Users/brumsby/drill/moredonuts.json` group by type;

|------------|-------------|
|    type    | sum_by_type |
|------------|-------------|
| donut      | 1194        |
|------------|-------------|

1 row selected (0.389 seconds)