chapter five

5 Postgres and JSON

This chapter covers

Storing JSON data in Postgres
Querying JSON data using built-in operators and functions
Updating specific fields in existing JSON objects
Optimizing JSON data access with GIN and B-tree indexes

Postgres introduced initial support for JSON back in 2012. Since then, JSON-specific capabilities and improvements have been added on a regular basis. Today, Postgres offers mature and comprehensive JSON support so that it can easily function as a document database, storing and processing JSON objects of various complexity.

Let’s explore Postgres’s JSON capabilities as we build an application for a pizza chain with hundreds of locations nationwide. We’ll learn how to store customer order details in JSON format and then work with this data while fulfilling orders and analyzing customers’ pizza preferences.

5.1 Storing JSON data

Imagine that we are part of the team building the application for the pizza chain. When a customer places a pizza order via the mobile or web frontend, our application backend receives the order details in JSON format. A sample order looks as follows:

{   
  "size": "medium",   
  "type": "margherita",  
  "crust": "regular",   
  "sauce": "marinara",  
  "toppings": {  
    "cheese": [ 
      {"mozzarella": "regular"}  
    ],  
    "veggies": [   
      {"tomato": "light"}   
    ]    
  }         
}

5.2 Loading the pizza order dataset

5.3 JSON in Postgres: Striking the balance

5 Postgres and JSON

This chapter covers

5.1 Storing JSON data

5.2 Loading the pizza order dataset

5.3 JSON in Postgres: Striking the balance

5.4 Querying JSON data

5.4.1 Extracting fields with the -> and ->> operators

5.4.2 Using the ? operator to check for the presence of a key

5.4.3 Comparing objects with the @> operator

5.4.4 Using JSON path expressions

5.5 Modifying JSON data

5.6 Indexing JSON data

5.6.1 Using an expression index with a B-tree

5.6.2 Using GIN indexes

5.7 Summary