Redash: Failed to run mongodb query

[quaninte]

When I try to run this mongodb on both local install or redash demo, a error raised "Error running query: string indices must be integers"

Below is the query, I think this is an error of python code since I can run other simple query just fine.

{
    "collection": "Email",
    "aggregate": [
        {
            "$group": {
                "_id": {
                    "$dateToString": {
                        "format": "%Y-%m-%d",
                        "date": "$sendTime"
                    }
                },
                "sent": {
                    "$sum": {
                        "$cond": {
                            "if": {
                                "$gte": [
                                    "$sent",
                                    1
                                ]
                            },
                            "then": 1,
                            "else": 0
                        }
                    }
                },
                "opened": {
                    "$sum": {
                        "$cond": {
                            "if": {
                                "$gte": [
                                    "$opened",
                                    1
                                ]
                            },
                            "then": 1,
                            "else": 0
                        }
                    }
                },
                "clicked": {
                    "$sum": {
                        "$cond": {
                            "if": {
                                "$gte": [
                                    "$clicked",
                                    1
                                ]
                            },
                            "then": 1,
                            "else": 0
                        }
                    }
                }
            }
        },
        {
            "$limit": 10
        }
    ]
}

[quaninte]

I also tried to make a script with pymongo and this query condition works fine
http://pastebin.com/GSCmdfJB

[quaninte]

Trace

02:16:19 worker.1 | Traceback (most recent call last):
02:16:19 worker.1 |   File "/usr/local/lib/python2.7/site-packages/celery/app/trace.py", line 240, in trace_task
02:16:19 worker.1 |     R = retval = fun(*args, **kwargs)
02:16:19 worker.1 |   File "/Users/quanmt/PycharmProjects/redash/redash/tasks.py", line 24, in __call__
02:16:19 worker.1 |     return super(BaseTask, self).__call__(*args, **kwargs)
02:16:19 worker.1 |   File "/usr/local/lib/python2.7/site-packages/celery/app/trace.py", line 437, in __protected_call__
02:16:19 worker.1 |     return self.run(*args, **kwargs)
02:16:19 worker.1 |   File "/Users/quanmt/PycharmProjects/redash/redash/tasks.py", line 297, in execute_query
02:16:19 worker.1 |     data, error = query_runner.run_query(annotated_query)
02:16:19 worker.1 |   File "/Users/quanmt/PycharmProjects/redash/redash/query_runner/mongodb.py", line 188, in run_query
02:16:19 worker.1 |     self._fix_dates(query_data)
02:16:19 worker.1 |   File "/Users/quanmt/PycharmProjects/redash/redash/query_runner/mongodb.py", line 160, in _fix_dates
02:16:19 worker.1 |     self._fix_dates(data[k][i])
02:16:19 worker.1 |   File "/Users/quanmt/PycharmProjects/redash/redash/query_runner/mongodb.py", line 162, in _fix_dates
02:16:19 worker.1 |     self._fix_dates(data[k])
02:16:19 worker.1 |   File "/Users/quanmt/PycharmProjects/redash/redash/query_runner/mongodb.py", line 162, in _fix_dates
02:16:19 worker.1 |     self._fix_dates(data[k])
02:16:19 worker.1 |   File "/Users/quanmt/PycharmProjects/redash/redash/query_runner/mongodb.py", line 162, in _fix_dates
02:16:19 worker.1 |     self._fix_dates(data[k])
02:16:19 worker.1 |   File "/Users/quanmt/PycharmProjects/redash/redash/query_runner/mongodb.py", line 162, in _fix_dates
02:16:19 worker.1 |     self._fix_dates(data[k])
02:16:19 worker.1 |   File "/Users/quanmt/PycharmProjects/redash/redash/query_runner/mongodb.py", line 162, in _fix_dates
02:16:19 worker.1 |     self._fix_dates(data[k])
02:16:19 worker.1 |   File "/Users/quanmt/PycharmProjects/redash/redash/query_runner/mongodb.py", line 160, in _fix_dates
02:16:19 worker.1 |     self._fix_dates(data[k][i])
02:16:19 worker.1 |   File "/Users/quanmt/PycharmProjects/redash/redash/query_runner/mongodb.py", line 158, in _fix_dates
02:16:19 worker.1 |     if isinstance(data[k], list):
02:16:19 worker.1 | TypeError: string indices must be integers

[arikfr]

The stack trace clearly indicates a bug in re:dash code, which is also explains why running this query w/ PyMongo worked.

I need to make sure when I get the chance to properly debug this, but I think I can see the bug: when _fix_dates is called for the $gte node, it calls for _fix_dates on a string, which tries to iterate it with for k in data, and then fails with the error you get.

I can fix this when I'm back from my vacation (September 1st). If you have a chance to fix this before, pull requests are always appreciated :-)

cc: @erans

[quaninte]

Thank for the instruction, I'm new to python and that helped me debug the issue.
I created a PR https://github.com/EverythingMe/redash/pull/543

[mderazon]

Getting the same error

{
  "collection": "places",
  "aggregate": [
    {
      "$group": {
        "_id": {"place_id": "$place_id", "place_name": "$place_name"},
        "count": { "$sum": 1 }
      }
    },
    {
      "$project": {
        "_id": "$_id.place_id",
        "name": "$_id.place_name",
        "count": "$count"
      }
    },
    {
      "$sort": {
        "count": -1
      }
    }
  ]
}

Fails on on the $sort operation with the same error message. (If I remove $sort it works fine)
Works fine in mongo shell

re:dash 0.9.1+b1377

[erans]

The format we use is slightly different in order to be able to maintain the order of fields, similar to what we do in regular queries.

Therefore, the query should look something like this:

{
  "collection": "places",
  "aggregate": [
    {
      "$group": {
        "_id": {"place_id": "$place_id", "place_name": "$place_name"},
        "count": { "$sum": 1 }
      }
    },
    {
      "$project": {
        "_id": "$_id.place_id",
        "name": "$_id.place_name",
        "count": "$count"
      }
    },
    {
     "$sort" : [
                {
                    "name" : "count",
                    "direction" : -1
                }
            ]
    }
  ]
}

You can see more examples here:
http://docs.redash.io/en/latest/usage/mongodb_querying.html

[mderazon]

I must have missed that.

Thanks!