Add JsonPathExpression to support json path clauses by othercorey · Pull Request #19463 · cakephp/cakephp

othercorey · 2026-05-24T12:12:07Z

Add support for the full json path expression clauses for JSON_VALUE() and JSON_EXISTS() functions. These can be extended to support JSON_QUERY and possibly JSON_TABLE in the future.

https://www.postgresql.org/docs/current/functions-json.html#SQLJSON-QUERY-FUNCTIONS

ADmad · 2026-05-24T12:58:38Z

+     * @param array $types Associative array of types for the expressions.
+     * @return $this
+     */
+    public function passing(array $passing, array $types = []): static


We should silence this rule for 5.4 so that we can at least add static for new methods.

ADmad · 2026-05-24T13:09:09Z

+    /**
+     * Sets the ON EMPTY clause.
+     *
+     * @param string $behavior The behavior on empty (NULL, ERROR, or DEFAULT).


Suggested change

* @param string $behavior The behavior on empty (NULL, ERROR, or DEFAULT).

* @param self::BEHAVIOR_* $behavior The behavior on empty (NULL, ERROR, or DEFAULT).

phpstan supports this, most likely other tools too

https://phpstan.org/writing-php-code/phpdoc-types#literals-and-constants

othercorey · 2026-05-24T14:40:26Z

@ADmad I should extend the query tests with the full format. The jsonValue() test is in UpateQueryTest, but it's not specific to update. Should I move it to QueryTest or somewhere else?

ADmad · 2026-05-24T15:48:58Z

I should extend the query tests with the full format. The jsonValue() test is in UpateQueryTest, but it's not specific to update. Should I move it to QueryTest or somewhere else?

QueryTest would work I guess.

markstory

Looking good to me.

markstory · 2026-05-26T03:38:28Z

+    }
+
+    /**
+     * Returns a FunctionExpression representing the Json Exists


Should these methods include any hints about the limitations in database support?

Sure. Most clauses are supported by engines that support the sql standard function.

I'm not sure if we should translate this to non-standard similar functions in other db engines -- maybe yes, but we'd have to deal with ignoring clauses that have a value set.

Most clauses are supported by engines that support the sql standard function.

Ok, I took a quick look at MySQL and Postgres docs, and I was surprised to see so much alignment in the implementations.

LordSimal · 2026-05-29T07:22:36Z

There are a few CS & Stan issues, other than that this looks good to merge.

othercorey · 2026-05-29T07:29:52Z

There are a few CS & Stan issues, other than that this looks good to merge.

I have an update that cleans up some types, but waiting until I have time to add a couple tests to ensure the engines parse ok.

LordSimal · 2026-05-30T10:24:44Z

-      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


Why specifically postgres 12 here? According to https://endoflife.date/postgresql 12 is EOL'd since over a year.

12.0 adds the JSONB_PATH_EXISTS() function. Since we were already transforming JSON_VALUE to JSONB_PATH_QUERY, thought it would be expected.

17.0 adds the sql standard functions we should be using.

othercorey · 2026-05-30T10:27:45Z

Updated JsonPathExpression::passing() to auto-set bind types
Added expression transforms for JSON_EXISTS
Updated ci to run PostgreSQL 12 since it adds driver-specific features
Fixed DEFAULT x behavior of ON EMPTY and ON ERROR clauses requiring literal values
Added FunctionsQueryTest to test driver-specific function expressions similar to AggregatesQueryTest

othercorey · 2026-05-30T10:30:48Z

+                                [null, 'literal'],
+                                '',
+                                parentheses: false,
+                            );


cake makes modifying function expressions into something else a nightmare. I'm not sure if this is worth it or not, but it's one way to support it.

I think it is worth the effort. SQLite is a fantastic database when it is a suitable choice.

othercorey · 2026-05-31T10:07:06Z

I don't know what to do about the rector failure. It wants to change the code layout while there's only one switch case.

LordSimal · 2026-05-31T10:30:07Z

I don't know what to do about the rector failure. It wants to change the code layout while there's only one switch case.

Well because its the SingularSwitchToIfRector rule :D

A switch-case with only 1 case is basically just a if.

othercorey · 2026-05-31T10:35:01Z

A switch-case with only 1 case is basically just a if.

I understand - it just sets a lousy code example that's different from the other drivers.

LordSimal · 2026-05-31T10:49:21Z

I appreciate trying to keep it as consistent as possible. Alternatively we can also ignore that rector rule in our config, but I don't think that this is a big difference.

ADmad · 2026-05-31T11:58:36Z

+     * @param \Cake\Database\Expression\FunctionExpression $expression The function expression to transform.
+     * @return void
+     */
+    protected function _transformFunctionExpression(FunctionExpression $expression): void


