PostgreSQL jsonb support #5112

@dunglas commented on GitHub (Apr 27, 2016):

What version of ORM and DBAL do you use?

JSONB support is only available for DBAL 5.6 (dev-master).

@dunglas commented on GitHub (Apr 27, 2016): What version of ORM and DBAL do you use? JSONB support is only available for DBAL 5.6 (`dev-master`).

admin commented

@Armenian commented on GitHub (May 2, 2016):

Hi,

I have this problem too. We have PG v9.5.
dbal version - 2.5.1
orm version - 2.5.1

We use doctrine 2 with ZF2.

@Armenian commented on GitHub (May 2, 2016): Hi, I have this problem too. We have PG v9.5. dbal version - 2.5.1 orm version - 2.5.1 We use doctrine 2 with ZF2.

admin commented

@dunglas commented on GitHub (May 2, 2016):

@Armenian see my previous comment. You need versions 2.6 (not tagged yet).

@dunglas commented on GitHub (May 2, 2016): @Armenian see my previous comment. You need versions 2.6 (not tagged yet).

admin commented

2026-01-22 14:58:40 +01:00

@Armenian commented on GitHub (May 2, 2016):

I have updated dbal and orm to dev-master but this does not helped.

@Armenian commented on GitHub (May 2, 2016): I have updated dbal and orm to dev-master but this does not helped.

admin commented

@tasselchof commented on GitHub (May 2, 2016):

I also updated:
doctrine/dbal dev-master 3df22cc Database Abstraction Layer
doctrine/orm dev-master 59a0410 Object-Relational-Mapper for PHP

Column is annotated like this:

@ORM\Column(type="json_array", nullable=true, options={"jsonb": true})

But no effect.

@Armenian we are also using ZF2 + Doctrine 2.

@tasselchof commented on GitHub (May 2, 2016): I also updated: doctrine/dbal dev-master 3df22cc Database Abstraction Layer doctrine/orm dev-master 59a0410 Object-Relational-Mapper for PHP Column is annotated like this: - @ORM\Column(type="json_array", nullable=true, options={"jsonb": true}) But no effect. @Armenian we are also using ZF2 + Doctrine 2.

admin commented

@dunglas commented on GitHub (May 2, 2016):

Weird, it works fo me (I used the lib trough the Symfony bundle).

@dunglas commented on GitHub (May 2, 2016): Weird, it works fo me (I used the lib trough the Symfony bundle).

admin commented

@tasselchof commented on GitHub (May 2, 2016):

@dunglas how you annotated this fields?

@tasselchof commented on GitHub (May 2, 2016): @dunglas how you annotated this fields?

admin commented

@dunglas commented on GitHub (May 2, 2016):

@tasselchof https://github.com/dunglas/doctrine-json-odm/blob/master/tests/Fixtures/TestBundle/Entity/Foo.php#L33-L36

json_document is a type provided by my lib but it extends json_array.

@dunglas commented on GitHub (May 2, 2016): @tasselchof https://github.com/dunglas/doctrine-json-odm/blob/master/tests/Fixtures/TestBundle/Entity/Foo.php#L33-L36 `json_document` is a type provided by my lib but it extends `json_array`.

admin commented

2026-01-22 14:58:42 +01:00

@tasselchof commented on GitHub (May 2, 2016):

I'll check zf2 module that is used and after will write here.

@tasselchof commented on GitHub (May 2, 2016): I'll check zf2 module that is used and after will write here.

admin commented

@tasselchof commented on GitHub (May 2, 2016):

@dunglas What versions of other components are you using?

My are:

doctrine/annotations dev-master 2e1b1f7 Docblock Annotations Parser
doctrine/cache v1.6.0 Caching library offering an object-oriented API for many cache back...
doctrine/collections v1.3.0 Collections Abstraction library
doctrine/common v2.6.1 Common Library for Doctrine projects
doctrine/dbal dev-master 3df22cc Database Abstraction Layer
doctrine/doctrine-module 1.0.1 Zend Framework 2 Module that provides Doctrine basic functionality ...
doctrine/doctrine-orm-module 0.10.0 Zend Framework 2 Module that provides Doctrine ORM functionality
doctrine/inflector v1.1.0 Common String Manipulations with regard to casing and singular/plur...
doctrine/instantiator 1.0.5 A small, lightweight utility to instantiate objects in PHP without ...
doctrine/lexer v1.0.1 Base library for a lexer that can be used in Top-Down, Recursive De...
doctrine/orm dev-master 59a0410 Object-Relational-Mapper for PHP

