Time Series Report

[Nigel David]

I'm following along with a video: https://www.youtube.com/watch?v=9McI5TnRcjM&t=200s

Trying to produce a time series data date-picker with one of my collections /vc_db/data.

Using a mark-down card i did this:

# Time Series Report

### Select begin and stop dates

beginDate = ____-__-__ (2017-07-15)

stopDate=____-__-__ (2017-07-17)

graphNodes =

[!``SELECT DISTINCT topic_id FROM `/vc_db/data` ORDER BY topic_id LIMIT 1``]

!``SELECT DISTINCT topic_id FROM `/vc_db/data` ORDER BY topic_id ASC``

And got this:

Next I opened a query card and wrote the following

SELECT 

    ts,

    topic_id,

    value

FROM 

    `/vc_db/data`

WHERE 

    ts >= :beginDate AND 

    ts <= :stopDate AND

    topic_id in (:graphNodes)

ORDER BY ts DESC

But the next card says "selected resource is empty"

And the final card shows the following error:

An error occured when counting the preview data.

Failed to execute SQL^2 query.: MongoDB Error: Command failed with error 16810: 'bad query: BadValue: cannot nest $ under $in' on server localhost:27017. The full response is { "ok" : 0.0, "errmsg" : "bad query: BadValue: cannot nest $ under $in", "code" : 16810 }

Something to do with the "$oid" in the graphNodes maybe$

Thanks

[do...@slamdata.com]

Hi Nigel

You are correct in that the issue resides in trying to use the $oid value. You can get around this by changing your queries to change the topic_id values to strings as shown below.

For the Markdown card change the nodes variable 

graphNodes =
[!``SELECT DISTINCT TO_STRING(topic_id) FROM `/vc_db/data` ORDER BY topic_id LIMIT 1``]
!``SELECT DISTINCT TO_STRING(topic_id) FROM `/vc_db/data` ORDER BY topic_id ASC``

For the SQL card change the query as follows:

SELECT
    ts,
    topic_id,
    value
FROM
    `/vc_db/data`
WHERE
    ts >= :beginDate AND
    ts <= :

stopDate AND
    TO_STRING(topic_id) in (:graphNodes)
ORDER BY ts DESC

Give that a try and let us know if you run into any other issues.

[Nigel David]

Thanks!

I was successful in using a join query so I could select by topic name. But I'm struggling with creating dashboards.