How to Query a JSON in PostgreSQL 
Tags: sql, database, postgres, tutorial
Published: 2020-07-15 09:59:22


PostgreSQL has support to store semistructured data in JSON column.

Database has two JSON data types to store JSON documents: `JSON` and `JSONB`. 
- JSON stores data in a raw format and JSONB stores data in a custom binary format.  
- Binary JSON (JSONB) supports indexing. 

PostgreSQL documentation also recommendation to use `JSONB` since it is more performant

##:thought_balloon: Use Case 
Lets explore the capabilities of JSONB with a use case of school table which has school information and app settings

###:anchor: Schema creation

```
CREATE TABLE schools (  
      id serial NOT NULL,
      name text,
      is_active boolean default false,
      settings jsonb
    );
    
insert into schools values (1,'school_1',true ,'{"userTypes":{"student":{"activeModules":[{"id":"attendance","enable":"false"},{"id":"resources","enable":"true"},{"id":"discussion","enable":"true"},{"id":"employees","enable":"false"}]},"teacher":{"activeModules":[{"id":"attendance","enable":"true"},{"id":"resources","enable":"true"},{"id":"discussion","enable":"true"},{"id":"employees","enable":"false"}]},"principal":{"activeModules":[{"id":"attendance","enable":"true"},{"id":"resources","enable":"true"},{"id":"employees","enable":"true"},{"id":"discussion","enable":"true"}]}},"enabledModules":["attendance","resources","employees","discussion"],"moduleSettings":{"attendance":{"enable":true},"resources":{"enable":true},"employees":{"enable":true},"discussion":{"enable":true}}}');
    
insert into schools values (2,'school_2',true ,'{"userTypes":{"student":{"activeModules":[{"id":"attendance","enable":"false"},{"id":"resources","enable":"false"},{"id":"discussion","enable":"true"},{"id":"employees","enable":"false"}]},"teacher":{"activeModules":[{"id":"attendance","enable":"true"},{"id":"resources","enable":"true"},{"id":"discussion","enable":"true"},{"id":"employees","enable":"false"}]},"principal":{"activeModules":[{"id":"attendance","enable":"true"},{"id":"resources","enable":"true"},{"id":"employees","enable":"true"},{"id":"discussion","enable":"true"}]}},"enabledModules":["attendance","resources","employees","discussion"],"moduleSettings":{"attendance":{"enable":true},"resources":{"enable":true},"employees":{"enable":true},"discussion":{"enable":true}}}');
 
insert into schools values (3,'school_3',true ,'{"userTypes":{"student":{"activeModules":[{"id":"employees","enable":"false"}]},"teacher":{"activeModules":[{"id":"employees","enable":"false"}]},"principal":{"activeModules":[{"id":"employees","enable":"true"}]}},"enabledModules":["employees"],"moduleSettings":{"employees":{"enable":true}}}');
```

###:anchor: JSON document 

![Document snapshot](https://dev-to-uploads.s3.amazonaws.com/i/g2o3agd003n5osbes4no.png)

###:anchor: Querying the JSON document

:one: Get schools where employees module is currently enabled 

```
 select 
   id,name 
 from 
   schools 
 where 
   settings -> 'moduleSettings' -> 'employees' ->> 'enable' ::text = 'true';

| id  | name     |
| --- | -------- |
| 1   | school_1 |

```
The above same query can be written with path based operator (`#>>`) operator as 

``` 

select 
  id, name 
from schools where settings #>> '{moduleSettings,employees,enable}' = 'true';

```

:bulb: PostgreSQL has two native operators `->` and `->>` to query JSON documents. The first operator `->` returns a JSON object, while the operator `->>` returns text.

For `{"employees": null}` JSON object 
- val `->` 'employees' IS NULL is false 
- val `->>` 'employees' IS NULL is true


:two:  Get schools where "resources" are present in enabledModules array

```
select 
   id, name 
from 
  schools 
where 
  settings -> 'enabledModules' ? 'resources';

```
:bulb: Existence operator (`?`) tests whether a string appears as an object key or array element at the top level of the JSON value

:three: Get schools where "resources" and "discussion" are present in enabledModules array
```
select 
    id, name 
from 
     schools 
where 
settings -> 'enabledModules' @>'["resources","discussion"]'::jsonb;

```
:bulb: Containment operator (@>) tests whether whether one JSONB document has contained within it another one

###:anchor: Updating a JSON document
:one: disable `employee` module for school id 1 

```
UPDATE schools
SET settings = jsonb_set( settings::jsonb,
'{moduleSettings,employee,enable}', 'true') where id = 1;

```
###:anchor: Delete attribute from JSON document 
:one: Remove "discussion" from enabledModules array

```
UPDATE schools 
SET 
settings = jsonb_set(
settings::jsonb,'{enabledModules}', 
(settings ->'enabledModules')::jsonb -'discussion') 
where id = 1  ;

```

:bulb: Deleting a key from a JSON object or from an array can be done with the `-`

:bulb: To delete from deep in a JSON hierarchy can be done with the `#-` operator


:pushpin: Here is tabular for of PostgreSQL native operators and usage

| operators | usage                                         |
| ---    | --------                                      |
| ->     | Get JSON object field by key                  | 
| ->>    | Get JSON object field by text                  | 
| -      | Delete JSON object attribute or array item        | 
| #>     | Get JSON object at specified path              |   
| `#>>    | Get JSON object at specified path as text     |            
| #-     | Delete the field or element with specified path  |               
| ?      | Check string existance in top-level keys     |            
| @>     |  Check json is present in another json       |            
| &#124;&#124;     |  concatenate two json       |            

:pushpin: To solidify the concepts read official [documentation](https://www.postgresql.org/docs/9.5/functions-json.html) 

You can play with JSONB column and queries with [DB Fiddle](https://www.db-fiddle.com/f/gqEyLhk4moUYabj8KCjnvh/50)


Keeping the post as short as possible, Let's deep dive in functions and indexing in upcoming posts :pray: