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.sourcesleads 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 🤔

[simPod]

The jsonb support is added already so this issue might get closed, right?

[back-2-95]

Related to this, how to configure this with attributes (PHP 8.1)?

Background: Symfony 6, PostgreSQL 13, dbal 3.2.1, orm 2.10.4

I cannot get anything accepted, eg this:

    #[ORM\Column(type: 'json', options: ['jsonb':true])]
    private array $some_things_here = [];

options must be an array there, but it cannot be associative.

Code also suggests you could set type to "jsonb" but then bin/console doctrine:schema:update --force will give (as there really is no Type [jsonb] => Doctrine\DBAL\Types\JsonbType:

Unknown column type "jsonb" requested. Any Doctrine type that you use has t
  o be registered with \Doctrine\DBAL\Types\Type::addType(). You can get a li
  st of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). If
   this error occurs during database introspection then you might have forgot
  ten to register all database types for a Doctrine Type. Use AbstractPlatfor
  m#registerDoctrineTypeMapping() or have your custom types implement Type#ge
  tMappedDatabaseTypes(). If the type name is empty you might have a problem
  with the cache or forgot some mapping information.

Doctrine documentation just refers to the option "jsonb":true but nothing more.

What am I missing here?

[beberlei]

Inside array the syntax requires => not :

[back-2-95]

Thanks @beberlei - this works:

#[ORM\Column(type: 'json', options: ['jsonb' => true])]

[b1rdex]

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

The issue is still there. Enabling jsonb for an already present json field doesn't produce a migration diff.