Menu
CoddyTech

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:

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 ->> '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 icon

Challenge

Medium

Return 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.
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