
How to Use JSONB in PostgreSQL with DbSchema
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:
|
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
|
Step 2: Insert Data
|
Tip: In some SQL editors, like DbSchema, you may need to run the
CREATE TABLE
andINSERT
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:
|
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 ->
|
This returns the values still wrapped in JSON format:
raw_json | population_json |
---|---|
“Paris” | 2148000 |
“Berlin” | 3769000 |
Example Using ->>
|
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:
|
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.
|
|
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:
|
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.