In cube.js, how would I query over contents of an array?

Question

Let's say I have an array of info (ex: tags: ['red', 'blue', 'green'] ) in my data. How would I add that to my cube(s) to do something like a filter on tags array contains a particular value?

I'm specifically using the Athena driver with pre-aggregation into Aurora Postgres.

This is where I have gotten so far, but it's not quite there yet.

cube(`Events`, {
  sql: `select * from events`,
  joins: {
    Tags: {
      relationship: `hasMany`,
      sql: `${tags}.id = ${tags}`
    }
  },
});
cube(`Tags`, {
  sql: `UNNEST(tags) t (id, idx)`,
  dimensions: {
    tag: {
      sql: `id`,
      type: `string`
    }
  }
});

score 2 · Accepted Answer · answered Feb 26 '20 at 18:23

It's a right direction. Primary keys and select for events table in tags cube should be added:

cube(`Events`, {
  sql: `select * from events`,

  joins: {
    Tags: {
      relationship: `hasMany`,
      sql: `${Events}.id = ${Tags}.id`
    }
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `string`,
      primaryKey: true
    }
  }
});

cube(`Tags`, {
  sql: `select e.id, t.id as tag from events e CROSS JOIN UNNEST(tags) t (id, idx)`,

  dimensions: {
    id: {
      sql: `id || tag`,
      type: `string`,
      primaryKey: true
    },
    tag: {
      sql: `tag`,
      type: `string`
    }
  }
});

Then equals filter on a Tags cube can be used to query it:

{
  "measures": ["Events.count"],
  "filters": [{ 
    "dimension": "Tags.tag",
    "operator": "equals",
    "values": ["red"]
  }]
}

In cube.js, how would I query over contents of an array?

1 Answers1