PostgreSQL JSON and JSONB Data Handling

1. What You'll Learn

Understand the difference between JSON and JSONB, and JSONB's advantages
Use JSON operators to extract and filter data
Use JSONB functions to query, modify, and generate JSON data
Create GIN indexes to speed up JSONB queries
Use JSONPATH (SQL/JSON standard) for complex queries
Hybrid design patterns combining JSONB with relational data

2. The Story

Bob is a backend engineer at a SaaS platform. The platform needs to store user configurations and product attributes, but these fields differ for every customer:

Customer A's user config has theme, language, notifications
Customer B's user config has timezone, currency, dashboard_layout
Product attributes vary even more: clothes have size/color, electronics have warranty/voltage

With a traditional relational model, every new field would require an ALTER TABLE. Bob chooses to store these dynamic fields in a single table using JSONB—flexible and efficient.

3. Concept: JSON vs JSONB

(1) Two JSON Types Compared

Dimension	JSON	JSONB
Storage	Stored as text, preserved verbatim	Stored as binary, parsed then stored
Write speed	Faster (no parsing)	Slower (needs parsing and conversion)
Query speed	Slower (parsed on every query)	Very fast (already parsed into tree)
Index support	No native index	Supports GIN index
Whitespace/order	Preserves original whitespace and key order	Not preserved; keys sorted alphabetically
Duplicate keys	All duplicate keys retained	Only the last value kept
Recommended for	Storage only, no querying	The vast majority of cases

▶ Example: JSON Preserves Whitespace, JSONB Does Not

SQL

SELECT '{"name": "Alice", "age": 30}'::json;
-- {"name": "Alice", "age": 30}

SELECT '{"name": "Alice", "age": 30}'::jsonb;
-- {"age": 30, "name": "Alice"}

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Create a Table with a JSONB Column

SQL

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username TEXT NOT NULL,
  profile JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO users (username, profile) VALUES
  ('alice', '{"theme": "dark", "language": "en", "notifications": true}'),
  ('bob', '{"timezone": "UTC-5", "currency": "USD", "dashboard_layout": "grid"}');

TEXT

INSERT 0 1

4. Concept: JSON Operators

(1) Basic Extraction Operators

Operator	Right operand	Return type	Description	Example
`->`	int	JSON/JSONB	Array element by index	`'[1,2,3]'::jsonb -> 1` → `2`
`->`	text	JSON/JSONB	Object value by key	`'{"a":1}'::jsonb -> 'a'` → `1`
`->>`	int	text	Array element by index (text)	`'[1,2,3]'::jsonb ->> 1` → `"2"`
`->>`	text	text	Object value by key (text)	`'{"a":1}'::jsonb ->> 'a'` → `"1"`

▶ Example: Extract Nested Fields

SQL

SELECT profile -> 'theme' AS theme_json,
       profile ->> 'theme' AS theme_text
FROM users
WHERE username = 'alice';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Path Extraction Operators

Operator	Right operand	Return type	Description
`#>`	text[]	JSON/JSONB	Value by path (JSON format)
`#>>`	text[]	text	Value by path (text format)

▶ Example: Path Extraction

SQL

SELECT profile #> '{address,city}' AS city_json,
       profile #>> '{address,city}' AS city_text
FROM users
WHERE profile ? 'address';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) Containment and Existence Operators (JSONB Only)

Operator	Description	Example
`@>`	Whether left contains right	`'{"a":1,"b":2}'::jsonb @> '{"a":1}'` → true
`<@`	Whether left is contained by right	`'{"a":1}'::jsonb <@ '{"a":1,"b":2}'` → true
`?`	Whether key exists	`'{"a":1}'::jsonb ? 'a'` → true
`?	`	Whether any key exists
`?&`	Whether all keys exist	`'{"a":1}'::jsonb ?& array['a','b']` → false

▶ Example: Containment Query—Find All Users with the Dark Theme Enabled

SQL

SELECT username, profile
FROM users
WHERE profile @> '{"theme": "dark"}';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Key-Existence Query—Find Users Who Set a Timezone

SQL