Unless this is an overridden function we don't need the underscore prefix.

othercorey · 2026-05-31T21:06:17Z

@ADmad How much do you dislike the parentheses parameter in QueryExpression? I don't know how else to concat to add another parameter or build a different sql expression.

markstory · 2026-06-03T02:46:37Z

    protected function _expressionTranslators(): array
    {
        return [
+            FunctionExpression::class => '_transformFunctionExpression',


Suggested change

FunctionExpression::class => '_transformFunctionExpression',

FunctionExpression::class => 'transformFunctionExpression',

markstory · 2026-06-03T02:46:43Z

+     * @param \Cake\Database\Expression\FunctionExpression $expression The function expression to transform.
+     * @return void
+     */
+    protected function _transformFunctionExpression(FunctionExpression $expression): void


Suggested change

protected function _transformFunctionExpression(FunctionExpression $expression): void

protected function transformFunctionExpression(FunctionExpression $expression): void

markstory · 2026-06-03T02:49:41Z

+                    ->setName('JSON_CONTAINS_PATH')
+                    ->iterateParts(function ($p, $key) {
+                        if ($key === 1) {
+                            return new QueryExpression(["'all'", $p], ['literal'], ',', parentheses: false);


Should we have a separate class for a ExpressionList style container that doesn't emit parentheses? I could see this being useful in other parts of the database drivers as well.

It would be helpful. Hoping one of you makes the call.

@LordSimal @ADmad Do you have any opinions on this so I can work on final update?

I have no strong opinion about this.

@othercorey Adding a new class as markstory suggested would be cleaner instead of adding more switches to QueryExpression.

markstory · 2026-06-03T02:50:44Z

+                                [null, 'literal'],
+                                '',
+                                parentheses: false,
+                            );


I think it is worth the effort. SQLite is a fantastic database when it is a suitable choice.

dereuromark added this to the 5.4.0 milestone May 24, 2026

dereuromark reviewed May 24, 2026

View reviewed changes

Comment thread src/Database/Expression/JsonPathExpression.php Outdated

ADmad reviewed May 24, 2026

View reviewed changes

dereuromark added the enhancement label May 24, 2026

markstory reviewed May 26, 2026

View reviewed changes

othercorey force-pushed the json-query branch 2 times, most recently from 699618e to e155348 Compare May 30, 2026 10:19

LordSimal reviewed May 30, 2026

View reviewed changes

othercorey force-pushed the json-query branch from e155348 to 954c04f Compare May 30, 2026 10:26

othercorey commented May 30, 2026

View reviewed changes

othercorey force-pushed the json-query branch 5 times, most recently from 5f464fd to 71d2bbc Compare May 31, 2026 10:06

Add JsonPathExpression to support json path clauses

4df3c26

othercorey force-pushed the json-query branch from 71d2bbc to 4df3c26 Compare May 31, 2026 10:33

ADmad reviewed May 31, 2026

View reviewed changes

markstory reviewed Jun 4, 2026

View reviewed changes

	* @param string $behavior The behavior on empty (NULL, ERROR, or DEFAULT).
	* @param self::BEHAVIOR_* $behavior The behavior on empty (NULL, ERROR, or DEFAULT).

	FunctionExpression::class => '_transformFunctionExpression',
	FunctionExpression::class => 'transformFunctionExpression',

	protected function _transformFunctionExpression(FunctionExpression $expression): void
	protected function transformFunctionExpression(FunctionExpression $expression): void

Conversation

othercorey commented May 24, 2026

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

ADmad May 24, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

othercorey commented May 24, 2026

Uh oh!

ADmad commented May 24, 2026

Uh oh!

markstory left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

LordSimal commented May 29, 2026

Uh oh!

othercorey commented May 29, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

othercorey commented May 30, 2026

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

othercorey commented May 31, 2026

Uh oh!

LordSimal commented May 31, 2026

Uh oh!

othercorey commented May 31, 2026

Uh oh!

LordSimal commented May 31, 2026

Uh oh!

Choose a reason for hiding this comment

Uh oh!

othercorey commented May 31, 2026

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

ADmad May 24, 2026 •

edited

Loading

othercorey commented May 29, 2026 •

edited

Loading