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

The Database API now supports JSON columns on MySQL, MariaDB, PostgreSQL and SQLite. You can use the query builders to filter and sort by values inside a JSON document, select those values, or copy them into other columns.

Defining a JSON column

To define a JSON column, use Column::json() in a schema definition or 'type' => 'json' in a hook_schema() array. This creates a JSON column on MySQL, MariaDB and SQLite, and a jsonb column on PostgreSQL.

$table = new Table(
  name: 'my_table',
  columns: [
    Column::serial(name: 'id'),
    Column::json(name: 'data'),
  ],
  primaryKey: new PrimaryKey(['id']),
);

Paths

Use a JSONPath string to identify a value inside a document. Paths start with $, followed by object keys or array indexes. For example, $.status reads a top-level key, $.address.city reads a nested key, and $.tags[0] reads the first item in an array. For keys with spaces or special characters, use brackets and double quotes: $["first name"].

Conditions

Use jsonCondition() to filter by a value inside a JSON column. It works on select, update, delete and merge queries, as well as condition groups. Comparisons use the PHP type of the value you pass: 10 matches a JSON number, while '10' matches a JSON string.

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

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

$group = $connection->condition('OR')
  ->jsonCondition('data', '$.status', 'published')
  ->condition('id', 1);
$query->condition($group);

The supported operators are =, <>, !=, <, <=, >, >=, IN, NOT IN, BETWEEN, NOT BETWEEN, CONTAINS, STARTS_WITH, ENDS_WITH, JSON_CONTAINS, IS NULL, IS NOT NULL and HAS KEY. With an empty array, IN matches no rows and NOT IN matches all rows.

A path can include one wildcard: .* for object members or [*] for array elements. For example, $.tags[*] checks each tag and matches the row if any tag satisfies the condition. Wildcards work with all the operators above except JSON_CONTAINS, IS NULL, IS NOT NULL and HAS KEY.

For inserts that copy rows from a select query, call from() first. Insert::jsonCondition() then filters that source query.

Selecting, sorting and filtering groups

Use addJsonExpression() to include a JSON value in the result and jsonOrderBy() to sort by one:

$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 you can use to read the value from a result row. jsonOrderBy() puts NULL values last for both ascending and descending sorts.

Use havingJsonCondition() to filter grouped results. Include the JSON column in GROUP BY so the query also works on MariaDB. SelectExtender supports jsonCondition(), addJsonExpression(), jsonOrderBy() and havingJsonCondition().

Use jsonExpression() on update and merge queries to assign an extracted JSON value to a column. For merge queries, this applies only to the update. On insert queries, it adds the value to the source select query passed to from().

Choosing a cast

Values selected, sorted or assigned through these methods are always cast to give them a consistent type across database backends. Choose a cast with JsonCastType::Int, Float, Bool or Text. The default is Text, which removes the surrounding double quotes from JSON strings. You can also use JsonCastType::fromPhpValue() to choose a cast based on a PHP value.

The value is cast in every row being updated. When using a numeric cast on a column with mixed JSON types, add a jsonCondition() to exclude values of the wrong type. Otherwise, an invalid cast can cause MySQL or PostgreSQL to abort the query.

Contrib database drivers

To add JSON support to a database driver, provide a JsonExpression subclass in the driver's namespace. The base class handles path parsing and validation; the subclass builds the SQL through the abstract doTo*() methods. The MySQL, PostgreSQL and SQLite drivers each provide an implementation, and MySQLi extends the MySQL implementation. Drivers can also provide a JsonCondition subclass.

If a driver has no JsonExpression subclass, calling Connection::jsonExpression() triggers a deprecation notice and throws \LogicException. All drivers must provide this class from Drupal 13.0.0.

Related behaviour changes

• Connection::query() casts boolean arguments to integers on all drivers. Only the PostgreSQL driver did this 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, so ARRAY['a','b'] stays intact.
• The MySQLi placeholder converter handles backslash escapes in string literals and no longer treats 1--2 as a comment.