Error in querying embedded json using apache drill

0
votes

donutTest.json (in my local system at /home/dev):

{
   "id":"0001",
   "type":"donut",
   "name":"Cake",

   "batter":{
            "id":"1001",
            "type":"Regular"
           },

   "topping":[
             { "id":"5001", "type":"None"},
             { "id":"5002", "type":"Glazed"}  
             ]
 }

This query is working fine.

 select topping[0].id as topping_id, topping[3].type as topping_type from dfs.`/home/dev/donutTest.json`;

But when I tried:

select batter.id as batter_id, batter.type as batter_type from dfs.`/home/dev/donutTest.json`;

It's showing error.

Table 'batter' not found

topping[0] and batter both are embedded document still error.

1
apache-drill

1 Answers

1
votes

Try using a table alias and then reference that in the select statement.

select donut.batter.id as batter_id, donut.batter.type as batter_type from dfs.`/home/dev/donutTest.json` as donut;

This way Drill has a reference to the actual table alias and then the nested structure underneath.