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

|
| By Webner

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"}');

1
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"}
                )
 
        )
 
)

Leave a Reply

Your email address will not be published. Required fields are marked *