Nested Repeated fields in Drill

[David Smith]

I'm trying out Drill for the first time, using MongoDB as a data source.
The Mongo collection I'm querying contains records representing user
accounts for an app. The loginEvents array represents all of the login
sessions for that user. What I want to do is get a count of the Login
events for each user.

Here's my query, and the error I receive:

0: jdbc:drill:zk=local> *SELECT _id, REPEATED_COUNT(loginEvents) as logins
from accounts limit 10;*

*Error: DATA_READ ERROR: Failure while reading ExtendedJSON typed value.
Expected a VALUE_STRING but received a token of type VALUE_NUMBER_INT*

* at [Source:
org.apache.drill.exec.vector.complex.fn.SeekableBAIS@31747d67; line: 1,
column: 205]*


*Line  1*

*Column  206*

*Field  timestamp*

*Fragment 0:0*


*[Error Id: 8bceb901-72c6-4b92-aba0-2243816d7a53 on 10.124.16.217:31010
<http://10.124.16.217:31010>] (state=,code=0)*
Any advice on how to do this?

BTW: here's an example Mongo record:

[image: Inline image 2]


{
  "_id": {
    "$oid": "54d16cf99b5xxxxxce10de"
  },
  "avatar": "xxx",
  "gender": "male",
  "email": "x...@example.com <d...@example.com>”,
  "passwordHash": “xxxxxxxxxx”,
  "salt": "ff3f335d4be1e83d7dcfc995683wer239",
  "firstName": "xxx",
  "lastName": "xxx",
  "role": "lead",
  "facebookId": “xxx”,
  "neighborhoodSince": {
    "$date": "2015-02-03T16:51:05.203-0800"
  },
  "accessTokens": [],
  "created": {
    "$date": "2015-02-03T16:51:05.203-0800"
  },
  "__v": 2,
  "address": {
    "location": {
      "lng": -122.368656,
      "lat": 47.680945
    },
    "formattedAddress": “xxx x st.”,
    "route": “xxxx 9th Ave NW",
    "zip": "98000",
    "country": "US",
    "state": "WA",
    "city": "Seattle"
  },
  "_neighborhood": {
    "$oid": "53e143f358f19e32039dfde9"
  },
  "properties": {
    "confirmedByUser": true,
    "residenceConfirmed": true
  },
  "loginEvents": [
    {
      "event": "login",
      "deviceType": "Android",
      "_id": {
        "$oid": "55eb05c0c2dda0bd1ad61666"
      },
      "timestamp": {
        "$date": "2015-09-05T08:09:52.792-0700"
      }
    },
    {
      "event": "logout",
      "deviceType": "Android",
      "_id": {
        "$oid": "55eb067ec2dda0bd1ad61667"
      },
      "timestamp": {
        "$date": "2015-09-05T08:13:02.031-0700"
      }
    },
    {
      "event": "login",
      "deviceType": "Android",
      "_id": {
        "$oid": "55eb0699c2dda0bd1ad61668"
      },
      "timestamp": {
        "$date": "2015-09-05T08:13:29.949-0700"
      }
    },
    {
      "event": "logout",
      "deviceType": "Android",
      "_id": {
        "$oid": "55eb069dc2dda0bd1ad61669"
      },
      "timestamp": {
        "$date": "2015-09-05T08:13:33.037-0700"
      }
    },
    {
      "event": "logout",
      "deviceType": "Android",
      "_id": {
        "$oid": "55f0cd8bc2dda0bd1ad61c4d"
      },
      "timestamp": {
        "$date": "2015-09-09T17:23:39.938-0700"
      }
    },
    {
      "event": "login",
      "deviceType": "Android",
      "_id": {
        "$oid": "55f0ce33c2dda0bd1ad61c4e"
      },
      "timestamp": {
        "$date": "2015-09-09T17:26:27.099-0700"
      }
    },
    {
      "event": "logout",
      "deviceType": "Android",
      "_id": {
        "$oid": "55f0ce34c2dda0bd1ad61c4f"
      },
      "timestamp": {
        "$date": "2015-09-09T17:26:28.177-0700"
      }
    },
    {
      "event": "logout",
      "deviceType": "Android",
      "_id": {
        "$oid": "55f1d1d5c2dda0bd1ad61d93"
      },
      "timestamp": {
        "$date": "2015-09-10T11:54:13.877-0700"
      }
    },
    {
      "event": "login",
      "deviceType": "Android",
      "_id": {
        "$oid": "55f1d1d5c2dda0bd1ad61d94"
      },
      "timestamp": {
        "$date": "2015-09-10T11:54:13.883-0700"
      }
    },
    {
      "event": "logout",
      "deviceType": "Android",
      "_id": {
        "$oid": "55f1d23dc2dda0bd1ad61d95"
      },
      "timestamp": {
        "$date": "2015-09-10T11:55:57.554-0700"
      }
    }
  ],
  "location": {
    "type": "Point",
    "coordinates": [
      -122.368656,
      47.680945
    ]
  },
  "verifications": {
    "email": [
      {
        "source": "x <d...@zweismith.com>x...@example.com",
        "_id": {
          "$oid": "55ea7023c8bf9a65f28a84a9"
        },
        "dateVerified": {
          "$date": "2015-09-04T21:31:31.951-0700"
        }
      }
    ]
  }
}