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