SELECT username, profile ->> 'timezone' AS tz
FROM users
WHERE profile ? 'timezone';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Multi-Key Query—Find Users Who Set Timezone or Currency

SQL

SELECT username
FROM users
WHERE profile ?| array['timezone', 'currency'];

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

5. Concept: JSONB Functions

(1) Query and Extraction Functions

Function	Return type	Description
`jsonb_path_query(data, path)`	setof jsonb	Query by JSONPATH, return all matches
`jsonb_array_elements(data)`	setof jsonb	Expand array into row set
`jsonb_each(data)`	setof (key, value)	Expand object into key-value pairs
`jsonb_object_keys(data)`	setof text	Return all top-level keys
`jsonb_typeof(data)`	text	Return the type of a JSON value

▶ Example: Expand a JSON Array into Rows

SQL

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  attributes JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO products (name, attributes) VALUES
  ('T-Shirt', '{"colors": ["red", "blue", "green"], "sizes": ["S", "M", "L"]}'),
  ('Laptop', '{"colors": ["silver", "black"], "warranty_years": 2}');

SELECT product_id, name,
       jsonb_array_elements_text(attributes -> 'colors') AS color
FROM products;

TEXT

INSERT 0 1

▶ Example: Expand an Object into Key-Value Pairs

SQL

SELECT username,
       (jsonb_each(profile)).key AS config_key,
       (jsonb_each(profile)).value AS config_value
FROM users;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Modification Functions

Function	Description
`jsonb_set(target, path, new_value)`	Set the value at a specified path
`jsonb_insert(target, path, new_value [, before])`	Insert a new value at a specified path
`target - key`	Delete a top-level key
`target - path_array`	Delete a specified path
`jsonb_pretty(data)`	Pretty-print output

▶ Example: Modify User Config

SQL

-- Add or update a field
UPDATE users
SET profile = jsonb_set(profile, '{language}', '"zh"')
WHERE username = 'alice';

-- Add a nested field
UPDATE users
SET profile = jsonb_set(profile, '{address,city}', '"New York"')
WHERE username = 'alice';

-- Delete a field
UPDATE users
SET profile = profile - 'notifications'
WHERE username = 'alice';

TEXT

-- SQL statement executed successfully

▶ Example: Append an Element to a JSON Array

SQL

-- Append to end (path must point to existing array, insert after last element)
UPDATE products
SET attributes = jsonb_set(
  attributes, '{colors}',
  (attributes -> 'colors') || '"yellow"'
)
WHERE name = 'T-Shirt';

TEXT

INSERT 0 1

▶ Example: Pretty-Print JSON

SQL

SELECT jsonb_pretty(profile) FROM users WHERE username = 'alice';

TEXT

{
    "theme": "dark",
    "language": "zh",
    "address": {
        "city": "New York"
    }
}

6. Concept: JSONB Indexes

(1) GIN Index Speeds Up JSONB Queries

GIN index type	Supported operators	Description
`jsonb_ops` (default)	`@>` `?` `?	?&`
`jsonb_path_ops`	`@>`	Smaller, faster index—supports containment queries only

▶ Example: Create a GIN Index

SQL

-- Default GIN index (supports @>, ?, ?|, ?&)
CREATE INDEX idx_users_profile ON users USING gin (profile);

-- Path ops GIN index (smaller, faster for @> only)
CREATE INDEX idx_users_profile_path ON users USING gin (profile jsonb_path_ops);

TEXT

CREATE TABLE

▶ Example: Compare Query Performance With and Without an Index

SQL

-- Without index: sequential scan
EXPLAIN ANALYZE
SELECT * FROM users WHERE profile @> '{"theme": "dark"}';

-- After creating GIN index: bitmap index scan
CREATE INDEX idx_users_profile ON users USING gin (profile);
EXPLAIN ANALYZE
SELECT * FROM users WHERE profile @> '{"theme": "dark"}';

TEXT

CREATE TABLE

Query method	Uses GIN index?	Description
`profile @> '{"theme":"dark"}'`	Yes	Containment query—GIN's best case
`profile ->> 'theme' = 'dark'`	No	Extraction then compare—needs a B-tree expression index
`profile ? 'theme'`	Yes	Key-existence query