@tasselchof commented on GitHub (May 2, 2016): @dunglas What versions of other components are you using? My are: doctrine/annotations dev-master 2e1b1f7 Docblock Annotations Parser doctrine/cache v1.6.0 Caching library offering an object-oriented API for many cache back... doctrine/collections v1.3.0 Collections Abstraction library doctrine/common v2.6.1 Common Library for Doctrine projects doctrine/dbal dev-master 3df22cc Database Abstraction Layer doctrine/doctrine-module 1.0.1 Zend Framework 2 Module that provides Doctrine basic functionality ... doctrine/doctrine-orm-module 0.10.0 Zend Framework 2 Module that provides Doctrine ORM functionality doctrine/inflector v1.1.0 Common String Manipulations with regard to casing and singular/plur... doctrine/instantiator 1.0.5 A small, lightweight utility to instantiate objects in PHP without ... doctrine/lexer v1.0.1 Base library for a lexer that can be used in Top-Down, Recursive De... doctrine/orm dev-master 59a0410 Object-Relational-Mapper for PHP

admin commented

@tasselchof commented on GitHub (May 2, 2016):

Found a problem, i think it's a bug.

If you already created you column as JSON type on automatically columns update via:
/opt/alt/php70/usr/bin/php ./vendor/bin/doctrine-module orm:schema-tool:update --dump-sql

It's not trying to switch it to JSONB, but if you are creating new column this annotation will work:

@ORM\Column(type="json_array", nullable=true, options={"jsonb": true})

Will generate this code:
ALTER TABLE reports_reports ALTER result TYPE JSONB;
ALTER TABLE reports_reports ALTER result DROP DEFAULT;

I changed column type manually to JSONB and update is not trying to switch it back to JSON is options={"jsonb": true} is set.

@tasselchof commented on GitHub (May 2, 2016): Found a problem, i think it's a bug. If you already created you column as JSON type on automatically columns update via: /opt/alt/php70/usr/bin/php ./vendor/bin/doctrine-module orm:schema-tool:update --dump-sql It's not trying to switch it to JSONB, but if you are creating new column this annotation will work: - @ORM\Column(type="json_array", nullable=true, options={"jsonb": true}) Will generate this code: ALTER TABLE reports_reports ALTER result TYPE JSONB; ALTER TABLE reports_reports ALTER result DROP DEFAULT; I changed column type manually to JSONB and update is not trying to switch it back to JSON is options={"jsonb": true} is set.

admin commented

@michalwiking commented on GitHub (Oct 22, 2016):

I traced it and it's DBAL issue, in: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php#L521 there is no if for checking jsonb option. Changed column is passed to getAlterTableSQL in changedColumns array but no SQL is generated.

And i think its both ways from jsonb true to false and from false to true.

@michalwiking commented on GitHub (Oct 22, 2016): I traced it and it's DBAL issue, in: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php#L521 there is no if for checking jsonb option. Changed column is passed to getAlterTableSQL in changedColumns array but no SQL is generated. And i think its both ways from jsonb true to false and from false to true.

admin commented

@miholeus commented on GitHub (Oct 13, 2017):

Are there any plans on jsonb support?

@miholeus commented on GitHub (Oct 13, 2017): Are there any plans on `jsonb` support?

admin commented

@Ocramius commented on GitHub (Oct 13, 2017):

@miholeus already supported in DBAL

@Ocramius commented on GitHub (Oct 13, 2017): @miholeus already supported in DBAL

admin commented

2026-01-22 14:58:44 +01:00

@miholeus commented on GitHub (Oct 13, 2017):

Oh, I see it appeared in version 2.6. Thanks!

@miholeus commented on GitHub (Oct 13, 2017): Oh, I see it appeared in version 2.6. Thanks!

admin commented

@leberknecht commented on GitHub (Apr 4, 2018):

Does DQL support LIKE queries on json-array fields on postgres? When fired like

WHERE entity.jsonArrayField LIKE :keyword

I'm getting

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json ~~ unknown

@leberknecht commented on GitHub (Apr 4, 2018): Does DQL support `LIKE` queries on json-array fields on postgres? When fired like WHERE entity.jsonArrayField LIKE :keyword I'm getting SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json ~~ unknown

