Read JSONB Fields
Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 16 of 25.
PostgreSQL JSONB stores a parsed JSON value. Our fixture supplies JSON as text, so cast meta to jsonb first. The ->> operator extracts an object field as SQL text.
Input table orders:
id | customer_id | amount | ordered_on | status | meta |
|---|---|---|---|---|---|
11 | 1 | 20 | 2025-01-02 | paid | {"channel":"web","items":2,"shipping":{"city":"Haifa"}} |
12 | 1 | 30 | 2025-01-04 | paid | {"channel":"app","items":3,"shipping":{"city":"Haifa"}} |
13 | 2 | 30 | 2025-02-01 | pending | {"channel":"web","items":1} |
14 | 3 | 50 | 2025-02-03 | paid | {"channel":"app","items":5,"shipping":{"city":"Eilat"}} |
15 | 2 | 10 | 2025-02-03 | paid | {"channel":"web","items":2} |
SELECT id, meta::jsonb ->> 'channel' AS channel FROM orders ORDER BY id;Order 11 returns web without JSON quotation marks. A missing field produces SQL NULL. JSON keys are case-sensitive, so channel and Channel refer to different keys.
Cast JSON text to jsonb and use ->> when you need a field as SQL text.
Challenge
MediumReturn id and channel for orders whose channel is app. Extract channel from meta and order by id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Connected Data
1Connect Tables
Match Rows with JOINKeep Rows with LEFT JOINCount After a JoinFind Missing MatchesCustomer Coverage4Work with JSONB
Read JSONB FieldsNavigate Nested JSONConvert JSON NumbersMatch JSONB StructureWeb Shipping AuditPractice on your own: PostgreSQL playground