▶ Example: B-tree Expression Index Speeds Up Extraction Queries

SQL

-- For queries using ->> operator
CREATE INDEX idx_users_theme ON users ((profile ->> 'theme'));
SELECT * FROM users WHERE profile ->> 'theme' = 'dark'; -- Uses index

TEXT

CREATE TABLE

7. Concept: JSONPATH (SQL/JSON Standard)

(1) JSONPATH Syntax

PostgreSQL 12+ supports the SQL/JSON standard JSONPATH, similar to XPath, for complex JSON queries.

Syntax	Description	Example
`$.key`	Root object key	`$.theme`
`$.array[*]`	Iterate over array	`$.colors[*]`
`$.nested.key`	Nested access	`$.address.city`
`? (condition)`	Filter	`$.items[*] ? (@.price > 100)`
`@`	Current element	`@.name`

▶ Example: Query with jsonb_path_query

SQL

SELECT jsonb_path_query(profile, '$.theme') AS theme
FROM users
WHERE username = 'alice';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: JSONPATH Query with Filter Condition

SQL

-- Products with warranty > 1 year
SELECT name,
       jsonb_path_query(attributes, '$.warranty_years') AS warranty
FROM products
WHERE jsonb_path_exists(attributes, '$.warranty_years ? (@ > 1)');

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Iterate Over Array Elements

SQL

SELECT name,
       jsonb_path_query(attributes, '$.colors[*]') AS color
FROM products;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

Function	Return type	Description
`jsonb_path_query(data, path)`	setof jsonb	Return all matches
`jsonb_path_query_array(data, path)`	jsonb	Return matches as JSON array
`jsonb_path_query_first(data, path)`	jsonb	Return first match
`jsonb_path_exists(data, path)`	boolean	Whether any match exists

8. Concept: Hybrid Design with JSONB and Relational Data

(1) When to Use JSONB, When to Use Relational Columns

Scenario	Recommended	Reason
Fields queried/sorted/joined frequently	Relational column + B-tree index	Best performance
Fields with fixed structure that take part in business logic	Relational column	Type-safe, full constraints
Fields whose structure varies by customer	JSONB + GIN index	Flexible, no ALTER TABLE needed
Occasional supplementary info queries	JSONB	Doesn't pollute the main table structure
Dynamic fields needing precise type constraints	JSONB + CHECK constraint	Balances flexibility and safety

▶ Example: Hybrid Design—Product Table

SQL

CREATE TABLE products_v2 (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,              -- Fixed column, indexed
  price NUMERIC(10,2) NOT NULL,        -- Fixed column, indexed
  stock INT NOT NULL DEFAULT 0,         -- Fixed column, indexed
  attributes JSONB NOT NULL DEFAULT '{}', -- Dynamic attributes
  metadata JSONB DEFAULT '{}'          -- Rarely queried meta info
);

CREATE INDEX idx_products_category ON products_v2 (category);
CREATE INDEX idx_products_attrs ON products_v2 USING gin (attributes jsonb_path_ops);

TEXT

CREATE TABLE

▶ Example: JSONB CHECK Constraint Ensures Data Quality

SQL

ALTER TABLE products_v2
ADD CONSTRAINT chk_attributes_schema
CHECK (
  jsonb_typeof(attributes -> 'colors') = 'array'
  AND attributes ? 'colors'
);

TEXT

-- SQL statement executed successfully

▶ Example: JSONB Join Query

SQL

-- Find orders where product has specific attribute
SELECT o.order_id, o.customer_id, p.name
FROM orders o
JOIN products_v2 p ON o.product_id = p.product_id
WHERE p.attributes @> '{"warranty_years": 2}';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

9. JSON Storage and Query Flow

