Sudhakar opened an issue for this, so I responded there. Steven is right, this is the current expected functionality, but I discuss there the reasons for it and opened the discussion for use cases that need this functionality.
On Tue, Feb 3, 2015 at 2:28 PM, Steven Phillips <[email protected]> wrote: > I think flatten is functioning as designed, but perhaps we need to treat > this case differently. It might make sense to return a single row with a > null for the flattened column in this case. The only model for "flatten" > that I know of is google Big Query. We should see how they handle this > case. > > On Tue, Feb 3, 2015 at 12:27 PM, MapR <[email protected]> wrote: > > > In a table, column with null values we > > present as nulls or empty values with other columns. I never had an > > experience with c1, c2, c3 (columns ) not presented when c3 has null > values > > even though c1, c2 have notnull values. > > > > Sudhakar Thota > > Sent from my iPhone > > > > > On Feb 3, 2015, at 11:57 AM, Andries Engelbrecht < > > [email protected]> wrote: > > > > > > 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 > > > > > > > > > -- > Steven Phillips > Software Engineer > > mapr.com >
