In listing 3 it looks like flatten is doing the correct thing by representing each element in the array with a new row and the element in the column output. And also for arrays that are empty there is no output row.
—Andries On Feb 3, 2015, at 11:54 AM, Sudhakar Thota <[email protected]> wrote: > It will be more appropriate if I add my file to have data to facilitate quick > test. > > [root@maprdemo clicks]# cat sthota_test_1.json > {"a":"r1cl1", "b":"r1c2","c":{ "x" : 1, "y" : "a string", "z" : [ 1] }} > {"a":"r2cl1", "b":"r2c2","c":{ "x" : 2, "y" : "a string", "z" : [ 1,2] }} > {"a":"r3cl1", "b":"r3c2","c":{ "x" : 3, "y" : "a string", "z" : [ 1,2,3] }} > {"a":"r4cl1", "b":"r4c2","c":{ "x" : 4, "y" : "a string", "z" : [ 1,2,3,4] }} > {"a":"r5cl1", "b":"r5c2","c":{ "x" : 5, "y" : "a string", "z" : [ 1,2,3,4,5] > }} > {"a":"r6cl1", "b":"r6c2","c":{ "x" : 6, "y" : "a string" }} > > > Begin forwarded message: > >> From: Sudhakar Thota <[email protected]> >> Subject: Drill - Flatten function - help please >> Date: February 3, 2015 at 11:52:39 AM PST >> To: [email protected] >> >> Hi Drillers, >> >> I have a request here about flatten function. I want to know if this is an >> expected behavior of drill flatten function with json documents. >> >> Listing-1 is contents of my json collection where I have intentionally >> included a empty array. >> Listing-2 shows that if I don’t call flatten function it shows all records. >> Listing-3 shows that if I call flatten function, row6 (r6cl1…) is ignored. I >> am using that in my projections not in predicate, but it looks like treated >> as predicate. >> >> Please correct me if I am wrong and let me know if there is any work around. >> >> Listing:1 >> >> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, t.c.z from >> dfs.`/data/nested/clicks/sthota_test_1.json` as t; >> +------------+------------+------------+------------+------------+ >> | a | b | EXPR$2 | EXPR$3 | EXPR$4 | >> +------------+------------+------------+------------+------------+ >> | r1cl1 | r1c2 | 1 | a string | ["1"] | >> | r2cl1 | r2c2 | 2 | a string | ["1","2"] | >> | r3cl1 | r3c2 | 3 | a string | ["1","2","3"] | >> | r4cl1 | r4c2 | 4 | a string | ["1","2","3","4"] | >> | r5cl1 | r5c2 | 5 | a string | ["1","2","3","4","5"] | >> | r6cl1 | r6c2 | 6 | a string | [] | >> +------------+------------+------------+------------+------------+ >> 6 rows selected (0.125 seconds) >> 0: jdbc:drill:> >> >> Listing:2 >> >> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y from >> dfs.`/data/nested/clicks/sthota_test_1.json` as t; >> +------------+------------+------------+------------+ >> | a | b | EXPR$2 | EXPR$3 | >> +------------+------------+------------+------------+ >> | r1cl1 | r1c2 | 1 | a string | >> | r2cl1 | r2c2 | 2 | a string | >> | r3cl1 | r3c2 | 3 | a string | >> | r4cl1 | r4c2 | 4 | a string | >> | r5cl1 | r5c2 | 5 | a string | >> | r6cl1 | r6c2 | 6 | a string | >> +------------+------------+------------+------------+ >> 6 rows selected (0.163 seconds) >> >> Listing:3 >> >> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, flatten(t.c.z) from >> dfs.`/data/nested/clicks/sthota_test_1.json` as t; >> +------------+------------+------------+------------+------------+ >> | a | b | EXPR$2 | EXPR$3 | EXPR$4 | >> +------------+------------+------------+------------+------------+ >> | r1cl1 | r1c2 | 1 | a string | 1 | >> | r2cl1 | r2c2 | 2 | a string | 1 | >> | r2cl1 | r2c2 | 2 | a string | 2 | >> | r3cl1 | r3c2 | 3 | a string | 1 | >> | r3cl1 | r3c2 | 3 | a string | 2 | >> | r3cl1 | r3c2 | 3 | a string | 3 | >> | r4cl1 | r4c2 | 4 | a string | 1 | >> | r4cl1 | r4c2 | 4 | a string | 2 | >> | r4cl1 | r4c2 | 4 | a string | 3 | >> | r4cl1 | r4c2 | 4 | a string | 4 | >> | r5cl1 | r5c2 | 5 | a string | 1 | >> | r5cl1 | r5c2 | 5 | a string | 2 | >> | r5cl1 | r5c2 | 5 | a string | 3 | >> | r5cl1 | r5c2 | 5 | a string | 4 | >> | r5cl1 | r5c2 | 5 | a string | 5 | >> +------------+------------+------------+------------+------------+ >> 15 rows selected (0.171 seconds) >> >> Thanks >> Sudhakar Thota >> >
