Unable to create arrays in "$group"-aggregation

[kaibs]

First of all: Thank you for the plugin, for the majority of my applications it is working flawlessly!

But with one query, I'm stuck. I've spent the last few hours trying to find the error on my side, so maybe this is a general issue with this plugin. I'm working with a db-collection whose documents have the following (relevant part) format:

{
    timestamp: ISODate('2023-09-20T10:26:42.265Z'),
    statusActivity: 'driving',
    name: 'agv1'
}

My (intermediate) goal is to group the docs by their name and create an array in each group with the following format: [{"state": "$statusActivity, "date": "$timestamp}]

My aggregation-query for this looks the following:

[
  {
    "$group": {
      "_id": "$name",
      "timestamp": { "$last": "$timestamp" },
      "dateArr": {
        "$addToSet": {
          "date": "$timestamp",
          "state": "$statusActivity"
        }
      }
    }
  }
]

I do some $match for time range etc. beforehand and a lot of "post-processing" afterward, but this is the critical part which doesn't work. I always get the following error: Schema Inference Failed: WriteArray can only write a Array while positioned on a Element or Value but is positioned on a TopLevel I've tried countless versions of my query with $push, $addToSet and even $accumulator. Also creating empty arrays beforehand, adding the arrays afterward with a second $group etc. Always the same error message.

My queries work flawlessly on mongoplayground.net (I've linked the playground with my test query) and also in the exact db I use with Grafana when I test the query in MongoDB-Express.

Is this a bug/shortcoming of the plugin? Or am I missing something obvious?

[meln5674]

That error indicates that its trying, and failing, to automatically figure out what the types of your output fields are, that's the "Schema Inference Failed", but the second part, which is what caused it to fail, seems to be coming from mongo itself. My first recommendation is to disable the schema inference in the query options and explicitly specify the field names and types to expect, and if that works, then it would be very helpful if you could provide a handful (5ish) documents that demonstrate the problem, and I can try to debug. If disabling schema inference also produces an error that has that WriteArray can only... it could potentially be related to https://github.com/meln5674/grafana-mongodb-community-plugin/issues/15, where a bug in grafana mistakes mongo query variables for browser javascript variables.

[kaibs]

Thank you for the swift reply!

To be totally honest, I kind of glanced over "Infer Schema" because it just worked for my other Grafana panels. I've now played a little bit around with it, and it at least seems to remove the immediate Schema Inference Failed-error if I deactivate automatic inference. But I can still replicate it.

I'm using the following aggregation-query to create a Table-panel (only for testing). All my observations result from this test db-collection

[
  {
    "$group": {
      "_id": "$name",
      "timestamp": { "$last": "$timestamp" },
      "count": {
        "$sum": 1
      },
      "dateArr": {
        "$addToSet": {
          "date": "$timestamp",
          "state": "$statusActivity"
        }
      }
    }
  }
]

As expected, this doesn't result in an immediate error. I can specify _id as *string and count as *int32, and get correct data for these fields. But I have no idea how I should handle the created array dateArr. I've tried defining date -> *time.Time and state -> *string separately, this results in a correctly sized array [5,2,2], but all fields are null. If I additionally specify dataArr -> *json.RawMessage, I get again a similar error:

Failed to convert document number 0: Failed to extract value columns: Failed to convert value for dateArr (primitive.A{map[string]interface {}{"date":1695205340133, "state":"idle"}, map[string]interface {}{"date":1695205350133, "state":"idle"}}): WriteArray can only write a Array while positioned on a Element or Value but is positioned on a TopLevel, map[_id:agv3 count:2 dateArr:[map[date:1695205340133 state:idle] map[date:1695205350133 state:idle]] timestamp:1695205350133]

Does this maybe don't work in general? I mean, you wrote in the info-box for Value Fields that nested objects are not supported.

[meln5674]

I've managed to reproduce the issue with a test, and I believe I have a fix, I will try to have it implemented and released sometime this week.

[meln5674]

v0.2.0+rc4 is now available, which should resolve this issue.

[kaibs]

Thanks a lot! I will test it tomorrow

[Davasny]

Hello! I think I've just run in similar issue in v0.2.0+rc4

Following group operation works fine:

{
  "$group": {
    "_id": "$_id",
    "roundedTimestamp": "$roundedTimestamp",
    "averageDuration": { "$avg": "$duration" }
  }
},

But when I change _id to something more complicated, panel returns JS error and seems to be not responding (there are no requests to backend)

{
  "$group": {
    "_id": {
      "$concat": [
        {"$toString": "_id"}
      ]
    },
    "roundedTimestamp": "$roundedTimestamp",
    "averageDuration": { "$avg": "$duration" }
  }
},

[Davasny]

After digging into datasource.ts I found out the problem is not in the mongodb-community-plugin. The root cause is in getTemplateSrv().replace from @grafana/runtime. I have reported issue to grafana team but I don't think it will be soon (if ever) fixed https://github.com/grafana/grafana/issues/79212

I suggest that we add switch like "use variables" in QueryEditor which will allow users to work without dashboard variables. What do you think about it?