Add support for fetching from JSON columns

[msmakouz]

Q	A
Bugfix?	❌
Breaks BC?	❌
New feature?	✔️
Issues	#129

This PR introduces a set of new query methods. These methods are designed to simplify working with JSON columns in the database, making it easier to filter and manipulate data stored in JSON format.

1) WhereJson

• whereJson(string $path, mixed $value): static - allow to select records where the JSON value at the specified path is equal to the passed value.
• orWhereJson(string $path, mixed $value): static - similar to the whereJson method, but uses the OR operator to combine with other conditions.

$users = $database
    ->select()
    ->from('users')
    ->whereJson('options->notifications->type', 'sms')
    ->fetchAll();

$users = $database
    ->select()
    ->from('users')
    ->whereJson('options->notifications->type', 'email')
    ->orWhereJson('options->notifications->type', 'sms')
    ->fetchAll();

2) WhereJsonContains

• whereJsonContains(string $path, mixed $value): static - allow to select records where the JSON array contains the passed value. The passed value can be an array, which will be encoded into JSON.
• orWhereJsonContains(string $path, mixed $value): static - similar to the whereJsonContains method, but uses the OR operator to combine with other conditions.

$users = $database
    ->select()
    ->from('users')
    ->whereJsonContains('options->languages', 'en')
    ->fetchAll();

$users = $database
    ->select()
    ->from('users')
    ->where('status', 'active')
    ->orWhereJsonContains('options->languages', 'fr')
    ->fetchAll();

3) WhereJsonDoesntContain

• whereJsonDoesntContain(string $path, mixed $value): static - allow to select records where the JSON array does NOT contain the passed value. The passed value can be an array, which will be encoded into JSON.
• orWhereJsonDoesntContain(string $path, mixed $value): static - similar to the whereJsonDoesntContain method, but uses the OR operator to combine with other conditions.

$users = $database
    ->select()
    ->from('users')
    ->whereJsonDoesntContain('options->languages', 'de')
    ->fetchAll();

$users = $database
    ->select()
    ->from('users')
    ->whereJsonDoesntContain('options->languages', 'fr')
    ->orWhereJsonDoesntContain('options->languages', 'de')
    ->fetchAll();

4) WhereJsonContainsKey

• whereJsonContainsKey(string $path): static - allows to select records where the JSON contains the desired path.
• orWhereJsonContainsKey(string $path): static - method is used to combine with other conditions using the OR operator.

$users = $database
    ->select()
    ->from('users')
    ->whereJsonContainsKey('options->languages->de')
    ->fetchAll();

$users = $database
    ->select()
    ->from('users')
    ->orWhereJsonContainsKey('options->languages->fr')
    ->orWhereJsonContainsKey('options->languages->de')
    ->fetchAll();

5) WhereJsonDoesntContainKey

• whereJsonDoesntContainKey(string $path): static - allows to select records where the JSON does NOT contain the desired path.
• orWhereJsonDoesntContainKey(string $path): static - method is used to combine with other conditions using the OR operator.

$users = $database
    ->select()
    ->from('users')
    ->whereJsonDoesntContainKey('options->languages->de')
    ->fetchAll();

$users = $database
    ->select()
    ->from('users')
    ->orWhereJsonDoesntContainKey('options->languages->fr')
    ->orWhereJsonDoesntContainKey('options->languages->de')
    ->fetchAll();

6) WhereJsonLength

• whereJsonLength(string $path, int $length, string $operator = '='): static - allows to select JSON arrays by their length.
• orWhereJsonLength(string $path, int $length, string $operator = '='): static - method is used to combine with other conditions using the OR operator.

$users = $database
    ->select()
    ->from('users')
    ->whereJsonLength('journal->errors', 0)
    ->fetchAll();

$users = $database
    ->select()
    ->from('users')
    ->whereJsonLength('journal->errors', 1, '>=')
    ->fetchAll();

All of these conditions can also be used in delete and update queries.

[codecov[bot]]

Codecov Report

Merging #135 (44b2bba) into 2.x (6c47eef) will increase coverage by 0.27%. The diff coverage is 98.27%.

@@             Coverage Diff              @@
##                2.x     #135      +/-   ##
============================================
+ Coverage     94.72%   95.00%   +0.27%     
- Complexity     1689     1788      +99     
============================================
  Files            96      128      +32     
  Lines          4535     4928     +393     
============================================
+ Hits           4296     4682     +386     
- Misses          239      246       +7     

Files	Coverage Δ
src/Driver/Compiler.php	96.17% <100.00%> (+0.06%)	:arrow_up:
src/Driver/CompilerCache.php	89.11% <100.00%> (+0.15%)	:arrow_up:
src/Driver/Jsoner.php	100.00% <100.00%> (ø)
src/Driver/MySQL/Injection/CompileJson.php	100.00% <100.00%> (ø)
src/Driver/MySQL/Injection/CompileJsonContains.php	100.00% <100.00%> (ø)
.../Driver/MySQL/Injection/CompileJsonContainsKey.php	100.00% <100.00%> (ø)
...river/MySQL/Injection/CompileJsonDoesntContain.php	100.00% <100.00%> (ø)
...er/MySQL/Injection/CompileJsonDoesntContainKey.php	100.00% <100.00%> (ø)
src/Driver/MySQL/Injection/CompileJsonLength.php	100.00% <100.00%> (ø)
src/Driver/MySQL/Injection/MySQLJsonExpression.php	100.00% <100.00%> (ø)
... and 35 more