Search code examples
socratasoda

How to construct a SoQL where clause on a text column using <= operation?

I get an Unrecognized arguments error when trying to apply a <= operation on a SoQL text field. Any help is very much appreciated.

I have tried to apply several different filters on this text field but only the = operation works. When I try any other I get an Unrecognized arguments error.

For example: https://data.sfgov.org/resource/bbb8-hzi6.json?start24=10:00 (works fine) but https://data.sfgov.org/resource/bbb8-hzi6.json?start24<=10:00 (fails)

According to the documentation here https://dev.socrata.com/docs/datatypes/text.html#, <= evaluates strings that are alphanumerically before or equal. I would expect these operations to work on text strings as stated.

Solution

  • The Socrata API uses regular GET parameters. In your example, you're performing simple filtering, which allows you to just perform exact matches.

    https://data.sfgov.org/resource/bbb8-hzi6.json?start24=10:00
    • start24=10:00 has the GET parameter start24 with a value of 10:00

    However, in your example with <=. That means something entirely different in the URL world; this means:

    • start24<=10:00 has the GET parameter start24< with a value of 10:00.

    Since you can't use any other operators such as <=, Socrata uses SoQL queries where special keywords start with $; e.g. $where.

    https://data.sfgov.org/resource/bbb8-hzi6.json?$where=start24<="10:00"

    Now this gets translated to:

    • $where=start24<="10:00" has the GET parameter $where with a value of start24<="10:00"

    Now, this Socrata can handle and will return your results.