JSONB_CONTAINS and ::jsonb

[jraller]

JSONB_CONTAINS is included in the codebase under the Postgres section, but isn't mentioned in the documentation. It appears to generate the correct SQL with the exception of not casting either parameter with ::jsonb.

Is there a way to use JSONB_CONTAINS with ->setParameter() that will result in the field reference and the parameter value being followed by ::jsonb?

I was able to use createNativeQuery and ResultSetMappingBuilder and get the expected result, and I've been able to write an OrX condition using JSON_GET_PATH_TEXT to or together several paths, but I'd prefer to use querybuilder and JSONB_CONTAINS if possible.

[jraller]

While this package doesn't include a DBAL jsonb type using another package that had that improved things. Setting both the ORM\Column type on the entity and setting the type in setParameter to 'jsonb' combined with a database migration to pick up the entity change improved things.

[jraller]

Further investigation shows that the parameter doesn't need to be typed with jsonb, just the entity column. Likely a documentation improvement would clear this up where the column can be set to jsonb instead of the built in DBAL JSON type.

[quentint]

I know this is an old issue, but I faced the same questions and I'm sure my experience could help someone 😉

I could not get this bundle to work (Symfony 6, PostgreSQL 14) so I ended up using opsway/doctrine-dbal-postgresql. After thinking more about this, I think my PR here helps when dealing with existing fields: opsway/doctrine-dbal-postgresql#39

Maybe this bundle could also benefit from it?