PostgreSQL® JSONB Cheatsheet: Complete & Fast Lookup Guide
Tags: postgres, jsonb, cheatsheet, json
Published: 2023-04-07 13:58:00

---
title: PostgreSQL® JSONB Cheatsheet: Complete & Fast Lookup Guide
published: true
date: 2023-04-07 13:58:00 UTC
tags: postgresql, jsonb, cheatsheet, json
canonical_url: https://aiven.io/developer/postgresql-jsonb-cheatsheet#1
---

How do I extract a JSON item? What about tabulating the content? Can I build a set of rows from an array?

If you need a FREE PostgreSQL database? 
🦀 Check [Aiven's FREE plans](https://go.aiven.io/francesco-signup)! 🦀
⚡️ Need to optimize your SQL query with AI? ⚡️
🐧 Check  <a href="https://go.aiven.io/ft-ai-db-optimizer">Aiven AI Database Optimizer</a>! Powered by EverSQL 🐧

Dealing with JSON datasets in PostgreSQL® is becoming more and more common, and we can see the mix PostgreSQL + JSON appearing frequently in [StackOverflow](https://stackoverflow.com/questions/tagged/postgresql). Knowing all the [PostgreSQL JSON functions and operators](https://www.postgresql.org/docs/current/functions-json.html) by heart might make you famous at a PostgreSQL trivia night, but is not an essential skill to have. 

We're therefore happy to release the [PostgreSQL® JSONB Cheatsheet](https://cdn.sanity.io/files/sczeoy4w/production/065818a8cd88cd048137368e5710ec35c0361185.pdf), a complete and fast lookup guide to all the PostgreSQL JSONB functions and operators. The cheatsheet provides a set of consistent examples of all the most common JSONB functions and operators.

> **Note:** PostgreSQL actually has two JSON datatypes, `json` and `jsonb`. The first validates that the content is in JSON format and stores it as a string, the second is a binary representation optimised for faster processing and better indexing. You can read more on [StackOverflow](https://stackoverflow.com/questions/22654170/explanation-of-jsonb-introduced-by-postgresql). If you need the JSON functions instead, they're really similar but without the `b` ending.

![Image of the cheatsheet](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/776f30385g4tsyv7ym8g.png)


The image above is only for display purposes, download the [high resolution copy](https://cdn.sanity.io/files/sczeoy4w/production/065818a8cd88cd048137368e5710ec35c0361185.pdf) with full copy/paste features.


## Get a PostgreSQL database

The operators and functions will work with any PostgreSQL database, all the docs have been checked from 9.5+, include both the JSON and JSONB functions.

In this walkthrough, we're going to use an Aiven for PostgreSQL database: we can create one, using the $300 and 30 days trial period, by accessing the [Aiven Console](https://console.aiven.io/), clicking on **Create Service** and then filling in the following details:

* *Service type*: the choice is **PostgreSQL®**, any version is ok. We can select the newly released Version 15.
* *Cloud provider and region*: we can deploy our PostgreSQL wherever we want. Feel free to select your favourite cloud provider and the cloud region closer to where you are, this will help minimise the latency. Please note that you don't need to create a cloud account with the chosen provider, Aiven will handle everything for you.
* *Service plan*: various options are available, from hobbyist to premium plans, covering all the scenarios from test to highly available production system. For our testing purposes the `Hobbyist` plan would be enough.
* *Service name*: used to identify the service uniquely, we can either accept the default or write a more accurate name. Let's go for `pg-jsonb-cheatsheet` so we can immediately understand why we created the PostgreSQL instance.

After clicking on **Create service**, we need just a couple minutes of patience for the service to come up.

Once the service status is **Running** we can use our favourite tool to connect. If the chosen tool is `psql`, we can get the necessary command line, complete with connection details, by clicking on **Quick connect**. Otherwise, copy the Service URI, as highlighted, and use the tool of your choice.

![Service overview page with Service URI highlighted](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/as9evj5yryiuwgl0zig0.png)

## Insert the data

The cheatsheet includes a small JSON dataset (at the top left) that allows us to explore the functions. To start we need to execute that code to create a table called `test` containing a single record, with a serial number in `id` and a JSONB payload in the `json_data` column. The same JSON is shown below, so you can easily copy/paste it.

```sql
create table test(id serial, json_data jsonb);

insert into test(json_data) values (
'{
    "id": 778,
    "shop": "Luigis Pizza",
    "name": "Edward Olson",
    "phoneNumbers":
        ["(935)503-3765x4154","(935)12345"],
    "address": "Unit 9398 Box 2056\nDPO AP 24022",
    "image": null,
    "pizzas": [
        {
            "pizzaName": "Salami",
            "additionalToppings": ["🥓", "🌶️"]
        },
        {
            "pizzaName": "Margherita",
            "additionalToppings": ["🍌", "🌶️", "🍍"]
        }
    ]
}');
```

## Experience the JSONB functions

Now we're all set for success! Check out the cheatsheet, identify the problem to solve and copy/paste the relevant code. 
For example, if you're looking to understand how to remove some fields from a JSON document, head to the **Edit** section, look for the **Remove items in A** example, copy and paste the code.

```sql
select json_data
    - ARRAY['pizzas','id']
    as no_pizzas_and_id
from test;
```

The result is the original JSON document without the `pizzas` and `id` columns

```
                                                                           no_pizzas_and_id
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"name": "Edward Olson", "shop": "Luigis Pizza", "image": null, "address": "Unit 9398 Box 2056\nDPO AP 24022", "phoneNumbers": ["(935)503-3765x4154", "(935)12345"]}
(1 row)
```

## A quick lookup for JSONB functions

Being a good SQL citizen doesn't mean needing to know all the PostgreSQL JSONB functions by heart. The PostgreSQL JSONB cheatsheet is a one pager that can help you quickly find the function you're looking for to solve your semi structured data problem.

Some more links you might be interested to:

* [PostgreSQL JSON functions and operators](https://www.postgresql.org/docs/current/functions-json.html)
* [A more detailed version of PostgreSQL JSONB examples](https://ftisiot.net/postgresqljson/main/)
* An example of applying [PostgreSQL JSON functions to find a restaurant in india](https://aiven.io/blog/pg-json-indian-restaurants)