Welcome to Abdul Malik Ikhsan's Blog

Querying PostgreSQL’s JSONB with Zend\Db

Posted in Tutorial PHP by samsonasik on March 13, 2017

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: