5 Postgres and JSON
This chapter covers
- Storing JSON data in Postgres
- Querying JSON data using built-in operators and functions
- Updating specific fields within 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 such mature and comprehensive JSON support that it can easily function as a document database, storing and processing JSON objects of various complexity.
In this chapter, you learn how to store, query, modify, and index JSON data in Postgres. You explore all this in a developer way by working on an application for a pizza chain with hundreds of locations nationwide.
5.1 Storing JSON data
Imagine we are part of the team building an application for a 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"}
]
}
}