Postgres | JSONB data type field in PostgreSQL(9.4) and using it in CakePHP

JSONB (JSON Blob) datatype in Postgres is used to save JSON data. We can search on JSONB type of column after creating an index on it.

For example:

Usage in PostgreSQL:

1. Create table and index

CREATE TABLE tests
(
  id integer NOT NULL,
  email citext NOT NULL,
  address jsonb NOT NULL,
  CONSTRAINT tests_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

— Index: tests_address_idx

CREATE INDEX tests_address_idx ON tests USING gin (address);

2. Insert into table

insert into tests(id,email,address)
values(2,'kailash@mail.com','{"city":"chd"}');

Usage in CakePHP:

3. Find records of city Moga

App::import('Model', 'Test');
        	$this->loadModel('Test');       
       	 $result = $this->Test->find('all',array('conditions'=>array(
               	 'address @> '=>'{ "city" : "moga" }'
       	 )));

The result of the query:

Array
(
    [0] => Array
        (
            [Test] => Array
                (
                    [id] => 1
                    [email] => email@mail.com
                    [address] => {"city": "moga"}
                )
 
        )
 
)