2

A bit of background. We are in the design phase of an HTTP API, and we are facing the question of how to define complex queries in JSON format. We have a items in our product that have A LOT of fields and properties, and proper filtering is vital for both the users and the interface built on top of the APIs.

Currently we perform the filtering with SQL queries, but since we need to expose some functionality via HTTP we need to find a sensible alternative. We settled on the idea of having a /search endpoint in our API, where we would like to POST a JSON body with the query.

The doubt that we have is really how to structure the query in JSON format. I looked around the internet and I could not found any standard format for queries are a bit more complex than a couple of conditions.

Let's consider the following made up SQL query:

SELECT code,
   description
FROM TABLE
WHERE end_contract > sysdate
  AND start_contract < sysdate
  AND area NOT NULL
  AND (supplier LIKE '%hey%'
       OR (SIZE = 67
           AND color = 'red'))
ORDER BY code;

I come up with something like this, but it looks a bit clunky and quite verbose.

{
    "fields": [
        "code",
        "description"
    ],
    "orderBy": "code",
    "query": {
        "condition": "and",
        "clauses": [
            {
                "field": "end_contract",
                "operator": ">",
                "value": "currentDate"
            },
            {
                "field": "start_contract",
                "operator": "<",
                "value": "currentDate"
            },
            {
                "field": "area",
                "operator": "not",
                "value": null
            },
            {
                "condition": "or",
                "clauses": [
                    {
                        "field": "supplier",
                        "operator": "like",
                        "value": "*hey*"
                    },
                    {
                        "condition": "and",
                        "clauses": [
                            {
                                "field": "size",
                                "operator": "=",
                                "value": 67
                            },
                            {
                                "field": "color",
                                "operator": "=",
                                "value": "red"
                            }
                        ]
                    }
                ]
            }
        ]
    }
}

What would be a tidy way of defining it in JSON format? Are there any formal or informal standard we can fall back to to define AND and OR relationships (and cases, ideally)?

Cheers!

  • 2
    Not fleshed enough for an answer, but: There is a formal standard of how to structure queries. It's called SQL. Not everything needs to be JSON just for the sake of it. – Florian Peschka Jul 27 '18 at 8:50
  • It's not just for the sake of it. Suppose you want to expose items without exposing the whole database or schema, and empower developers to query items without having to bother setting up database connections, sessions, supporting various database vendors, SQL and non SQL. Do you still think that having a SQL query is a good idea? Would you pass a SQL query in a json call? – GalacticDessert Jul 27 '18 at 9:02
  • 2
    SQL doesn't make any such assumptions. It's a generic language you can use to represent queries upon data. You can publish your schema and developers can use SQL to query it using your API endpoints. You tokenize the query and transfer it into a "real" SQL for your database. I'm not proposing passing the SQL that comes into your API directly to the databse, that would indeed be foolish. I'm proposing taking in "api-SQL" and translating it into "db-SQL", just like you would with your own JSON. Just to think a bit outside the box ;) – Florian Peschka Jul 27 '18 at 9:07
2

One option would be to have your app support the Open Data Protocol (OData). It is a standard based on HTTP and REST. It is used by large-scale services such as Azure Search.

https://www.odata.org/getting-started/basic-tutorial/

It should provide pretty much everything you want to do, and in a common format which makes integration with other systems easier if needed.

1

I always have been in favor of presenting json to language interfaces and then using language constructs to pick needed data. In your case seems like it might have security issues. But you could use it for a prototyping, and then hardcode the queries at server as the interface settles.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.