This bundle extends Doctrine to use the jsonb datatype that ships with Postgresql 9.4.
This bundle is fully compatible with Symfony, but you do not have to use Symfony (see the composer.json for dependencies).
Please make sure you have Postgresql with a version of at least 9.4 installed before using this bundle.
The Bundle allows to create Jsonb fields and use the @>,? and the #>> operator on the Jsonb field.
Other Operations can be easily added.
I recently discovered the power of NativeQueries (http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html). Right now I only use NativeQueries when querying. An example is shown below.
Open a command console, enter your project directory and execute the following command to download the latest stable version of this bundle:
$ composer require "boldtrn/jsonb-bundle": "~1.0"# config.yml
doctrine:
dbal:
types:
jsonb: Boldtrn\JsonbBundle\Types\JsonbArrayType
mapping_types:
jsonb: jsonb
orm:
dql:
string_functions:
JSONB_AG: Boldtrn\JsonbBundle\Query\JsonbAtGreater
JSONB_HGG: Boldtrn\JsonbBundle\Query\JsonbHashGreaterGreater
JSONB_EX: Boldtrn\JsonbBundle\Query\JsonbExistence
Note: There were people having issues with the above configuration. They had the following exception:
[Symfony\Component\Config\Definition\Exception\InvalidConfigurationException]
Unrecognized options "dql" under "doctrine.orm"
This was fixed by changing the dql part in the following (add the entity_managers between orm and dql):
doctrine:
orm:
entity_managers:
dql:
/**
* @Entity
*/
class Test
{
/**
* @Id
* @Column(type="string")
* @GeneratedValue
*/
public $id;
/**
* @Column(type="jsonb")
*
* Usually attrs is an array, depends on you
*
*/
public $attrs = array();
}
$q = $this
->entityManager
->createNativeQuery(
"
SELECT t.id, t.attrs
FROM Test t
WHERE t.attrs @> 'value'
"
, $rsm);
You only need to setup the $rsm ResultSetMapping according to the Doctrine documentation.
This example shows how to use the contains statement in a WHERE clause.
The = TRUE is a workaround for Doctrine that needs an comparison operator in the WHERE clause.
$q = $this
->entityManager
->createQuery(
"
SELECT t
FROM E:Test t
WHERE JSONB_AG(t.attrs, 'value') = TRUE
"
);
This produces the following Query:
SELECT t0_.id AS id0, t0_.attrs AS attrs1 FROM Test t0_ WHERE (t0_.attrs @> 'value') = true
This example shows how to query for a value that is LIKE %d%
The result could be data like:
id | attrs
----+--------------------------------------
4 | {"a": 1, "b": {"c": "abcdefg", "e": true}}
$q = $this
->entityManager
->createQuery(
"
SELECT t
FROM E:Test t
WHERE JSONB_HGG(t.attrs , '{\"b\",\"c\"}') LIKE '%d%'
"
);
This produces the following Query:
SELECT t0_.id AS id0, t0_.attrs AS attrs1 FROM Test t0_ WHERE (t0_.attrs #>> '{\"object1\",\"object2\"}') LIKE '%a%'
The ? operator is implemented by calling its function jsonb_exists(column_name, value) since Doctrine will consider it a parameter placeholder otherwise. The same must be done if you want to implement ?| and ?& operators, using jsonb_exists_any(column_name, value) and jsonb_exists_all(column_name, value) respectively