Search code examples
node.jsmongodbcube.jsmongodb-connector-for-bi

Design a counting measure of the element number in a MongoDB array property in cube.js

I'm using cube.js with MongoDB through MongoDB Connector for BI and MongoBI Driver and so far so good. I'd like to have a cube.js numerical measure that counts the element length from a MongoDB array of object nested property. Something like:

{
  "nested": {
    "arrayPropertyName": [
      {
        "name": "Leatha Bauch",
        "email": "[email protected]"
      },
      {
        "name": "Pedro Hermiston",
        "email": "[email protected]"
      }
    ]
  }
}

I wasn't able to figure that out looking at the docs and I was wondering if that is even possible.

I tried with type: count:

    MyNestedArrayPropertyCounter: {
      sql: `${CUBE}.\`nested.arrayPropertyName\``,
      type: `count`,
      format: `number`,
    },

but I'm getting

Error: Error: Unknown column 'nested.arrayPropertyName' in 'field list'

Any help/advice is really appreciated. Thanks

Solution

  • BI treats nested arrays as separate relational tables. See https://www.mongodb.com/blog/post/introducing-the-mongodb-connector-for-bi-20

    That's why you get unknown column error, it's not part of the parent document table.

    So my guess you have to build schema on the nested array and then build measure count with dimension on parent object id.

    Hope it halps.