YSQL (YugabyteDB Structured Query Language) supports two dedicated data types to store JSON (JavaScript Object Notation) data.
JSON
- Stores JSON data as text.
- Performs operations with text-based processing.
- Does not enforce strict JSON validation.
JSONB (Recommended)
- Stores JSON data in a binary format.
- Allows for efficient indexing and searching.
- Enforces strict JSON validation.
- Provides better query performance than JSON.
Example:
yugabyte=# SELECT split_part(version(), '-', 3) "YB Version";
YB Version
------------
2024.2.2.0
(1 row)
CREATE TABLE roller_coasters (
id SERIAL PRIMARY KEY,
name TEXT,
stats JSONB
);
INSERT INTO roller_coasters (name, stats) VALUES
('Fury 325', '{"height_ft": 325, "speed_mph": 95, "inversions": 0, "manufacturer": {"name": "Bolliger & Mabillard", "country": "Switzerland"}}'),
('Kingda Ka', '{"height_ft": 456, "speed_mph": 128, "inversions": 0, "manufacturer": {"name": "Intamin", "country": "Switzerland"}}'),
('Steel Vengeance', '{"height_ft": 205, "speed_mph": 74, "inversions": 4, "manufacturer": {"name": "Rocky Mountain Construction", "country": "USA"}}'),
('Top Thrill 2', '{"height_ft": 420, "speed_mph": 120, "inversions": 0, "manufacturer": {"name": "Zamperla", "country": "Italy"}}'),
('The Voyage', '{"height_ft": 173, "speed_mph": 67, "inversions": 0, "manufacturer": {"name": "The Gravity Group", "country": "USA"}}'),
('Millennium Force', '{"height_ft": 310, "speed_mph": 93, "inversions": 0, "manufacturer": {"name": "Intamin", "country": "Switzerland"}}'),
('Taron', '{"height_ft": 98, "speed_mph": 72, "inversions": 0, "manufacturer": {"name": "Intamin", "country": "Switzerland"}}'),
('Lightning Rod', '{"height_ft": 165, "speed_mph": 73, "inversions": 0, "manufacturer": {"name": "Rocky Mountain Construction", "country": "USA"}}');
This dataset includes a mix of steel and wooden roller coasters from various manufacturers.
To query nested fields in JSON or JSONB column, you are proably familiar with the JSON operators -> and ->>.
Query:
SELECT id, name, stats->'height_ft' AS height FROM roller_coasters ORDER BY 3 DESC LIMIT 3;
Result:
id | name | height
---+--------------+--------
2 | Kingda Ka | 456
4 | Top Thrill 2 | 420
1 | Fury 325 | 325
(3 rows)
PostgreSQL15 introduced JSON subscripting, making it easier to work with json and jsonb data. Instead of using the -> or ->> JSON operators, you can now access JSON elements with a more intuitive, array-like syntax.
Query:
SELECT id, name, stats['height_ft'] AS height FROM roller_coasters ORDER BY 3 DESC LIMIT 3;
Result:
ERROR: cannot subscript type jsonb because it is not an array
Uh oh! What happened?
Since this feature was introduced in PostgreSQL15, you’ll need a YugabyteDB version that supports it—at least YugabyteDB 2.25.0.0 (preview) or 2025.1.0.0 (stable / coming REAL soon!).
yugabyte=# SELECT split_part(version(), '-', 3) "YB Version";
YB Version
------------
2.25.0.0
(1 row)
Query:
SELECT id, name, stats['height_ft'] AS height FROM roller_coasters ORDER BY 3 DESC LIMIT 3;
Result:
id | name | height
---+--------------+--------
2 | Kingda Ka | 456
4 | Top Thrill 2 | 420
1 | Fury 325 | 325
(3 rows)
It’s working now!
Have Fun!
