Proper Metabase chart query works but not in Metabase

Get Help
1

Hi !

I'm pretty new with mongo and Metabase. I've done a native query that works great in Mongo Chart. But when I'm creating a native question with the same query I have this error :

readStartArray can only be called when CurrentBSONType is ARRAY, not when CurrentBSONType is DOCUMENT

I've tried to find a solution online for ages but nothing helped me.

Here is my query extracted from MongoDB Chart :

{
  "isFetching": false,
  "isOpen": true,
  "pipeline": [
    {
      "$lookup": {
        "from": "interests",
        "localField": "interest",
        "foreignField": "_id",
        "as": "interest_lookup_interests"
      }
    },
    {
      "$addFields": {
        "interest_lookup_interests": {
          "$ifNull": [
            {
              "$arrayElemAt": [
                "$interest_lookup_interests",
                0
              ]
            },
            null
          ]
        }
      }
    },
    {
      "$group": {
        "_id": {
          "__alias_0": "$interest_lookup_interests.name"
        },
        "__alias_1": {
          "$sum": {
            "$cond": [
              {
                "$ne": [
                  {
                    "$type": "$createdAt"
                  },
                  "missing"
                ]
              },
              1,
              0
            ]
          }
        }
      }
    },
    {
      "$project": {
        "_id": 0,
        "__alias_0": "$_id.__alias_0",
        "__alias_1": 1
      }
    },
    {
      "$project": {
        "value": "$__alias_1",
        "label": "$__alias_0",
        "_id": 0
      }
    },
    {
      "$addFields": {
        "__agg_sum": {
          "$sum": [
            "$value"
          ]
        }
      }
    },
    {
      "$sort": {
        "__agg_sum": -1
      }
    },
    {
      "$project": {
        "__agg_sum": 0
      }
    },
    {
      "$limit": 5000
    }
  ]
}
2

Hi @Cedric
Mongo native queries in Metabase starts and ends with [ ... ], since they are executed inside of the aggregate-function as the pipeline.
Try using the GUI in Metabase, and then convert to native query via Notebook editor > Show native query.

3

Hi Flamber and thanks for your reply!

I already tried that and it returns an other error :

Command failed with error 8000 (AtlasError): 'isFetching is not allowed or the syntax is incorrect, see the Atlas documentation for more information' on server [server name]. The full response is { "ok" : 0, "errmsg" : "isFetching is not allowed or the syntax is incorrect, see the Atlas documentation for more information", "code" : 8000, "codeName" : "AtlasError" }

I've also tried the GUI and I managed to join the two collections. Problem is that all the document from the distant collection is merged into one column. And I can't find a way to split it or just grab the value I need.

4

@Cedric You should only include everything within the pipeline brackets.

Create Views on your MongoDB database (directly, not via Metabase), then Metabase will see those views are regular "tables".

5

Ok great! I'll look into that! :pray: