cakephp · othercorey · May 24, 2026 · LordSimal · May 30, 2026 · othercorey
diff --git a/.github/workflows/ci.yml b/.github/workflows/ci.yml
@@ -83,8 +83,12 @@ jobs:
       if: matrix.db-type == 'mysql' && matrix.dependencies == 'lowest'
       run: docker run --rm --name=mysqld -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=cakephp -p 3306:3306 -d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
 
-    - name: Setup PostgreSQL with PostGIS
-      if: matrix.db-type == 'pgsql'
+    - name: Setup PostgreSQL 12 with PostGIS
+      if: matrix.db-type == 'pgsql' && matrix.php-version == '8.2'
+      run: docker run --rm --name=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=cakephp -p 5432:5432 -d postgis/postgis:12-3.5
+
+    - name: Setup PostgreSQL 18 with PostGIS
+      if: matrix.db-type == 'pgsql' && matrix.php-version != '8.2'
       run: docker run --rm --name=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=cakephp -p 5432:5432 -d postgis/postgis:18-3.6
 
     - name: Setup MariaDB 11.8

diff --git a/src/Database/Driver/Mysql.php b/src/Database/Driver/Mysql.php
@@ -19,6 +19,8 @@
 use Cake\Database\Driver;
 use Cake\Database\DriverFeatureEnum;
 use Cake\Database\Expression\DistinctComparisonExpression;
+use Cake\Database\Expression\FunctionExpression;
+use Cake\Database\Expression\QueryExpression;
 use Cake\Database\Expression\StringAggExpression;
 use Cake\Database\Query;
 use Cake\Database\Query\SelectQuery;
@@ -39,6 +41,7 @@ class Mysql extends Driver
     protected function _expressionTranslators(): array
     {
         return [
+            FunctionExpression::class => '_transformFunctionExpression',
-            FunctionExpression::class => '_transformFunctionExpression',
+            FunctionExpression::class => 'transformFunctionExpression',
-            FunctionExpression::class => '_transformFunctionExpression',
+            FunctionExpression::class => 'transformFunctionExpression',
             StringAggExpression::class => 'transformStringAggExpression',
             DistinctComparisonExpression::class => 'transformDistinctComparisonExpression',
         ];
@@ -355,6 +358,33 @@ public function version(): string
         return $this->_version;
     }
 
+    /**
+     * Receives a FunctionExpression and changes it so that it conforms to this SQL dialect.
+     *
+     * @param \Cake\Database\Expression\FunctionExpression $expression The function expression to transform.
+     * @return void
+     */
+    protected function _transformFunctionExpression(FunctionExpression $expression): void
-    protected function _transformFunctionExpression(FunctionExpression $expression): void
+    protected function transformFunctionExpression(FunctionExpression $expression): void
-    protected function _transformFunctionExpression(FunctionExpression $expression): void
+    protected function transformFunctionExpression(FunctionExpression $expression): void
+    {
+        if ($this->isMariadb()) {
+            return;
+        }
+
+        switch ($expression->getName()) {
+            case 'JSON_EXISTS':
+                $expression
+                    ->setName('JSON_CONTAINS_PATH')
+                    ->iterateParts(function ($p, $key) {
+                        if ($key === 1) {
+                            return new QueryExpression(["'all'", $p], ['literal'], ',', parentheses: false);
+                        }
+
+                        return $p;
+                    });
+                break;
+        }
+    }
+
     /**
      * Get PDO ATTR_SSL_KEY id.
      *

diff --git a/src/Database/Driver/Postgres.php b/src/Database/Driver/Postgres.php
@@ -345,16 +345,28 @@ protected function _transformFunctionExpression(FunctionExpression $expression):
                     ->add([') + (1' => 'literal']); // Postgres starts on index 0 but Sunday should be 1
                 break;
             case 'JSON_VALUE':
-                $expression->setName('JSONB_PATH_QUERY')
-                    ->iterateParts(function ($p, $key) {
-                        if ($key === 0) {
-                            $p = sprintf('%s::jsonb', $p);
-                        } elseif ($key === 1) {
-                            $p = sprintf("'%s'::jsonpath", $this->quoteIdentifier($p['value']));
-                        }
-
-                        return $p;
-                    });
+                if (version_compare($this->version(), '17.0', '<')) {
+                    $expression->setName('JSONB_PATH_QUERY')
+                        ->iterateParts(function ($p, $key) {
+                            if ($key === 0) {
+                                return $p = sprintf('%s::jsonb', $p);
+                            }
+
+                            return $p;
+                        });
+                }
+                break;
+            case 'JSON_EXISTS':
+                if (version_compare($this->version(), '17.0', '<')) {
+                    $expression->setName('JSONB_PATH_EXISTS')
+                        ->iterateParts(function ($p, $key) {
+                            if ($key === 0) {
+                                return sprintf('%s::jsonb', $p);
+                            }
+
+                            return $p;
+                        });
+                }
                 break;
         }
     }

diff --git a/src/Database/Driver/Sqlite.php b/src/Database/Driver/Sqlite.php
@@ -19,6 +19,7 @@
 use Cake\Database\Driver;
 use Cake\Database\DriverFeatureEnum;
 use Cake\Database\Expression\FunctionExpression;
+use Cake\Database\Expression\QueryExpression;
 use Cake\Database\Expression\StringAggExpression;
 use Cake\Database\Expression\TupleComparison;
 use Cake\Database\Schema\SchemaDialect;
@@ -325,6 +326,22 @@ protected function _transformFunctionExpression(FunctionExpression $expression):
             case 'JSON_VALUE':
                 $expression->setName('JSON_EXTRACT');
                 break;
+            case 'JSON_EXISTS':
+                $expression
+                    ->setName('JSON_TYPE')
+                    ->iterateParts(function ($p, $key) {
+                        if ($key === 1) {
+                            return new QueryExpression(
+                                [$p, ') IS NOT NULL AND (1'],
+                                [null, 'literal'],
+                                '',
+                                parentheses: false,
+                            );
+                        }
+
+                        return $p;
+                    });
+                break;
         }
     }
 }
diff --git a/src/Database/Expression/JsonPathExpression.php b/src/Database/Expression/JsonPathExpression.php
@@ -0,0 +1,242 @@
+<?php
+declare(strict_types=1);
+
+/**
+ * CakePHP(tm) : Rapid Development Framework (https://cakephp.org)
+ * Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
+ *
+ * Licensed under The MIT License
+ * For full copyright and license information, please see the LICENSE.txt
+ * Redistributions of files must retain the above copyright notice.
+ *
+ * @copyright     Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
+ * @link          https://cakephp.org CakePHP(tm) Project
+ * @since         5.4.0
+ * @license       https://opensource.org/licenses/mit-license.php MIT License
+ */
+namespace Cake\Database\Expression;
+
+use Cake\Database\ExpressionInterface;
+use Cake\Database\TypedResultInterface;
+use Cake\Database\TypedResultTrait;
+use Cake\Database\ValueBinder;
+use Closure;
+
+/**
+ * Represents a JSON path expression with optional clauses for each json function.
+ */
+class JsonPathExpression implements ExpressionInterface, TypedResultInterface
+{
+    use TypedResultTrait;
+
+    public const BEHAVIOR_NULL = 'NULL';
+    public const BEHAVIOR_ERROR = 'ERROR';
+    public const BEHAVIOR_DEFAULT = 'DEFAULT';
+
+    /**
+     * @var string
+     */
+    protected string $path;
+
+    /**
+     * @var array<string, array{value: mixed, type: string|null}>
+     */
+    protected array $passing = [];
+
+    /**
+     * @var string|null
+     */
+    protected ?string $returning = null;
+
+    /**
+     * @var array{behavior: string, value: mixed}|null
+     */
+    protected ?array $onEmpty = null;
+
+    /**
+     * @var array{behavior: string, value: mixed}|null
+     */
+    protected ?array $onError = null;
+
+    /**
+     * Constructor.
+     *
+     * @param string $path The json path
+     */
+    public function __construct(string $path)
+    {
+        $this->path = $path;
+    }
+
+    /**
+     * Sets the RETURNING clause.
+     *
+     * Not all database engines support all clauses. Check for support
+     * before using.
+     *
+     * @param string $type The sql data type to return
+     * @return $this
+     */
+    public function returning(string $type)
+    {
+        $this->returning = $type;
+
+        return $this;
+    }
+
+    /**
+     * Sets the PASSING clause.
+     *
+     * Not all database engines support all clauses. Check for support
+     * before using.
+     *
+     * @param array<string, string|int|float|bool> $passing Mapping of variable name to value.
+     * @param array<string, string|int> $types Optional mapping of variable name to binding type.
+     * @return $this
+     */
+    public function passing(array $passing, array $types = [])
+    {
+        foreach ($passing as $name => $value) {
+            $type = $types[$name] ?? null;
+            if ($type === null) {
+                $type = match (true) {
+                    is_string($value) => 'string',
+                    is_int($value) => 'integer',
+                    is_float($value) => 'float',
+                    is_bool($value) => 'boolean',
+                    default => null,
+                };
+            }
+
+            $this->passing[$name] = ['value' => $value, 'type' => $type];
+        }
+
+        return $this;
+    }
+
+    /**
+     * Sets the ON EMPTY clause.
+     *
+     * Not all database engines support all clauses. Check for support
+     * before using.
+     *
+     * @param self::BEHAVIOR_* $behavior The behavior on empty (NULL, ERROR, or DEFAULT).
+     * @param mixed $value The default value if behavior is DEFAULT.
+     * @return $this
+     */
+    public function onEmpty(string $behavior, mixed $value = null)
+    {
+        $this->onEmpty = ['behavior' => $behavior, 'value' => $value];
+
+        return $this;
+    }
+
+    /**
+     * Sets the ON ERROR clause.
+     *
+     * Not all database engines support all clauses. Check for support
+     * before using.
+     *
+     * @param self::BEHAVIOR_* $behavior The behavior on error (NULL, ERROR, or DEFAULT).
+     * @param mixed $value The default value if behavior is DEFAULT.
+     * @return $this
+     */
+    public function onError(string $behavior, mixed $value = null)
+    {
+        $this->onError = ['behavior' => $behavior, 'value' => $value];
+
+        return $this;
+    }
+
+    /**
+     * @inheritDoc
+     */
+    public function sql(ValueBinder $binder): string
+    {
+        $sql = "'{$this->path}'";
+
+        if ($this->passing) {
+            $passing = [];
+            foreach ($this->passing as $name => ['value' => $value, 'type' => $type]) {
+                if ($value instanceof ExpressionInterface) {
+                    $exprSql = $value->sql($binder);
+                } else {
+                    $placeholder = $binder->placeholder('param');
+                    $binder->bind($placeholder, $value, $type);
+                    $exprSql = $placeholder;
+                }
+                $passing[] = sprintf('%s AS %s', $exprSql, $name);
+            }
+            $sql .= ' PASSING ' . implode(', ', $passing);
+        }
+
+        if ($this->returning) {
+            $sql .= ' RETURNING ' . $this->returning;
+        }
+
+        if ($this->onEmpty !== null) {
+            $sql .= ' ' . $this->behaviorSql($this->onEmpty, 'EMPTY', $binder);
+        }
+
+        if ($this->onError !== null) {
+            $sql .= ' ' . $this->behaviorSql($this->onError, 'ERROR', $binder);
+        }
+
+        return $sql;
+    }
+
+    /**
+     * Generates the SQL for ON EMPTY or ON ERROR clauses.
+     *
+     * @param array $clause The clause configuration.
+     * @param string $type The type of clause (EMPTY or ERROR).
+     * @param \Cake\Database\ValueBinder $binder The value binder.
+     * @return string
+     */
+    protected function behaviorSql(array $clause, string $type, ValueBinder $binder): string
+    {
+        $behavior = strtoupper($clause['behavior']);
+        if ($behavior === self::BEHAVIOR_DEFAULT) {
+            $value = $clause['value'];
+            if ($value instanceof ExpressionInterface) {
+                $value = $value->sql($binder);
+            } elseif (is_string($value)) {
+                $value = sprintf("'%s'", str_replace("'", "''", $value));
+            } elseif (is_bool($value)) {
+                $value = $value ? 'TRUE' : 'FALSE';
+            } elseif ($value === null) {
+                $value = 'NULL';
+            }
+
+            // DEFAULT clause require a literal value
+            return sprintf('DEFAULT %s ON %s', $value, $type);
+        }
+
+        return sprintf('%s ON %s', $behavior, $type);
+    }
+
+    /**
+     * @inheritDoc
+     */
+    public function traverse(Closure $callback)
+    {
+        foreach ($this->passing as ['value' => $value]) {
+            if ($value instanceof ExpressionInterface) {
+                $callback($value);
+                $value->traverse($callback);
+            }
+        }
+
+        if ($this->onEmpty !== null && $this->onEmpty['value'] instanceof ExpressionInterface) {
+            $callback($this->onEmpty['value']);
+            $this->onEmpty['value']->traverse($callback);
+        }
+
+        if ($this->onError !== null && $this->onError['value'] instanceof ExpressionInterface) {
+            $callback($this->onError['value']);
+            $this->onError['value']->traverse($callback);
+        }
+
+        return $this;
+    }
+}