Querying JSON in Field::queryCondition

[rynpsc]

Problem

I've have a field type that stores a value as JSON with the format:

{
  "region": "GB",
  "number": "01234567890"
}

With Craft 5 now using JSON and providing some useful methods for dealing with JSON I'd like to allow users to query these fields on a granular level like so:

craft.entries.phone(
  [
    'and',
    { region: ['FR', 'GB'] },
    { number: '01234567890' },
  ]
)

The standard Field::queryCondition doesn't support this but support could be added in on a field by field basis.

This is somewhat doable but I find myself having to go outside of Craft's built in methods like jsonExtract as Field::valueSql and Field::getValueSql make some assumptions about the JSON path that make this harder to implement.

Solution

In my testing this could be solved by allowing a path to be passed to valueSql and then down to getValueSql, falling back to current behaviour of using the first type in Field::dbType().

I've quickly put this together to illustrate where I pass the path into valueSql and then on to getValueSql, falling back to the current behaviour.

protected static function valueSql(array $instances, array $jsonPath = null): ?string
{
    $valuesSql = array_filter(
        array_map(fn(self $field) => $field->getValueSql($jsonPath), $instances),
        fn(?string $valueSql) => $valueSql !== null,
    );

    if (empty($valuesSql)) {
        return null;
    }

    if (count($valuesSql) === 1) {
        return reset($valuesSql);
    }

    return sprintf('COALESCE(%s)', implode(',', $valuesSql));
}

public function getValueSql(array $path = null): ?string
{
    if (!isset($this->layoutElement)) {
        return null;
    }

    $dbType = static::dbType();

    if ($dbType === null) {
        return null;
    }

    $jsonPath = [$this->layoutElement->uid];

    if (isset($path)) {
        // If we have a path use it
        $jsonPath = array_merge($jsonPath, $path);
        // Maybe type should be passed down via a param also?
        $dbType = ArrayHelper::getValue($dbType, end($path));
    } elseif (is_array($dbType)) {
        // Focus on the primary value by default
        $jsonPath[] = array_key_first($dbType);
        $dbType = reset($dbType);
    }

    $db = Craft::$app->getDb();
    $qb = $db->getQueryBuilder();
    $sql = $qb->jsonExtract('elements_sites.content', $jsonPath);

    if ($db->getIsMysql()) {
        // If the field uses an optimized DB type, cast it so its values can be indexed
        // (see "Functional Key Parts" on https://dev.mysql.com/doc/refman/8.0/en/create-index.html)
        $castType = match (Db::parseColumnType($dbType)) {
            Schema::TYPE_CHAR,
            Schema::TYPE_STRING,
            'varchar' => 'CHAR(255)',
            // only reliable way to compare booleans is as 'true'/'false' strings :(
            Schema::TYPE_BOOLEAN => 'CHAR(5)',
            Schema::TYPE_DATE => 'DATE',
            Schema::TYPE_DATETIME => 'DATETIME',
            Schema::TYPE_DECIMAL => 'DECIMAL',
            Schema::TYPE_DOUBLE => 'DOUBLE',
            Schema::TYPE_FLOAT => 'FLOAT',
            Schema::TYPE_TINYINT,
            Schema::TYPE_SMALLINT,
            Schema::TYPE_INTEGER,
            Schema::TYPE_BIGINT => 'SIGNED',
            SCHEMA::TYPE_TIME => 'TIME',
            default => null,
        };
        if ($castType !== null) {
            // if a length was specified, replace the default with that
            if ($length = Db::parseColumnLength($dbType)) {
                $castType = preg_replace('/\(\d+\)/', "($length)", $castType);
            }
            $sql = "CAST($sql AS $castType)";
        }
    }

    return $sql;
}

This then allows using the jsonExtract method which I can then use in queryCondition to achieve the query.

public static function queryCondition(array $instances, mixed $value, array &$params): array|string
{
    $param = QueryParam::parse($value);
    $condition = [$param->operator];

    foreach ($param->values as $value) {
        $k = key($value); // This isn't quite right as I get Expected parameter of type 'array|object', 'string' provided in the IDE but it works
        $v = reset($value); // ditto

        $valueSql = static::valueSql($instances, [$k]);
        $condition[] = Db::parseParam($valueSql, $v, columnType: Schema::TYPE_JSON);
    }

    return $condition;
}

[brandonkelly]

Just added a $key argument to Field::valueSql() and getValueSql() for the next release (3552d89).

Not quite as flexible as a $path array, but it’s important that getValueSql() knows what the corresponding value type is via dbType(), so it can typecast the values appropriately.

[rynpsc]

Excellent! Just what I needed. Thanks 😍

[brandonkelly]

Craft 5.0.0-alpha.2 is out with that change 🎉