JSONB Field Extraction in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Literal Values, Data Types, and Type Casting, WHERE Clause and Comparison Operators
Builds toward JSONB Aggregation (jsonb_agg, json_build_object)
How do you extract a field from JSONB in SQL?
The -> and ->> operators pull values out of JSONB columns — one field at a time, one row at a time. They're what you use when a column stores semi-structured data and you need to work with the values inside it like regular SQL columns.
The difference between the two comes down to what they return. -> returns the extracted value as JSONB, preserving its structure so you can chain more extractions onto it. ->> returns the extracted value as plain text — ready for display or comparison, but all type information is gone.
SELECT name, attributes->>'color' AS color, (attributes->>'weight_kg')::numeric AS weight_kg FROM products WHERE attributes IS NOT NULL LIMIT 10
The ->>'color' operator extracts color and returns it as plain text. The (attributes->>'weight_kg')::numeric extracts weight_kg as text and casts it to a number — the cast is what lets you compare it against numeric thresholds. Each ->> gives you the terminal value as text; when the value is a number or date, you always need an explicit cast to work with it.
How do you read a nested JSONB field in PostgreSQL?
When the JSONB has nested objects (like {'address': {'city': 'London'}}), use -> to navigate into the nested level as JSONB, then ->> for the final field:
metadata -> 'address' ->> 'city' -- navigates into address, returns city as textArray elements use the same operators with integer indexes: -> 0 returns the first element as JSONB, ->> 0 returns it as text.
JSONB columns show up often in practice: an events table with a properties column storing arbitrary attributes, a products table with a metadata field for flexible specs, or a log table storing raw API payloads. Each row can have a different set of keys. You can't reference these fields with a regular column name — the extraction operators are how you get to them.
What is the difference between -> and ->> in PostgreSQL?
The one thing that trips people up
->> always returns text. If the JSONB field contains a number, ->> returns the string '80', not the number 80. Comparing or doing arithmetic with it requires an explicit cast:
WHERE (metadata ->> 'status') = 'active'
AND (metadata ->> 'score')::numeric > 80The status comparison works without a cast because the target is already text. The score comparison needs ::numeric because you're comparing against a number. Skip the cast and PostgreSQL will raise a type error.
Navigating into a missing key returns NULL rather than an error, so a field that doesn't exist in one row's JSON silently becomes NULL in the output — which is usually correct for heterogeneous data, but worth knowing if you're assuming the key is always present.
For queries that filter on JSONB fields frequently, be aware that extraction in WHERE runs a full table scan unless there's a GIN index on the column. The query is correct either way, but performance can suffer on large tables without an index.
What do the #> and #>> JSONB operators do?
Deep paths with `#>` and `#>>`
For deeply nested structures, PostgreSQL also supports path-based operators. #> takes an array of keys as the path: metadata #> '{address, city}' is equivalent to metadata -> 'address' -> 'city'. #>> returns the same path as text, like ->>. These are useful when the path is longer or when you want to express nested navigation in a single operator. For one or two levels of nesting, chaining -> and ->> is clearer. For three or more levels, the path operators are easier to read.
Practice JSONB Field Extraction in SQL
Brightlane's product merchandising system displays color attributes for catalog items.
Write a query to return the ID, name, and color attribute for product id = 7.
Assumptions:
- The
productstable has one row per product with anid, aname, and anattributesJSONB column. - Each product's
attributesvalue is a JSONB object whose keys depend on the product type. - Product
id = 7has a'color'key in itsattributes. The result should pull that value out as plain text.
Output:
- A single row with columns
id,name, andcolor.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 JSONB Field Extraction practice problems
Write a query to return the ID, name, and color attribute for product id = 7.
Write a query to return the ID, name, and warranty term for product id = 9. The warranty term should be returned as the text value stored under 'warranty_years' in the product's attributes (no numeric cast).
Write a query to return the ID and name of every product whose 'format' attribute is 'Paperback'.
Write a query to return the ID, name, and warranty length in years for every product whose warranty term exceeds 1 year.
Write a query to return the ID, name, and material attribute for every product whose 'material' attribute is 'Denim'.
Write a query to return the ID, the page count extracted as a JSONB value in a column named pages_jsonb, and the page count extracted as a text value in a column named pages_text for product id = 34.
Write a query to return the event ID, user ID, and page path for every event whose event_type is 'page_view'. The page path is stored under the 'page' key in the event's properties JSONB column.
Write a query to return the ID, name, and color attribute for product id = 34.
Write a query to return the ID and name of every product whose 'gps' attribute is the boolean true value.
Write a query to return the ID, name, and page count for every book product with more than 400 pages.
Start learning to practice all 10 JSONB Field Extraction problems, with instant grading and mastery tracking.
Deeper guides on JSONB Field Extraction
- building a JSON document from a query
jsonb_build_object for the shape, jsonb_agg to collect the rows, and one nested document from two tables.
Common questions about JSONB Field Extraction
What happens when a JSONB key does not exist?
You get NULL rather than an error, with both extraction operators. That is usually right for data where rows carry different keys, but it does mean a misspelled key looks exactly like a key that is genuinely absent.
Why does comparing an extracted number fail?
Because the text operator always returns text, so comparing it with a number raises an operator does not exist error. Cast the extracted value to numeric first and the comparison behaves as you expect.
Which extraction operator should you use?
The one returning JSONB while you are still navigating, and the one returning text when you have arrived. Chaining the first gets you through nested objects; the second gives you a value you can cast, compare or display.
