Orm: PostgreSQL jsonb support

[tasselchof]

I am trying to set options to create jsonb field:

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

But generated SQL is still JSON:

ALTER TABLE reports_reports ALTER settings TYPE JSON;

[dunglas]

What version of ORM and DBAL do you use?

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

[Armenian]

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.

[dunglas]

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

[Armenian]

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

[tasselchof]

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.

[dunglas]

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

[tasselchof]

@dunglas how you annotated this fields?

[dunglas]

@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.

[tasselchof]

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

[tasselchof]

@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]

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.

[michalwiking]

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.

[miholeus]

Are there any plans on jsonb support?

[Ocramius]

@miholeus already supported in DBAL

[miholeus]

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

[leberknecht]

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

[Ocramius]

Bound parameter likely needs to be cast to string

[leberknecht]

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

[Aerendir]

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

[kconde2]

Hello, I'm getting this error on PostgreSQL et EasyAdmin by doing this :
dql_filter: "entity.roles LIKE '%%ROLE_USER%%'"

Do you have solution for this ?

It doesn't still work, something I'm doing wrong 🤔