Filtering API query results by date

User Support API
1

The API documentation at https://kc.kobotoolbox.org/api/v1/data shows an example that filters form submission data by date:

curl -X GET 'https://kc.kobotoolbox.org/api/v1/data/22845?query={"date": {"gt$": "2014-09-29T01:02:03+0000"}}'

Even allowing for the MongoDB documentation stating that the greater than operator is actually $gt rather than gt$, has anyone got this to work?

I can’t retrieve any of my data at all, even using a date 2018-01-01T00:00:00+0000.

1 Like
2

Well. I’ve been digging some more, and in the interests of sharing my now working results, here is what I’ve found.

  1. There is an outstanding documentation bug reported well over two years ago at https://github.com/kobotoolbox/kobocat/issues/150, such that the operators are actually $gt rather than gt$, etc.

  2. The date field shown in the example is a red-herring. Rather, the field to be compared must exist in the dataset. I’ve been using _submission_time.

  3. The query must be URL-encoded. Here is an example that works for me with the curl command-line tool

     curl -s -G --user "$username:$password" --data-urlencode query='{"_submission_time":{"$gt":"2019-02-27"}}' https://kc.kobotoolbox.org/api/v1/data/12345

    The date/time value can be either a date (as in the example here) or a more complete date/time such as 2019-02-17T17:00:00+0000 to indicate 5pm UTC on 17th February 2019.

2 Likes
3

Many thanks for this, very helpful.

In case it helps anyone, here’s how to get the forms submitted during a time range, such as a single-day’s submissions. – we use this for pulling yesterday’s submissions in crontab. Doubtless there are other ways, but it an be tricky to pull the pieces together from the doc, as others have also found.

# fetch token from  https://kf.kobotoolbox.org/token/
TOKEN=0123456789012345678901234567890123456789
URL='https://kc.kobotoolbox.org/api/v1/data'
# form id from list at data URL
FORM=123456

# -s silent
# -S show error
# -f fail with error status
# -g no globbing, needed for braces in url

curl -sSkg \
  -X GET \
  -H "Authorization: Token $TOKEN" \
 $URL/$FORM'?query={"$and": [{"_submission_time": {"$gte": "2019-05-28"}},{"_submission_time": {"$lt": "2019-05-29"}}] }'

Kind regards to all

Jonathan.

2 Likes