JSON Extraction

When an entity property is declared as type="json", individual keys inside the stored JSON value can be read directly in a query using dot notation.

explanation

JSON Column Path Access

The first property segment after the range alias selects the JSON column; any further segments are treated as a path inside that JSON value:

// Read a single key from a JSON column
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p.attributes.color)
");

foreach ($results as $row) {
    $color = $row['p.attributes.color']; // scalar value extracted from JSON
}

// Read multiple keys from the same JSON column
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p.attributes.color, p.attributes.size, p.attributes.weight)
");

// Access a nested key (two levels deep)
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p.attributes.dimensions.width)
");

JSON path access also works in where clauses and sort by:

// Filter on a JSON key
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p)
    where p.attributes.inStock = true
");

// Sort by a JSON key
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p.name, p.attributes.price)
    sort by p.attributes.price asc
");
JSON path access generates dialect-appropriate SQL: JSON_UNQUOTE(JSON_EXTRACT(...)) for MySQL and older MariaDB/SQLite, JSON_VALUE(...) for MariaDB ≥ 10.9 and SQLite ≥ 3.38, and the #>> operator for PostgreSQL. The result is always a scalar string. If you need the complete JSON value as a PHP array, retrieve the full entity or the column itself (p.attributes) rather than a path inside it.