admin commented

@Ocramius commented on GitHub (Apr 4, 2018):

Bound parameter likely needs to be cast to string

@Ocramius commented on GitHub (Apr 4, 2018): Bound parameter likely needs to be cast to string

admin commented

@leberknecht commented on GitHub (Apr 4, 2018):

Hm, the parameter is a string, according to the logs, the resulting query looks like this:

SELECT p0_.id AS id_0, p0_.values AS values_1 FROM product p0_ WHERE p0_.values LIKE ?

With params: ["test"]

Its from this small sample application: https://github.com/leberknecht/easy-admin-postgres-json-search which is basically SF4 + easy admin + postgres and one entity. values is a json_array, and as the driver is postgres, i would think the resulting query should look something like

SELECT p0_.id AS id_0, p0_.values AS values_1 FROM product p0_ WHERE p0_.values::jsonb ? ?

no? (...except for the ? ? which probably is wrong anyways as the first ? is the operator for jsonb, not a placeholder)

From the DQL generated by easy-admin bundles createSearchQueryBuilder:

$queryBuilder->orWhere(sprintf('%s.%s LIKE :fuzzy_query', $entityName, $fieldName));
$queryParameters['fuzzy_query'] = '%'.$lowerSearchQuery.'%';

Is there a test somewhere that illustrates how to use DQL to search in json-arrays in postgres?

Edit:
forgot to mention, to avoid confusion: i have modified Easy-admin bundle here, as i am looking at https://github.com/EasyCorp/EasyAdminBundle/issues/2184

@leberknecht commented on GitHub (Apr 4, 2018): Hm, the parameter is a string, according to the logs, the resulting query looks like this: SELECT p0_.id AS id_0, p0_.values AS values_1 FROM product p0_ WHERE p0_.values LIKE ? With params: `["test"]` Its from this small sample application: https://github.com/leberknecht/easy-admin-postgres-json-search which is basically SF4 + easy admin + postgres and one entity. `values` is a `json_array`, and as the driver is postgres, i would think the resulting query should look something like SELECT p0_.id AS id_0, p0_.values AS values_1 FROM product p0_ WHERE p0_.values::jsonb ? ? no? (...except for the `? ?` which probably is wrong anyways as the first `?` is the operator for jsonb, not a placeholder) From the DQL generated by easy-admin bundles `createSearchQueryBuilder`: $queryBuilder->orWhere(sprintf('%s.%s LIKE :fuzzy_query', $entityName, $fieldName)); $queryParameters['fuzzy_query'] = '%'.$lowerSearchQuery.'%'; Is there a test somewhere that illustrates how to use DQL to search in json-arrays in postgres? Edit: forgot to mention, to avoid confusion: i have modified Easy-admin bundle here, as i am looking at https://github.com/EasyCorp/EasyAdminBundle/issues/2184

admin commented

@Aerendir commented on GitHub (Feb 21, 2020):

I confirm the bug is already present.

First field creation:

    /**
     * @var array
     * @ORM\Column(type="json")
     */
    private $metadata;

Updating to jsonb

    /**
     * @var array
     * @ORM\Column(type="json", options={"jsonb": true})
     */
    private $metadata;

This doesn't transform the field into a jsonb one but leaves it as json.

This causes errors like this:

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist:
json @> unknown LINE 1: ... FROM leads.sources_leads s0_ WHERE (s0_.metadata @> $1) = t...
.........^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Spent last two hours to understand why the query didn't worked! 😤

The solution was as simple as dropping the field and run again

bin/console doctrine:schema:update --force

@Aerendir commented on GitHub (Feb 21, 2020): I confirm the bug is already present. First field creation: ```php /** * @var array * @ORM\Column(type="json") */ private $metadata; ``` Updating to `jsonb` ```php /** * @var array * @ORM\Column(type="json", options={"jsonb": true}) */ private $metadata; ``` This doesn't transform the field into a `jsonb` one but leaves it as `json`. This causes errors like this: > SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: > json @> unknown LINE 1: ... FROM leads.sources_leads s0_ WHERE (s0_.metadata @> $1) = t... >.........^ > HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. Spent last two hours to understand why the query didn't worked! 😤 The solution was as simple as dropping the field and run again ```console bin/console doctrine:schema:update --force ```

admin commented