Working with JSON and JSONB

Sometimes your data structure just doesn't fit into the usual rows and numbers. For example, a user might have a list of hobbies, random profile settings, or nested order parameters. Creating separate tables for all that is a pain. That's where JSON comes to the rescue.

PostgreSQL supports two formats for working with this kind of data: JSON and JSONB. Both let you store structured data inside a single column, but there are some important differences between them.

Let's break down how this works, when to use which format, and what cool stuff you can do with them.

What is JSON

JSON (JavaScript Object Notation) is a text-based data exchange format that was created to make representing structured data easy. It's a format every developer working with web apps knows well, and you can describe it as "pretty human-readable" and "easy for computers to parse." In PostgreSQL, this format is used for storing and handling structured data.

Here's an example of a JSON object:

{
  "name": "Alex Lin",
  "age": 25,
  "skills": ["SQL", "PostgreSQL", "JavaScript"],
  "address": {
    "city": "Berlin",
    "postal_code": "10115"
  }
}

Note: JSON is just text, but text with rules. For example, key names are always wrapped in quotes.

JSONB: Binary JSON

JSONB is "binary JSON," which is also supported by PostgreSQL. Unlike JSON, JSONB can be indexed and is optimized for fast searching and updates. The main difference between JSON and JSONB in PostgreSQL is how they're stored:

• JSON is stored as a text string, exactly as you passed the data in.
• JSONB converts the data into a binary format, which is more efficient for most operations.

JSONB gives you features like filtering, indexing, and comparing complex nested structures.

Main Advantages of JSONB

Why pick JSONB over JSON? Here are a few reasons:

1. Faster searching and filtering

JSONB is built for quick data retrieval. For example, if you have a big array of objects, JSONB lets you find the right element fast without scanning everything.

2. Indexing support

With indexing, you can search by keys and values inside JSONB, making your queries lightning fast. Treating JSON as plain text (in JSON format) doesn't let you index it.

3. Easy handling of nested data

JSONB is awesome for working with nested structures. You don't have to blow up your schema with tons of tables for hierarchical data—everything can be packed neatly.

When to Use JSON and When to Use JSONB

• JSON is good if you want to save data "as is" in text form. For example, if you care about the exact way the data is written or you want minimal processing.
• JSONB is your friend if you plan to actively query, filter, and modify the data, or if you need indexing.

Examples of JSON Objects

Let's check out a few examples of JSON objects to see what their structure can look like.

Simple JSON object.

Key-value structure:

{
  "name": "Ekaterina",
  "age": 29
}

Arrays in JSON

JSON supports arrays:

{
  "skills": ["Python", "SQL", "Data Analysis"]
}

Nested Objects

JSON lets you build layouts for storing complex data:

{
  "name": "Andrey",
  "contacts": {
    "email": "andrey@example.com",
    "phone": "+79012345678"
  }
}

Combining Arrays and Objects

You can combine arrays and objects:

{
  "team": [
    {
      "name": "Elena",
      "role": "manager"
    },
    {
      "name": "Pavel",
      "role": "developer"
    }
  ]
}

JSON and PostgreSQL

PostgreSQL supports two separate data types for working with JSON:

• JSON: text format.
• JSONB: binary format.

Creating a Table with JSON and JSONB Columns

Let's see how you can use JSON/JSONB in PostgreSQL tables. For example, let's create a table to store info about company employees:

-- Creating a table with JSON and JSONB columns
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    details JSON,    -- text JSON
    profile JSONB    -- binary JSON
);

At first glance, it looks like there's no difference between these columns. But that's not true: JSON is perfect for storing data in its original form, while JSONB is better for filtering and searching.

-- Inserting data
INSERT INTO employees (name, details, profile)
VALUES
('Alex Lin', '{"age": 30, "city": "Tallinn"}', '{"skills": ["SQL", "PostgreSQL"], "hobby": "football"}'),
('Maya Novak', '{"age": 25, "city": "Riga"}', '{"skills": ["Python", "Machine Learning"], "hobby": "reading"}');

Extracting JSONB Data

You can extract data from JSONB using special functions, which we'll cover in the next lecture. For example, to find out about employees' skills:

-- Extracting skills
SELECT name, profile->'skills' AS skills
FROM employees;

Result:

Using JSON in Real Life

JSON (and JSONB) is used all over the place in real-world apps. Here are a few examples:

1. APIs and microservices. JSON is the standard format for data transfer in RESTful APIs. PostgreSQL supports it at the storage and processing level.
2. Data integration. If your database gets data from different systems, working with JSONB is way more convenient.
3. Handling complex structures. For example, JSONB is great for storing survey data, user settings, or company metadata.