How to Use JSONB in PostgreSQL with DbSchema

---

Postgresql • 07-Jul-2025

by Roxana Haidiner

---

What Is JSON?

JSON (JavaScript Object Notation) is a lightweight format for storing and exchanging data. It looks like structured text using key-value pairs, and it’s easy for both humans and machines to read.

Example:

{
  "name": "Paris",
  "population": 2148000,
  "area": 105.4
}

PostgreSQL supports two ways to store structured data in a column: JSON and JSONB.

• JSON stores the data as plain text. It’s readable but slower for queries.
• JSONB stores the same data in binary format. It’s faster, allows indexing, and supports advanced filtering.

The main advantage of using JSONB is that you can query individual fields inside the JSON structure using SQL. You can also sort, filter, and index values without splitting your data into multiple tables.

For example, instead of creating a separate table for cities, you can store all cities inside a cities field as JSONB, then query each city just like you would with a regular table.

In this tutorial, you’ll learn how to:

• Create a table that uses a JSONB column
• Insert nested data using JSON format
• Query values from JSONB fields using PostgreSQL functions

JSON vs JSONB: Which One Should You Use?

Use JSON if:

• You only need to store and retrieve the raw JSON
• You don’t plan to query or filter inside the JSON content

Use JSONB if:

• You want to filter or sort based on values inside the JSON
• You want to index fields inside the JSON for performance
• You want to avoid normalizing the structure into many separate tables

In most real-world cases, JSONB is the better choice, especially when your data is dynamic, hierarchical, or semi-structured.

A Real-World Example: Countries and Cities

Let’s use an example that’s easy to understand - storing a list of countries, each with a list of cities. Each city has a name, population, and area.

This kind of data structure is a perfect candidate for JSONB, because you don’t want to create a separate table for cities if you’re only displaying them with their parent country.

Step 1: Create the Table

CREATE TABLE countries (
  id SERIAL PRIMARY KEY,
  name TEXT,
  cities JSONB
);

Step 2: Insert Data

INSERT INTO countries (name, cities) VALUES
('France', '[
  {"name": "Paris", "population": 2148000, "area": 105.4},
  {"name": "Lyon", "population": 513000, "area": 47.9},
  {"name": "Marseille", "population": 861000, "area": 240.6}
]'),
('Germany', '[
  {"name": "Berlin", "population": 3769000, "area": 891.8},
  {"name": "Munich", "population": 1472000, "area": 310.7},
  {"name": "Hamburg", "population": 1841000, "area": 755.2}
]');

Tip: In some SQL editors, like DbSchema, you may need to run the CREATE TABLE and INSERT statements together in the same execution. Otherwise, the table might not be recognized immediately when you run the insert separately.

Each country now holds an array of city objects, stored in the cities JSONB column.

Step 3: Query Cities from JSONB

You can extract and work with each city using PostgreSQL’s jsonb_array_elements function:

SELECT
  c.name AS country,
  city->>'name' AS city,
  (city->>'population')::INT AS population,
  (city->>'area')::NUMERIC AS area_km2
FROM countries c,
     jsonb_array_elements(c.cities) AS city;

This query unpacks the JSON array and returns each city as a separate row:

Understanding the Difference Between -> and ->>

PostgreSQL provides two operators for accessing data inside JSONB fields:

Operator	Returns	Type	Use Case
->	JSON object	jsonb	Use when you want to extract a nested JSON value and keep it as JSON
->>	Text (string)	text	Use when you want the actual value as plain text, for display or filtering

---

Example Using ->

SELECT
  city->'name' AS raw_json,
  city->'population' AS population_json
FROM company.countries c,
     jsonb_array_elements(c.cities) AS city;

This returns the values still wrapped in JSON format:

raw_json	population_json
“Paris”	2148000
“Berlin”	3769000

Example Using ->>

SELECT
  city->>'name' AS city_name,
  city->>'population' AS population_text
FROM company.countries c,
     jsonb_array_elements(c.cities) AS city;

This returns the values as plain text:

city_name	population_text
Paris	2148000
Berlin	3769000

Indexes on JSONB

If you plan to query JSONB fields frequently, you can improve performance by creating expression indexes:

CREATE INDEX idx_city_population ON countries ((city->>'population'));

This lets PostgreSQL quickly search or filter based on values inside the JSONB column.

Querying Nested Arrays Inside JSONB

Let’s look at a more complex structure - a product with multiple variants, and each variant includes multiple options.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  specs JSONB
);

INSERT INTO products (name, specs) VALUES
('Laptop', '{
  "variants": [
    {
      "color": "Silver",
      "options": [
        {"ram": "16GB", "storage": "512GB SSD"},
        {"ram": "32GB", "storage": "1TB SSD"}
      ]
    },
    {
      "color": "Black",
      "options": [
        {"ram": "8GB", "storage": "256GB SSD"},
        {"ram": "16GB", "storage": "512GB SSD"}
      ]
    }
  ]
}');

This structure includes two arrays:

• variants (an array of product color versions)

• options inside each variant (RAM and storage combinations)

To extract each option from every variant, you can unwrap both levels like this, using LATERAL JOIN, which is often preferred in production code:

SELECT
  p.id,
  p.name,
  variant->>'color' AS color,
  option->>'ram' AS ram,
  option->>'storage' AS storage
FROM products p
INNER JOIN LATERAL jsonb_array_elements(p.specs->'variants') AS variant ON true
INNER JOIN LATERAL jsonb_array_elements(variant->'options') AS option ON true;

Why use LATERAL? It makes dependencies between rows clearer and avoids confusion when nesting JSON operations.

Key Points to Remember

• Use JSONB when your data is flexible or nested and you don’t want to create extra tables for every detail.
• Use -> if you want to keep the result as JSON, especially when you’re going to extract more from it.
• Use ->> when you just need the plain text value - great for filtering or showing results.
• You can filter, sort, and even create indexes on values inside a JSONB column.
• To work with arrays inside JSONB, use jsonb_array_elements() - it will turn each item into a row you can query.
• If your JSON has arrays inside arrays (like options inside product variants), use LATERAL JOIN to unpack them step by step.

Tools like DbSchema make it easy to visualize your schema, run SQL queries, and work with JSONB fields through a graphical interface.
You can download DbSchema for free and try it with your PostgreSQL database.