This page covers PostgreSQL array and JSON/JSONB operators and functions available in this library.
See also: Examples for these functions in whole queries
Array and JSON operators
Some PostgreSQL operators have multiple meanings depending on the data types involved. This library provides specific DQL function names to avoid conflicts:
| Operator | Array/JSON usage | Spatial usage | Text/pattern usage |
|---|---|---|---|
@> |
CONTAINS (arrays contain elements) |
N/A (PostGIS defines no @>; see ~) |
N/A |
<@ |
IS_CONTAINED_BY (element in array) |
N/A (PostGIS defines no <@; see @) |
N/A |
&& |
OVERLAPS (arrays/ranges overlap) |
Works automatically with geometry/geography | N/A |
Usage Guidelines:
- Arrays/JSON: Use
CONTAINS,IS_CONTAINED_BY,OVERLAPSfor array and JSON operations - Boolean operators: All operators return boolean values and should be used with
= TRUEor= FALSEin DQL (see Writing DQL)
Array and JSON operators
| PostgreSQL operator | Register for DQL as | Implemented by |
|---|---|---|
| @> | CONTAINS | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Contains |
| <@ | IS_CONTAINED_BY | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\IsContainedBy |
| && | OVERLAPS | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Overlaps |
| ? | RIGHT_EXISTS_ON_LEFT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\TheRightExistsOnTheLeft |
| ?& | ALL_ON_RIGHT_EXIST_ON_LEFT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\AllOnTheRightExistOnTheLeft |
| ?| | ANY_ON_RIGHT_EXISTS_ON_LEFT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\AnyOnTheRightExistsOnTheLeft |
| @? | RETURNS_VALUE_FOR_JSON_VALUE | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ReturnsValueForJsonValue |
| #- | DELETE_AT_PATH | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\DeleteAtPath |
| -> | JSON_GET_FIELD | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonGetField |
| -» | JSON_GET_FIELD_AS_TEXT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonGetFieldAsText |
| #> | JSON_GET_OBJECT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonGetObject |
| #» | JSON_GET_OBJECT_AS_TEXT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonGetObjectAsText |
Array functions
| PostgreSQL functions | Register for DQL as | Implemented by |
|---|---|---|
| all | ALL_OF | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\All |
| any | ANY_OF | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Any |
| any_value | ANY_VALUE | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\AnyValue |
| array_agg | ARRAY_AGG | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayAgg |
| array_append | ARRAY_APPEND | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayAppend |
| array_cat | ARRAY_CAT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayCat |
| array_dims | ARRAY_DIMENSIONS | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayDimensions |
| array_fill | ARRAY_FILL | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayFill |
| array_length | ARRAY_LENGTH | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayLength |
| array_lower | ARRAY_LOWER | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayLower |
| array_ndims | ARRAY_NUMBER_OF_DIMENSIONS | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayNumberOfDimensions |
| array_position | ARRAY_POSITION | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayPosition |
| array_positions | ARRAY_POSITIONS | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayPositions |
| array_prepend | ARRAY_PREPEND | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayPrepend |
| array_remove | ARRAY_REMOVE | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayRemove |
| array_replace | ARRAY_REPLACE | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayReplace |
| array_reverse | ARRAY_REVERSE | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayReverse |
| array_sample | ARRAY_SAMPLE | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArraySample |
| array_shuffle | ARRAY_SHUFFLE | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayShuffle |
| array_sort | ARRAY_SORT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArraySort |
| array_to_json | ARRAY_TO_JSON | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayToJson |
| array_to_string | ARRAY_TO_STRING | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayToString |
| array_upper | ARRAY_UPPER | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayUpper |
| cardinality | ARRAY_CARDINALITY | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayCardinality |
| string_to_array | STRING_TO_ARRAY | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\StringToArray |
| trim_array | TRIM_ARRAY | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\TrimArray |
| unnest | UNNEST | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Unnest |
JSON functions
| PostgreSQL functions | Register for DQL as | Implemented by |
|---|---|---|
| json | JSON | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Json |
| json_agg | JSON_AGG | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonAgg |
| json_array_length | JSON_ARRAY_LENGTH | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonArrayLength |
| json_build_array | JSON_BUILD_ARRAY | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonBuildArray |
| json_build_object | JSON_BUILD_OBJECT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonBuildObject |
| json_each | JSON_EACH | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonEach |
| json_each_text | JSON_EACH_TEXT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonEachText |
| json_exists | JSON_EXISTS | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonExists |
| json_extract_path | JSON_EXTRACT_PATH | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonExtractPath |
| json_extract_path_text | JSON_EXTRACT_PATH_TEXT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonExtractPathText |
| json_object_agg | JSON_OBJECT_AGG | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonObjectAgg |
| json_object_keys | JSON_OBJECT_KEYS | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonObjectKeys |
| json_query | JSON_QUERY | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonQuery |
| json_scalar | JSON_SCALAR | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonScalar |
| json_serialize | JSON_SERIALIZE | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonSerialize |
| json_strip_nulls | JSON_STRIP_NULLS | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonStripNulls |
| json_typeof | JSON_TYPEOF | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonTypeof |
| json_value | JSON_VALUE | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonValue |
| row | ROW | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Row |
| row_to_json | ROW_TO_JSON | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\RowToJson |
| to_json | TO_JSON | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ToJson |
| to_jsonb | TO_JSONB | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ToJsonb |
JSON(value WITH UNIQUE KEYS) fails on an object that repeats a key. It takes only a text value, such as a string literal or a text field: PostgreSQL rejects a json or jsonb value with cannot use non-string types with WITH UNIQUE KEYS clause.
JSONB functions
| PostgreSQL functions | Register for DQL as | Implemented by |
|---|---|---|
| jsonb_array_elements | JSONB_ARRAY_ELEMENTS | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbArrayElements |
| jsonb_agg | JSONB_AGG | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbAgg |
| jsonb_array_elements_text | JSONB_ARRAY_ELEMENTS_TEXT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbArrayElementsText |
| jsonb_array_length | JSONB_ARRAY_LENGTH | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbArrayLength |
| jsonb_build_array | JSONB_BUILD_ARRAY | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbBuildArray |
| jsonb_build_object | JSONB_BUILD_OBJECT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbBuildObject |
| jsonb_each | JSONB_EACH | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbEach |
| jsonb_each_text | JSONB_EACH_TEXT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbEachText |
| jsonb_exists | JSONB_EXISTS | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbExists |
| jsonb_extract_path | JSONB_EXTRACT_PATH | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbExtractPath |
| jsonb_extract_path_text | JSONB_EXTRACT_PATH_TEXT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbExtractPathText |
| jsonb_insert | JSONB_INSERT | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbInsert |
| jsonb_object_agg | JSONB_OBJECT_AGG | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbObjectAgg |
| jsonb_object_keys | JSONB_OBJECT_KEYS | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbObjectKeys |
| jsonb_path_exists | JSONB_PATH_EXISTS | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbPathExists |
| jsonb_path_match | JSONB_PATH_MATCH | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbPathMatch |
| jsonb_path_query | JSONB_PATH_QUERY | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbPathQuery |
| jsonb_path_query_array | JSONB_PATH_QUERY_ARRAY | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbPathQueryArray |
| jsonb_path_query_first | JSONB_PATH_QUERY_FIRST | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbPathQueryFirst |
| jsonb_pretty | JSONB_PRETTY | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbPretty |
| jsonb_set | JSONB_SET | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbSet |
| jsonb_set_lax | JSONB_SET_LAX | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbSetLax |
| jsonb_strip_nulls | JSONB_STRIP_NULLS | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbStripNulls |
| jsonb_to_tsvector | JSONB_TO_TSVECTOR | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbToTsvector |
| jsonb_typeof | JSONB_TYPEOF | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbTypeof |
Bonus helpers
| PostgreSQL functions | Register for DQL as | Implemented by |
|---|---|---|
| array | ARRAY | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Arr |
| value = ANY(list of values) | IN_ARRAY | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\InArray |
| CAST(json -» node as BIGINT) | JSON_GET_FIELD_AS_INTEGER | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonGetFieldAsInteger |
| aggregate FILTER (WHERE condition) | FILTER | MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Filter |
ARRAY takes strings, fields, parameters and function calls; a bare number such as ARRAY(1, 2) does not parse. Quoted elements build a text[], which PostgreSQL will not compare with a non-text array column - there, pass a PostgreSQL array literal instead: CONTAINS(e.integerArray, '{1,2}') = TRUE.
FILTER wraps the aggregate in DQL
In SQL, FILTER (WHERE ...) follows the aggregate’s closing parenthesis. DQL cannot parse anything after a function’s closing parenthesis, so in DQL FILTER becomes a function around the aggregate, with the condition as its second argument:
| SQL | DQL |
|---|---|
COUNT(e.id) FILTER (WHERE e.status = 'active') |
FILTER(COUNT(e.id), WHERE e.status = 'active') |
SUM(e.amount) FILTER (WHERE e.refunded = false) |
FILTER(SUM(e.amount), WHERE e.refunded = FALSE) |
array_agg(e.id ORDER BY e.createdAt) FILTER (WHERE e.archivedAt IS NULL) |
FILTER(ARRAY_AGG(e.id ORDER BY e.createdAt), WHERE e.archivedAt IS NULL) |
percentile_cont(0.5) WITHIN GROUP (ORDER BY e.amount) FILTER (WHERE e.paid = true) |
FILTER(PERCENTILE_CONT(0.5 WITHIN GROUP ORDER BY e.amount), WHERE e.paid = TRUE) |
- A comma separates the aggregate from
WHERE, and the condition has no parentheses around it. - The first argument must be an aggregate: DQL’s own
AVG,COUNT,MAX,MINandSUM, or a function implementingMartinGeorgiev\Doctrine\ORM\Query\AST\Functions\AggregateFunction. Every aggregate in this library implements it; implement it on an aggregate of your own to wrap that one too. Anything else, including a nestedFILTER, throws aParserException, as PostgreSQL would reject it. - The condition takes anything a DQL
WHEREdoes, including parameters.
Usage examples
The results come from these rows of Entity:
| id | name | status | score | category | tags | categories | createdAt | archivedAt |
|---|---|---|---|---|---|---|---|---|
| 1 | Dune | active | 91 | books | {important,urgent} |
{admin} |
2026-01-10 09:00 | |
| 2 | Neuromancer | active | 78 | books | {important} |
{user,guest} |
2026-02-05 09:00 | 2026-06-01 09:00 |
| 3 | Kind of Blue | draft | 85 | music | {urgent,important,later} |
{guest} |
2026-03-15 09:00 |
Each -- → line under a query is one row of its result. A query without its own ORDER BY has its rows listed in sample-row order, or by group.
-- DQL array literal syntax: ARRAY('val1', 'val2') - not standard PHP array notation
SELECT e FROM Entity e WHERE CONTAINS(e.tags, ARRAY('important', 'urgent')) = TRUE
-- → Entity {id: 1}
-- Entity {id: 3}
SELECT e FROM Entity e WHERE OVERLAPS(e.categories, ARRAY('admin', 'user')) = TRUE
-- → Entity {id: 1}
-- Entity {id: 2}
-- JSON_BUILD_OBJECT takes alternating key/value pairs; result is a JSON object
SELECT e.id, JSON_BUILD_OBJECT('name', e.name, 'status', e.status, 'score', e.score) as json_data FROM Entity e
-- → ['id' => 1, 'json_data' => '{"name" : "Dune", "status" : "active", "score" : 91}']
-- ['id' => 2, 'json_data' => '{"name" : "Neuromancer", "status" : "active", "score" : 78}']
-- ['id' => 3, 'json_data' => '{"name" : "Kind of Blue", "status" : "draft", "score" : 85}']
-- ARRAY_AGG with ORDER BY inside the aggregate
SELECT e.category, ARRAY_AGG(e.id ORDER BY e.createdAt DESC) as entity_ids FROM Entity e GROUP BY e.category
-- → ['category' => 'books', 'entity_ids' => '{2,1}']
-- ['category' => 'music', 'entity_ids' => '{3}']
-- FILTER wraps the aggregate in DQL (see above)
SELECT e.category, FILTER(COUNT(e.id), WHERE e.status = 'active') as active_count FROM Entity e GROUP BY e.category
-- → ['category' => 'books', 'active_count' => 2]
-- ['category' => 'music', 'active_count' => 0]
SELECT e.category, FILTER(ARRAY_AGG(e.id ORDER BY e.createdAt), WHERE e.archivedAt IS NULL) as live_ids FROM Entity e GROUP BY e.category
-- → ['category' => 'books', 'live_ids' => '{1}']
-- ['category' => 'music', 'live_ids' => '{3}']