JSON column type and JSON query API added to the Database API

Schema and Database API support JSON as a column type. Query builders can filter, sort, select and assign values at a JSON path. Supported backends are MySQL, MariaDB, PostgreSQL and SQLite.

The json column type

Set type to json in hook_schema(). MySQL, MariaDB and SQLite map it to JSON, PostgreSQL maps it to jsonb.

$schema['my_table'] = [
  'fields' => [
    'id' => ['type' => 'serial', 'not null' => TRUE],
    'data' => ['type' => 'json'],
  ],
  'primary key' => ['id'],
];

Conditions

jsonCondition() is part of ConditionInterface. Select, update and delete queries accept it, and so do condition groups.

// Select.
$ids = $connection->select('my_table', 'm')
  ->fields('m', ['id'])
  ->jsonCondition('m.data', '$.status', 'published')
  ->execute()
  ->fetchCol();

// Delete.
$connection->delete('my_table')
  ->jsonCondition('data', '$.score', 10, '<')
  ->execute();

// Mixed with regular conditions in a group.
$group = $connection->condition('OR')
  ->jsonCondition('data', '$.status', 'published')
  ->condition('id', 1);
$query->condition($group);

Insert::jsonCondition() filters the select query passed to from().

Supported operators: =, <>, !=, <, <=, >, >=, IN, NOT IN, BETWEEN, NOT BETWEEN, CONTAINS, STARTS_WITH, ENDS_WITH, JSON_CONTAINS, IS NULL, IS NOT NULL, HAS KEY.

IN with an empty array matches nothing. NOT IN with an empty array matches everything.

A path holds at most one wildcard segment, written $.* or $[*]. The comparison operators (=, <>, !=, <, <=, >, >=, IN, NOT IN) then ask whether any matched value satisfies the condition. They are listed in JsonCondition::WILDCARD_OPERATORS. The other operators reject a wildcard path. A path with more than one wildcard is rejected as well.

Select list, sorting and HAVING

$query = $connection->select('my_table', 'm');
$alias = $query->addJsonExpression('m.data', '$.title', 'title');
$query->jsonOrderBy('m.data', '$.number', 'DESC', JsonCastType::Int);

addJsonExpression() returns the alias of the expression. jsonOrderBy() sorts NULL values last in both directions.

Select::havingJsonCondition() sets a HAVING condition. The JSON column must be listed in the GROUP BY. MariaDB rejects it otherwise.

All four methods are also available on SelectExtender.

Assigning an extracted value to a column

Update::jsonExpression() and Merge::jsonExpression() set a column to a value taken from a JSON column. Insert::jsonExpression() does the same for the rows a select query returns.

$connection->update('my_table')
  ->jsonExpression('title', 'data', '$.title')
  ->jsonCondition('data', '$.title', NULL, 'IS NOT NULL')
  ->execute();

An assignment cannot skip rows holding another type. MySQL and PostgreSQL reject a numeric cast of a JSON string. Restrict the query with a jsonCondition() when the column holds mixed types.

Casts

Extracted values are always cast, so the result type is the same on every backend. The cast types are the cases of the JsonCastType enum: Int, Float, Bool and Text. Text is the default and returns the value unquoted. On MySQL and MariaDB a text cast truncates to 255 characters.

JsonCastType::fromPhpValue() returns the case matching the type of a PHP value.

For contrib database drivers

A driver provides JSON support with a JsonExpression subclass in its own namespace. Connection::jsonExpression() resolves the class through getDriverClass('JsonExpression'). Drivers can also provide a JsonCondition subclass, which Connection::jsonCondition() resolves the same way.

Without a JsonExpression subclass, Connection::jsonExpression() triggers a deprecation and throws \LogicException. The method becomes abstract in Drupal 12.0.0.

The public methods of JsonExpression are @internal: toSql(), toCastSql(), toHasKeySql(), toContainsSql(), toTypeGuardSql(), toComparisonSql() and hasWildcard(). They validate the path, then call an abstract doTo*() counterpart. A driver implements those counterparts. Path parsing, normalization and the wildcard checks stay in the base class.

Core ships a subclass for mysql, pgsql and sqlite. The mysqli one extends the mysql one.

Related behaviour changes

• Connection::query() casts boolean arguments to integers for all drivers, including the values of an expanded array argument. The pgsql driver did this on its own before.
• Connection::prefixTables() no longer replaces {word} inside a single-quoted JSON literal such as '{"key": 1}'. A quoted string that is not valid JSON is still expanded.
• Connection::quoteIdentifiers() no longer replaces [word] inside a single-quoted string, which keeps ARRAY['a','b'] intact.
• The mysqli placeholder converter now handles backslash escapes in string literals, as produced by \mysqli::real_escape_string(). It starts a -- comment only when a whitespace or control character follows, so 1--2 stays a subtraction.
• pgsql: Schema::findPrimaryKeyColumns() binds the table name as a placeholder instead of inlining {table}.