Convert JSON Numbers
Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 18 of 25.
JSON extraction with ->> produces text even when the JSON value is numeric. Parenthesize the extraction before casting it. Then numeric comparisons behave like numbers rather than alphabetical text comparisons.
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 ->> 'items')::integer AS item_count FROM orders WHERE (meta::jsonb ->> 'items')::integer >= 3 ORDER BY id;Orders 12 and 14 pass with 3 and 5 items. Comparing text values would be unsuitable: the text 10 sorts before 2. Our fixtures contain whole-number items; invalid numeric text would cause a cast error.
Extract JSON text, parenthesize it, then cast before numeric arithmetic or comparison.
Challenge
MediumReturn id and amount_per_item, dividing amount by the extracted item count using numeric arithmetic and rounding to two decimal places. All fixtures have positive item counts. 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