DEV Community

Cover image for How to Query a JSON in PostgreSQL
Rupesh Padhye
Rupesh Padhye

Posted on

3 2

How to Query a JSON in PostgreSQL

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

💭 Use Case

Lets explore the capabilities of JSONB with a use case of school table which has school information and app settings

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

⚓ JSON document

Document snapshot

⚓ Querying the JSON document

1️⃣ 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';

💡 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

2️⃣ Get schools where "resources" are present in enabledModules array

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

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

3️⃣ Get schools where "resources" and "discussion" are present in enabledModules array

select 
    id, name 
from 
     schools 
where 
settings -> 'enabledModules' @>'["resources","discussion"]'::jsonb;

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

⚓ Updating a JSON document

1️⃣ disable employee module for school id 1

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

⚓ Delete attribute from JSON document

1️⃣ Remove "discussion" from enabledModules array

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

💡 Deleting a key from a JSON object or from an array can be done with the -

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

📌 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
|| concatenate two json

📌 To solidify the concepts read official documentation

You can play with JSONB column and queries with DB Fiddle

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

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry 🕒

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay