Menu
CoddyTech

Navigate Nested JSON

Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 17 of 25.

The -> operator retains a JSON value so you can navigate into another object. Use ->> at the final step to extract SQL text. A missing intermediate key produces NULL rather than an error.

Input table orders:

idcustomer_idamountordered_onstatusmeta
111202025-01-02paid{"channel":"web","items":2,"shipping":{"city":"Haifa"}}
121302025-01-04paid{"channel":"app","items":3,"shipping":{"city":"Haifa"}}
132302025-02-01pending{"channel":"web","items":1}
143502025-02-03paid{"channel":"app","items":5,"shipping":{"city":"Eilat"}}
152102025-02-03paid{"channel":"web","items":2}
SELECT id, meta::jsonb -> 'shipping' ->> 'city' AS shipping_city FROM orders ORDER BY id;

Order 11 returns Haifa from its shipping object. Order 13 has no shipping object and therefore returns NULL. Converting shipping to text too early would stop JSON navigation.

Keep intermediate objects as JSON with -> and extract the final text with ->>.

challenge icon

Challenge

Medium

Return id and destination from shipping.city. Use Pickup when the nested city is missing. 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.
quiz iconTest yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Connected Data

Practice on your own: PostgreSQL playground