flowchart TD
    A[JSON Text Input] --> B{Target Type?}
    B -->|json| C[Store as-is<br/>No parsing overhead]
    B -->|jsonb| D[Parse & Convert<br/>to binary tree]
    D --> E[Store as JSONB<br/>Keys sorted, no whitespace]
    E --> F{Query Type?}
    F -->|@> contains| G[GIN Index Scan<br/>Fast path]
    F -->|->> extract + compare| H[B-tree Expr Index<br/>or Seq Scan]
    F -->|jsonpath| I[JSONPATH Engine<br/>PG 12+]
    G --> J[Return Results]
    H --> J
    I --> J
    C --> K[Parse on every query<br/>Slow, no index]
    K --> J

10. Hands-on: SaaS Platform User Config and Product Attribute System

Bob needs to implement a complete SaaS platform data storage solution, supporting flexible user configuration and product attribute management.

SQL

-- Step 1: Create core tables with JSONB
CREATE TABLE saas_users (
  user_id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  plan TEXT NOT NULL DEFAULT 'free',
  config JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMP DEFAULT now()
);

CREATE TABLE saas_products (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL,
  specs JSONB NOT NULL DEFAULT '{}',
  tags JSONB NOT NULL DEFAULT '[]'
);

-- Step 2: Insert sample data
INSERT INTO saas_users (email, name, plan, config) VALUES
  ('alice@corp.com', 'Alice', 'pro',
   '{"theme":"dark","language":"en","notifications":{"email":true,"sms":false},"sidebar":["dashboard","reports"]}'),
  ('bob@corp.com', 'Bob', 'enterprise',
   '{"theme":"light","language":"zh","notifications":{"email":true,"sms":true},"sidebar":["dashboard","admin","billing"]}');

INSERT INTO saas_products (name, category, price, specs, tags) VALUES
  ('Pro Widget', 'widget', 49.99,
   '{"weight_kg":0.5,"colors":["red","blue"],"warranty_years":3}',
   '["popular","new"]'),
  ('Mega Gadget', 'gadget', 199.99,
   '{"weight_kg":2.0,"colors":["silver","black"],"voltage":"220V"}',
   '["premium","bestseller"]');

-- Step 3: Create indexes
CREATE INDEX idx_saas_users_config ON saas_users USING gin (config);
CREATE INDEX idx_saas_products_specs ON saas_products USING gin (specs jsonb_path_ops);
CREATE INDEX idx_saas_products_tags ON saas_products USING gin (tags);
CREATE INDEX idx_saas_products_category ON saas_products (category);

-- Step 4: Query examples
-- Find users with email notifications enabled
SELECT name, config ->> 'theme' AS theme
FROM saas_users
WHERE config @> '{"notifications":{"email":true}}';

-- Find products available in red
SELECT name, price
FROM saas_products
WHERE specs -> 'colors' @> '["red"]';

-- Find products with specific tags
SELECT name
FROM saas_products
WHERE tags @> '["premium"]';

-- Update user config (add new field)
UPDATE saas_users
SET config = jsonb_set(config, '{timezone}', '"America/New_York"')
WHERE email = 'alice@corp.com';

-- Remove a config field
UPDATE saas_users
SET config = config - 'language'
WHERE email = 'bob@corp.com';

-- Expand product tags for analytics
SELECT name, jsonb_array_elements_text(tags) AS tag
FROM saas_products;

-- Pretty print user config
SELECT name, jsonb_pretty(config) FROM saas_users WHERE plan = 'pro';

❓ FAQ

Q Which should I choose, JSON or JSONB?

A For the vast majority of cases, choose JSONB. JSONB queries faster, supports indexing, and has richer operators. Use JSON only when you need to preserve the original text format (whitespace, key order, duplicate keys), or for storage-only with no querying.

Q Can JSONB replace relational tables?

A Not entirely. Fields queried/sorted/joined frequently should use relational columns. JSONB suits structure-variable supplementary data; a hybrid design is the best practice.

Q What's the difference between jsonb_set and jsonb_insert?

A jsonb_set replaces the value at an existing path, creating it if the path doesn't exist. jsonb_insert inserts a new element at a specified position in an array (the before parameter controls front/back), and does not replace if the key already exists.

Q How do I choose between a GIN index and a B-tree expression index?

