Exploring new approaches to queries

[abnegate]

We've been brainstorming on enhancing our query capabilities in Appwrite, particularly around ways we can support nested queries.

As a result, we're considering some significant changes to our query serialization process and would love your input. This is an important decision, and we want to ensure all voices are heard.

To allow nested queries, our idea is for the SDK code to look something like this:

Query.or([
    Query.startsWith('name', 'jake'),
    Query.greaterThan('followers', 10000)
])

Where the queries nested inside the Query.or parameter will be grouped with an OR operator instead of the default AND.

Current approach

Our SDK methods like Query.startsWith('name', 'jake') return serialized strings ("startsWith(\"name\", \"jake\")"), which the API then deserializes back into Query objects. This has served us well, especially for straightforward queries.

Proposed change

However, with the introduction of nested queries, the complexity escalates. Consider the string output for a nested query using our current method: "or([startsWith(\"name\",\"jake\"),greaterThan(\"followers\",10000)])". Parsing this requires significant rework and state maintenance in our parser.

Alternative - JSON

An alternative is switching to JSON. This way, the SDKs could utilize native JSON tools to directly output from an object. For instance, the same query as above could look like:

{
  "type": "or",
  "attribute": null,
  "values": [
    {
      "type": "startsWith",
      "attribute": "name",
      "values": ["jake"]
    },
    {
      "type": "greaterThan",
      "attribute": "followers",
      "values": [10000]
    }
  ]
}

This would simplify the API’s job to just decoding the JSON, eliminating the need for custom parsing.

Pros and cons

The major upside of JSON is its ease of use and elimination of custom serializers/parsers. However, it's not all roses; the query size inflates significantly (a 2.5x increase in characters for the example given), and the API call complexity increases significantly for non-SDK users.

We need your thoughts!

• How do you view the trade-off between simplicity (JSON) and compactness (current syntax)?
• Can we improve the current approach to handle nested queries more efficiently?
• What potential challenges or benefits do you foresee with JSON?

Every bit of feedback is a step toward making Appwrite even better!

[obiwanzenobi]

I could imagine that JSON format would also benefit to relationship queries (and made them possible!). This includes relationship projection and filtering relationship documents on database/appwrite side.

[aabmets]

Use a custom data structure instead of JSON, where known keywords are represented as single characters or short codes.
You can also remove double-quotes from known keywords, but not user-provided strings.

Your 164-char JSON object:

{
  "type": "or",
  "attribute": null,
  "values": [
    {
      "type": "startsWith",
      "attribute": "name",
      "values": ["jake"]
    },
    {
      "type": "greaterThan",
      "attribute": "followers",
      "values": [10000]
    }
  ]
}

Becomes a 75-char custom-structure object:
(a hybrid between JSON and YAML)

{
  t: or,
  a: null,
  v: [
    {
      t: sw,
      a: "name",
      v: ["jake"]
    },
    {
      t: gt,
      a: "followers",
      v: [10000]
    }
  ]
}

The reduction in non-whitespace characters from 164 to 75 corresponds to a percentage reduction of approximately 54.27%.

On server-side, you would have to convert this structure back into a parseable json.
Essentially it's a tradeoff between network transfer latency and computational complexity.
Since Appwrite performance is heavily dependent on network latency, the tradeoff can be considered a reasonable one.

For further reduction in size, you could consider running the serialized custom object through a compression algorithm like LZMA or some other to reduce the size of user-provided values.

[joern-h]

I like the idea of minimizing the known keywords.
What are the pros and cons of a custom serialization method compared to an existing method such as CBOR?

If network latency is important to us, I think we should focus on implementing select queries for related collections first, as shown in #7033.

[DH-555]

It's less standard, but more efficient. A con and a pro.
I see this a very good approach since most people is not using the API rest but SDK. It's not as efficient as current approach, but it's easier to read, so basically it's an intermediate point between current solution and JSON.

[abnegate]

Interesting proposition, I agree this has pros and cons, but it keeps us in the same position while requiring engineering effort to make the change: syntax is compact, but we need a custom conversion layer on both ends

[gepd]

I was very happy the day I saw the new query syntax, it's simple and easy to understand. I feel like this aproach will make lost that good things about the current state. I mean, it is not hard to understand, but is like we lost part of the main philosophy of appwrite, simplicity.

It would be better if the SDK use the same syntax but under the hood, translate to JSON, we also could have an specific method to pass a raw JSON for cases where the SDK can't cover. Something like Query.raw() or similar

[abnegate]

Just to clarify - SDK method signatures will not change. You can still use Query.equal('name', 'jake') etc. as before, but the output of these methods would be different. Does that fit your idea of translating under the hood?

Curious about what kind of things you find the SDK doesn't cover? raw is certainly an option - but first-class support for commonly needed queries will be even better

[Nishantjain10]

I think it's a good idea to use JSON for nested queries in Appwrite. It makes things clearer, easy to work with, and it's better for handling lots of information. This way, it's easier to keep everything in check.

• instead of saying which type of operator in the JSON structure, like "type": "or," you can make it simpler by using operator overloading. Also, devs can use the order of attributes in the query to make the syntax shorter and easier to read, something like this:

  "or": [
    {
      "type": "startsWith",
      "attribute": 0,
      "values": ["jake"]
    },
    {
      "type": "greaterThan",
      "attribute": 1,
      "values": [10000]
    }
  ]
}

[abnegate]

This style is definitely something we are considering as a step towards making the syntax more compact if using JSON. The downside there is we would be handling nested/grouped queries differently from other types. Again, it's about trade-offs, and this might be a good balance.

[DH-555]

Sincerely I think it will be better a hybrid approach:

• The format @aabmets proposed for API users
• The proposed change for SDK users

The reason is simple: in SDK it will be possible to achieve the best performance and efficiency without compromising DX, and in the case of API rest, the @aabmets approach to have a good DX without having a huge impact in bandwidth.

[abnegate]

This is an interesting thought. Since the SDK output is not really exposed to developers - there would be no issue with inconsistency if a developer swapped from SDKs to raw API calls. We would also only need a conversion to go from current query syntax to JSON instead of conversion all the way to a Query object. Will keep this in mind

[moshOntong-IT]

I hope we also have Query for array attribute

[vini-brito]

I prefer to work with JSON instead of other custom formats, less to learn.

[fliitor]

A structure like mongo selector should works

{
  "$or": [
    {
      "$and": [
        {
          "my_attribute": {
            "$gt": 1000
          }
        },
        {
          "my_attribute": {
            "$lte": 2000
          }
        }
      ]
    },
    {
      "my_second_attr": {
        "$sw": "jake"
      }
    }
  ]
}

With $ define reserved internal instructor

On parser, first need to validate input (and perhaps create an endpoint to validate query for rest users ?)