[5.x]: Invalid PostgreSQL query generated

[borispran]

What happened?

Description

Invalid SQL query is generated for PostgresSQL.

Works on MySQL!

Steps to reproduce

1. Create field of checkboxes type (I named it purpose). Add two options (first, second).
2. Create entry type and add previously created field to it.
3. Create section and one entry.
4. Go to GraphQL console

{ entries { id } }

returns one entry, but when filter by field value

{ entries(purpose: "first") { id } }

an exception is thrown:

ERROR:  operator does not exist: text @> jsonb
LINE 17: ...nt"#>>'{"3dbe2731-4531-4906-8345-9e2b629a4251"}') @> '"first...
                                                              ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Here is the generated SQL:

SELECT
    "elements"."id", "elements"."canonicalId", "elements"."fieldLayoutId", "elements"."uid",
    "elements"."enabled", "elements"."archived", "elements"."dateLastMerged", "elements"."dateCreated",
    "elements"."dateUpdated", "elements_sites"."id" AS "siteSettingsId", "elements_sites"."siteId",
    "elements_sites"."title", "elements_sites"."slug", "elements_sites"."uri", "elements_sites"."content",
    "elements_sites"."enabled" AS "enabledForSite", "entries"."sectionId", "entries"."fieldId",
    "entries"."primaryOwnerId", "entries"."typeId", "entries"."postDate", "entries"."expiryDate",
    "structureelements"."root", "structureelements"."lft", "structureelements"."rgt", "structureelements"."level",
    "structureelements"."structureId"
FROM (
    SELECT "elements"."id" AS "elementsId", "elements_sites"."id" AS "siteSettingsId", "structureelements"."structureId" FROM "elements" "elements"
        INNER JOIN "entries" "entries" ON "entries"."id" = "elements"."id"
        INNER JOIN "elements_sites" "elements_sites" ON "elements_sites"."elementId" = "elements"."id"
        LEFT JOIN "structureelements" "structureelements" ON ("structureelements"."elementId" = "elements"."id") AND (
            EXISTS (SELECT * FROM "structures" WHERE ("id" = "structureelements"."structureId") AND ("dateDeleted" IS NULL)))
    WHERE ("entries"."sectionId"=1) AND 
        ((("elements_sites"."content"#>>'{"3dbe2731-4531-4906-8345-9e2b629a4251"}') @> '"first"'::jsonb)) AND 
        ((("elements"."enabled"=TRUE) AND ("elements_sites"."enabled"=TRUE)) AND 
        ("entries"."postDate" <= '2024-04-25 13:04:59') AND 
        (("entries"."expiryDate" IS NULL) OR ("entries"."expiryDate" > '2024-04-25 13:04:59'))) AND 
        ("elements"."archived"=FALSE) AND 
        ("elements"."dateDeleted" IS NULL) AND 
        ("elements"."draftId" IS NULL) AND 
        ("elements"."revisionId" IS NULL) 
    ORDER BY "structureelements"."lft", "entries"."postDate" DESC, "elements"."id" DESC
) "subquery"
    INNER JOIN "elements" "elements" ON "elements"."id" = "subquery"."elementsId"
    INNER JOIN "elements_sites" "elements_sites" ON "elements_sites"."id" = "subquery"."siteSettingsId"
    INNER JOIN "entries" "entries" ON "entries"."id" = "subquery"."elementsId"
    LEFT JOIN "structureelements" "structureelements" ON ("structureelements"."elementId" = "subquery"."elementsId") AND ("structureelements"."structureId" = "subquery"."structureId")
ORDER BY "structureelements"."lft", "entries"."postDate" DESC, "elements"."id" DESC

Problem is here:

((("elements_sites"."content"#>>'{"3dbe2731-4531-4906-8345-9e2b629a4251"}') @> '"first"'::jsonb))

this line is built in

cms/src/db/pgsql/QueryBuilder.php

Lines 135 to 143 in 11a31e8

	public function jsonExtract(string $column, array $path): string
	{
	$column = $this->db->quoteColumnName($column);
	$path = $this->db->quoteValue(
	sprintf('{%s}', implode(',', array_map(fn(string $seg) => sprintf('"%s"', $seg), $path)))
	);
	return "($column#>>$path)";
	}

Changing operator from #>> (extract as text) to #> (extract as jsonb) or adding explicit cast to jsonb fixes the problem.

So line 142 could look like this return "($column#>$path)"; or this return "($column#>>$path)::jsonb"; whatever fits better to the rest of the query building code.

Craft CMS version

5.0.5

PHP version

8.2

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

[i-just]

Hi, thanks for reporting! I raised a PR for this.

[brandonkelly]

Craft 5.0.6 is out with that fix. Thanks again!

[richardcalahan]

I'm getting a similar error in 5.2.5

operator does not exist: jsonb ~~

[brandonkelly]

@richardcalahan Can you post a new issue with a full stack trace + version info?