Nested JSON Objects in Keen.io

In a collection in Keen.io, I have a property named pours of type list. pours is a list of individual pour objects which contain the properties start_time_of_pour, end_time_of_pour, and pour_amount. However I can't directly query for data stored in this list in the workbench. The workbench only allows me to set the Target Property to the pour list, not the individual pour objects or properties.

Is there a way to access these nested objects and properties in the workbench? Thanks!

Josh from Keen IO here.

There isn't currently a way to query properties of objects contained in lists via the workbench or API. There are, however, a few ways to work around this.

Indexed Properties

You can copy properties in the object list out to a set of indexed properties before you send the event. This results in properties like pour_1_amount, pour_2_amount up to pour_n_amount.

If you do this, you should add a number_of_pours property that represents the size of the pours list. When you're ready to analyze, first query to get the maximum number_of_pours over the range of events. This will be an input into a loop you will run to analyze each indexed property.

Let's say you want to find the total amount of all pours. Here's how'd do that using Ruby and keen-gem, though the concept applies generally.

# within an irb session
require 'keen'   

# get the maximum number of pours to check
maximum_pours_length = 
  Keen.maximum('collection', target_property: 'number_of_pours')

# total pour amount
total_pour_amount = 0

for n in maximum_pours_length
  total_pour_amount = total_pour_amount + 
    Keen.sum('collection', target_property: "pour_#{n}_amount")
end

# print the total
puts total_pour_amount

total_pour_amount will contain the sum of all pour amounts for each list item of each event.

Pre-aggregate Properties

You can aggregate (a.k.a. reduce) all instances of the property down to one: e.g. total_pour_amount, maximum_pour_end_time, etc. This requires you to know what analysis you want to do in advance, but you can then use Keen for all the querying.

Extraction w/ client side processing

You can perform an extraction, and do manual processing on your side. To make the extraction faster you can limit it to certain properties. This option keeps the data model the cleanest, but it does require more work at query time because Keen won't be doing the aggregation. Projects like Miso Dataset can make some of that work easier.