JSON data type added to database schema API

Drupal's Schema and Database API now support JSON as a native column type, with a driver-neutral query API for filtering by values at a JSON path.

The json field type

Declare a JSON column in hook_schema() by setting its type to json. Each driver maps to its native JSON type: JSON on MySQL/MariaDB and SQLite, jsonb on PostgreSQL.

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

Querying JSON values

Use Select::jsonCondition() (or its trait counterpart QueryConditionTrait::jsonCondition()) to filter a select query by the value at a JSON path:

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

Supported operators cover the usual equality and comparison operators (=, <>, <, <=, >, >=), IN / NOT IN, BETWEEN / NOT BETWEEN, IS NULL / IS NOT NULL, HAS KEY, JSON_CONTAINS, and the string-matching CONTAINS, STARTS_WITH, and ENDS_WITH.

For more complex cases, get a JsonExpression directly and embed it in custom SQL:

$expression = $connection->jsonExpression('data', '$.number');
$query->addExpression($expression->toCastSql(JsonCastType::Int), 'n');
$query->orderBy('n');

For contrib database drivers

Drivers must provide a Drupal\Core\Database\Query\JsonExpression subclass under their own namespace to support JSON queries. Connection::jsonExpression() picks it up automatically via getDriverClass('JsonExpression').

Until Drupal 12.0.0 the base Connection::jsonExpression() is non-abstract and throws a \LogicException (after an E_USER_DEPRECATED) when called on a driver that has no implementation. From Drupal 12.0.0 the method becomes abstract. See the mysql, pgsql, and sqlite subclasses in core for working examples.