SQL › Converting and Reading JSON

CAST, JSON and JSONB

CAST changes a value's type, such as text to a number. JSONB columns hold whole documents, like {"brand": "Nova", "ram": "8GB"}, and operators read the values inside them.

Syntax

Order of execution: FROM (and any function that turns JSON into rows) → WHERE → SELECT → ORDER BY

A function in FROM such as jsonb_each_text adds rows; operators in WHERE and SELECT read values from each document.

Code
SELECT CAST(column AS type), column::type,
       json_column -> 'key', json_column ->> 'key'
FROM table_name, jsonb_each_text(json_column)
WHERE json_column ? 'key' AND json_column @> '{"key": "value"}';

Examples

Casting

Change a type to calculate, compare or display differently.

Code
SELECT price::int, CAST(price AS TEXT), '2025-06-01'::date, '42'::int + 1 FROM products;
 
-- Fails: 'abc' isn't a number
SELECT 'abc'::int;

Reading values

Pick a value out of a JSON document, as JSON or as text.

Code
SELECT product_name,
       specs -> 'author' AS author_json,     -- "Paulo Coelho", with quotes
       specs ->> 'author' AS author          -- Paulo Coelho
FROM products;
 
-- Numbers come out as text: cast them before doing maths
SELECT product_name, (specs ->> 'pages')::int + 10 AS pages_with_cover FROM products;

Filtering on JSON

Keep rows whose document has a key or contains a value.

Code
SELECT product_name FROM products WHERE specs ? 'author';              -- has an author key
SELECT product_name FROM products WHERE specs @> '{"brand": "Volt"}';  -- brand is Volt
SELECT product_name FROM products WHERE specs ->> 'brand' = 'Volt';    -- the same, as text

Arrays inside JSON

Check an array for a value, or turn its items into rows.

Code
SELECT product_name FROM products WHERE specs -> 'sizes' ? 'UK8';
 
SELECT product_name, size
FROM products, jsonb_array_elements_text(specs -> 'sizes') AS size
WHERE product_id = 9;

A document as rows

List every key and value of a document, one per row.

Code
SELECT key, value
FROM products, jsonb_each_text(specs)
WHERE product_id = 12
ORDER BY key;

In other databases

JSONB and the operators above are PostgreSQL's. MySQL has a JSON type read with paths, such as specs ->> '$.brand'. SQL Server and Oracle use the standard JSON_VALUE(specs, '$.brand'), which PostgreSQL has from version 17.

Remember

• -> returns JSON and ->> returns text; compare and display with ->>.
• Text compares letter by letter: '8GB' > '16GB'. Cast numbers before comparing or sorting.
• Keys are case-sensitive: specs ->> 'Brand' is NULL.
• A cast fails when the value can't convert. Casting a decimal to an integer rounds it in PostgreSQL, MySQL and Oracle, but SQL Server cuts the decimals off.

Practice

1. Brand and Whole-Rupee Price (Easy)
2. Spec Sheets With RAM (Medium)
3. Sizes of Products Sold in L (Medium)