CodeRang
SQL topics

SQL JSON: PostgreSQL JSONB Queries and Practice

SQL JSON queries are useful when structured data is stored inside JSON documents rather than regular columns. This example shows how PostgreSQL extracts nested JSONB values, returns text with ->>, and casts JSON fields before numeric comparisons.

Updated · SQL Arena team

JSON and nested data courseAll problems

What you will practice

  • Extract JSON objects with -> and scalar text with ->>
  • Read nested fields from PostgreSQL jsonb
  • Cast JSON strings to numeric SQL types
  • Expand arrays with jsonb_array_elements
  • Handle missing keys separately from JSON null

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Find orders above 100 and extract the city from JSON

The orders table stores part of each order inside the jsonb column payload. Return orders whose total is at least 100. The output must contain order_id, city, and total. City is nested under delivery, while total is stored as a JSON string. Sort the result by order id.

Query 1PostgreSQLv16
SELECT
  o.id AS order_id,
  o.payload->'delivery'->>'city' AS city,
  (o.payload->>'total')::numeric AS total
FROM orders o
WHERE (o.payload->>'total')::numeric >= 100
ORDER BY o.id;
Submit
Resultcustomersorders
Answer accepted

How SQL JSON Queries Work in PostgreSQL

The city requires two JSON operations. o.payload->'delivery' returns the nested delivery object as JSON. Adding ->>'city' then extracts the city value as text. That distinction is the main reason PostgreSQL has both operators: -> keeps a JSON value, while ->> converts the selected scalar to text.

The total field looks numeric, but the JSON document stores it as a string. Therefore o.payload->>'total' has the PostgreSQL type text. The ::numeric cast turns that text into a number before the WHERE condition compares it with 100. Without the cast, you would be doing a text comparison rather than a numeric one, which can produce incorrect ordering and filtering for values with different digit lengths.

The SELECT uses the same cast so total is returned as a numeric value. WHERE removes smaller orders, and ORDER BY o.id makes the output deterministic. Once this pattern is comfortable, the next step is working with jsonb arrays, testing value types, and distinguishing a missing property from a property explicitly set to JSON null.

PostgreSQL provides both json and jsonb data types. JSONB stores a parsed binary representation and supports additional operators and indexing options. The -> operator returns a JSON value, while ->> returns text, so a numeric-looking value extracted with ->> may still need an explicit cast before arithmetic or comparison. Operators can also be chained for nested documents: use -> while navigating through JSON objects and ->> when you want the final scalar value as SQL text.

SQLPostgreSQL v16
SELECT
  o.id AS order_id,
  o.payload->'delivery'->>'city' AS city,
  (o.payload->>'total')::numeric AS total
FROM orders o
WHERE (o.payload->>'total')::numeric >= 100
ORDER BY o.id;
Query result
order_idcitytotal
102Boston145.00
104Rome210.00
105Amsterdam119.90

Common SQL JSON and JSONB Mistakes

01

Using -> when you need text

The -> operator returns JSON, not ordinary SQL text. That is useful while traversing nested structures, but inconvenient when you need to compare, concatenate, or cast the final scalar. Use ->> when the next operation expects a regular SQL value.

02

Comparing numeric JSON strings as text

A JSON field containing "145.00" is still a string. Comparing it directly with another string uses lexical rules, not numeric rules. Cast extracted values to numeric, integer, or another suitable SQL type before calculations and numeric filters.

03

Treating missing properties as JSON null

A document without a coupon key is structurally different from a document containing "coupon": null. Those cases may need different reporting or validation logic. Check key existence when the distinction matters instead of relying on a single null test.

Practice problems on this topic

JSON and nested data course

After simple field extraction, move to exercises where the JSON shape itself matters. You can practice reading a city from a nested object, checking when an error value is null, inspecting values sent by an import, and detecting when items is not actually an array. Harder tasks combine extraction with calculations such as counting order items, computing revenue by a city stored inside payload, or averaging item prices by category. SQL Arena also includes the “JSON and Nested Data” course, where each lesson combines explanation with a query task and the first chapters are free.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is SQL JSON?

SQL JSON usually refers to storing, reading, filtering, or transforming JSON data with SQL. The exact syntax differs between database systems, so PostgreSQL JSON queries are not identical to MySQL or BigQuery JSON queries.

How do you query JSON in PostgreSQL?

PostgreSQL provides JSON operators and functions for accessing document fields. For example, -> returns JSON while ->> returns the selected value as text.

What is JSONB in PostgreSQL?

JSONB is PostgreSQL's binary JSON data type. It supports structured JSON operations and indexing options that are useful when JSON documents need to be searched or filtered.

What is the difference between -> and ->> in PostgreSQL JSON?

The -> operator keeps the result as JSON, while ->> returns text. This distinction matters when you need to continue traversing a nested document or cast the final value to another SQL type.

See also

Practice SQL JSON Queries

SQL Arena contains 1,192 automatically checked tasks, with separate browser sandboxes for PostgreSQL, MySQL, ClickHouse, and BigQuery. Answers are also checked against hidden data, so a query needs to express the actual rule rather than match one visible result. The free plan includes 292 tasks with no card required. For a structured path from basic extraction to arrays and nested values, continue with the “JSON and Nested Data” course.

Practice SQL JSON

More practice from CodeRang