A Use GIN for containment queries (@>), and a B-tree expression index for equality queries (->> 'key' = 'value'). The two can coexist, covering different query patterns.

Q How do I choose between JSONPATH and traditional operators?

A Operators are more concise for simple queries; JSONPATH is more powerful for complex nested queries and filter conditions. JSONPATH is the SQL/JSON standard, so it's more portable.

Q Can a JSONB field have a CHECK constraint?

A Yes. Use jsonb_typeof(), the ? operator, etc. in a CHECK constraint to validate the JSONB structure—for example CHECK (jsonb_typeof(attributes -> 'colors') = 'array').

Q Do large numbers of JSONB updates cause performance problems?

A Yes. A JSONB update replaces the entire value (MVCC creates a new version), and frequently updating large JSONB values produces many dead tuples. It's recommended to split frequently-updated fields into relational columns, and let JSONB store low-update-frequency supplementary data.

📖 Summary

JSONB is binary-stored JSON—queries faster, supports indexes—recommended as the default choice
-> returns JSON type, ->> returns text type, #>/#>> extract by path
The containment operator @> combined with a GIN index is the best combination for JSONB queries
jsonb_set/jsonb_insert/- handle add/modify/delete; jsonb_array_elements/jsonb_each handle expansion
JSONPATH (PG 12+) provides SQL/JSON-standard complex query capabilities
Hybrid design: fixed fields use relational columns, dynamic fields use JSONB, with CHECK constraints to ensure quality

📝 Exercises

⭐ Create an app_settings table with app_name (TEXT) and settings (JSONB) columns; insert two rows, then use ->> to query the value of a config item.
⭐⭐ Create a GIN index on the saas_products table; write a query that finds the names of all products whose specs have warranty_years > 2, and use jsonb_pretty to pretty-print the specs.
⭐⭐⭐ Design a JSONB hybrid scheme for an orders table: fixed columns for order_id/customer_id/total_amount/status/created_at, and a JSONB column extra storing coupon info (coupon_code/discount_percent) and delivery notes (delivery_notes). Write: insert an order with extra, use @> to find orders that used a specific coupon, and use jsonb_set to append gift_wrap: true to an existing order.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL JSON and JSONB Data Handling

1. What You'll Learn

2. The Story

3. Concept: JSON vs JSONB

(1) Two JSON Types Compared

▶ Example: JSON Preserves Whitespace, JSONB Does Not

▶ Example: Create a Table with a JSONB Column

4. Concept: JSON Operators

(1) Basic Extraction Operators

▶ Example: Extract Nested Fields

(2) Path Extraction Operators

▶ Example: Path Extraction

(3) Containment and Existence Operators (JSONB Only)

▶ Example: Containment Query—Find All Users with the Dark Theme Enabled

▶ Example: Key-Existence Query—Find Users Who Set a Timezone

▶ Example: Multi-Key Query—Find Users Who Set Timezone or Currency

5. Concept: JSONB Functions

(1) Query and Extraction Functions

▶ Example: Expand a JSON Array into Rows

▶ Example: Expand an Object into Key-Value Pairs

(2) Modification Functions

▶ Example: Modify User Config

▶ Example: Append an Element to a JSON Array

▶ Example: Pretty-Print JSON

6. Concept: JSONB Indexes

(1) GIN Index Speeds Up JSONB Queries

▶ Example: Create a GIN Index

▶ Example: Compare Query Performance With and Without an Index

▶ Example: B-tree Expression Index Speeds Up Extraction Queries

7. Concept: JSONPATH (SQL/JSON Standard)

(1) JSONPATH Syntax

▶ Example: Query with jsonb_path_query

▶ Example: JSONPATH Query with Filter Condition

▶ Example: Iterate Over Array Elements

8. Concept: Hybrid Design with JSONB and Relational Data

(1) When to Use JSONB, When to Use Relational Columns

▶ Example: Hybrid Design—Product Table

▶ Example: JSONB CHECK Constraint Ensures Data Quality

▶ Example: JSONB Join Query

9. JSON Storage and Query Flow

10. Hands-on: SaaS Platform User Config and Product Attribute System

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好