💡 Support for Querying JSON Columns in Databases for better DX

[lotyp]

I have an idea!

Feature Request:

I am requesting the inclusion of enhanced methods to query JSON column types directly via cycle/orm. This feature not only aligns with the capabilities offered by frameworks like Laravel, but also significantly improves the overall Developer Experience (DX).

Current Behavior:

Currently, querying JSON in cycle/orm involves more complex, less intuitive code:

...

use Cycle\Database\Injection\Parameter;

...

$exists = $webhookCallsRepository
    ->select()
    ->where(['name' => 'stripe'])
    ->andWhere("JSON_EXTRACT(payload, '$.id')", '=', new Parameter(['payloadId' => $request->get('id')]))
    ->count();

Full class located here: https://github.com/wayofdev/laravel-stripe-webhooks/blob/master/src/Profile/StripeWebhookProfile.php

Desired Behavior:

$users = $repository->select()
                ->whereJson('preferences->dining->meal', 'salad')
                ->fetch();

$users = $repository->select()
                ->whereJsonContains('options->languages', 'en')
                ->fetch();

$users = $repository->select()
                ->whereJsonLength('options->languages', '>', 1)
                ->fetch();

Proposal:

I've checked internal code of cycle/orm and found that first thing to modify would be WhereTrait.php, where additional methods could be implemented:

<?php

trait WhereTrait
{
    // ... (other existing methods) ...

    public function whereJson(string $column, $value): self
    {
        $this->registerToken(
            'AND',
            [$column, '->', $value],
            $this->whereTokens,
            $this->whereWrapper()
        );

        return $this;
    }

    public function whereJsonContains(string $column, $value): self
    {
        $this->registerToken(
            'AND',
            [$column, '->', $value],
            $this->whereTokens,
            $this->whereWrapper()
        );

        return $this;
    }

    public function whereJsonLength(string $column, $operator, $value = null): self
    {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }

        $this->registerToken(
            'AND',
            [$column, 'json_length', $operator, $value],
            $this->whereTokens,
            $this->whereWrapper()
        );

        return $this;
    }
}

Benefits:

Improved Developer Experience (DX): This change would significantly reduce the complexity and verbosity of JSON-related queries, leading to faster and more intuitive coding.

Reduced Errors: Leveraging built-in methods reduces the likelihood of errors from manually written SQL statements.

Alignment with Popular Frameworks: It brings cycle/orm on par with other frameworks, making it a more attractive option for developers familiar with those patterns. I believe this feature, coupled with other necessary changes, would be a valuable addition to cycle/orm. I look forward to feedback and potential inclusion in a future release.