Redash: valid json results in not a valid JSON error

[pjvds]

The following valid JSON results in an Error running query: Invalid query format. The query is not a valid JSON. error.

{
    "collection": "event",
    "aggregate": [
        {
            "$project": {
                "month": {
                    "$date": "$startDate"
                }
            }
        }
    ]
}

What gets posted to the http endpoint?

curl 'http://localhost/api/query_results' -H 'Accept: application/json, text/plain, */*' -H 'Cookie: session=.eJwdjjGLg0AQRv_KMXUKXbxGuObYRBRmxDCJzDYhR8yts24TOc4Y8t-zpHp8xft4Dzhdb8Psobyep3nYwGm8QPmAjx8oQVgK0qMndVF4Uuopuqq7k9kF6RttbTOR_R7RutFpXaDu075Ep00Qxsxxl2OkiHzIhevkNSo9FmTJC7v0u4tUyYIqGXFnRMNna_2IKy4SxaChkLigOcaW9wHXOmvtISP1Eatudf32n-zvPflf8NzA3zzc3v2Qw_MFsLVHzw.DG2bqw.U5SBWIiOFT9VhZJjRuLnST96TnY' -H 'Connection: keep-alive' --data-binary '{"data_source_id":1,"query":"{\n    \"collection\": \"event\",\n    \"aggregate\": [\n        {\n            \"$project\": {\n                \"month\": {\n                    \"$date\": \"$startDate\"\n                }\n            }\n        }\n    ]\n}","max_age":0,"query_id":5}' --compressed

What do the logs say?

worker_1    | [2017-08-10 07:22:38,603: INFO/MainProcess] Task redash.tasks.execute_query[9b0c71ad-d792-40c8-9c85-f749baabdfa8] succeeded in 0.0139267686754s: QueryExecutionError('Invalid query format. The query is not a valid JSON.',)

[arikfr]

It does look valid. Do you know if this is an error that comes from Redash or from Mongo? (you can see the stacktrace in the log file)

Are you making the call with curl or from the UI?

[SebC99]

Same issue for me with match:

{
  "$match": {
     "_created_at": {
       "$gte": {
         "$date": "{{fromDate}}"
       },
       "$lt": {
         "$date": "{{toDate}}"
        }
       }
    }
}

Mongo accepts and runs the query without any issue, but Redash doesn't

[stableShip]

+1, same error show up

[kyoshidajp]

@SebC99 What is the value of {{fromDate}} and {{toDate}} parameter?

My query which is using "$match" and "$date" got a correct result.

{
    "collection": "users",
    "aggregate": [
        {
            "$match": {
                "created_at": {
                    "$gte": {
                        "$date": "2017-01-01T15:00:00.000Z"
                    },
                    "$lt": {
                        "$date": "2018-01-01T15:00:00.000Z"
                    }
                }
            }
        }
    ]
}

[arikfr]

The reason you get the invalid JSON message is due to the use of $startDate value for a $date extension. While the error message could be clearer, your JSON query is indeed "invalid".

I'm not sure what the $startDate supposed to be, but it doesn't look like a standard thing.

[SebC99]

@arikfr @kyoshidajp Nope, that's not the issue.
In my case, I'm using parameters in the query, and the only format causing the JSON to be accepted is number. Using Date (as it should be) or Text to match @kyoshidajp example doesn't work

[SebC99]

@arikfr any update on this? This issue shouldn't be closed as it still doesn't work: we can't use date format parameters

[arikfr]

@SebC99 I'm closing this as the new issue you opened (#2362) is clearer and contains the relevant info. Thanks :+1: