Querying PostgreSQL’s JSONB with Zend\Db

PostgreSQL is one of Databases that has json support. If you have table(s) that has json column(s), we can query it with Zend\Db in your php application layer.

For example, we have the following data:

CREATE TABLE album (id serial primary key, data jsonb);

INSERT INTO album VALUES
   (1, '{ "title": "Hello", "singer": "Adelle" }'),
   (2, '{ "title": "September", "singer": "Justin Timberlake" }')
;

We then want to produce SQL like :

select * from album where data ->> 'title' = 'Hello'

There is Zend\Db\Sql\Predicate\Expression for that. So, you can build select with:

use Zend\Db\Sql\Select;
use Zend\Db\Sql\Predicate\Expression;

$select = new Select();
$select->from('album')
       ->where([new Expression("data ->> 'title' = ?", 'Hello')]);

That’s easy! Let’s make it more complicated. We then want to produce SQL with subset of jsonb like:

select * from album where data @> '{"singer": "Adelle"}'

In this case, as it is not a common sql operation across DBs, you need to pass the filter as $expression, the first parameter of Zend\Db\Sql\Predicate\Expression::__construct:

use Zend\Db\Sql\Select;
use Zend\Db\Sql\Predicate\Expression;

$expression = <<<expr
data @> '{"singer": "Adelle"}'
expr;

$select = new Select();
$select->from('album')
       ->where([new Expression($expression)]);

That’s it 😉

Advertisements