SQL JSON Functions Explained: Working with JSON Data in SQL

-> and ->> sit one character apart, and that single character changes the data type of everything that comes back. Mix them up in a WHERE clause that compares against a number, and the filter silently returns nothing — no error, just an empty result set that looks like your data went missing when it didn’t. This one small distinction accounts for a surprising share of the “why isn’t my JSON query working” questions that come up in code review.

JSON functions in SQL exist because relational databases were never designed to store a flexible, nested blob of data in a single column. Then real applications started needing exactly that — a settings field, an API payload, a product’s variable attributes — and every major database vendor added its own set of functions to reach inside that blob without forcing you to parse it in application code first.

The tricky part is that these functions serve two very different audiences with two very different needs. A beginner usually wants to pull one value out of a JSON column and move on. Someone working at scale usually needs to filter on nested conditions, reshape JSON into rows, or build JSON back out of relational data for an API response. Both audiences are technically using “JSON functions,” but the tools that matter look almost nothing alike. Laying them out side by side is the fastest way to understand where you currently stand and what comes next.

Beginner Level: Pulling a Single Value Out of a JSON Column

The first thing almost anyone needs from a JSON column is a single value buried inside it — a “status” field, a “price” field, an “email” field nested a level or two down.

In PostgreSQL, this is -> for getting a JSON object or array back, and ->> for getting that same value converted to text. data->>'status' returns the status field as a plain text string you can compare, filter, or display directly. data->'status' returns it still wrapped as JSON, which matters if you need to chain another extraction on top of it but gets in the way if you just want the plain value.

MySQL takes a different path with JSON_EXTRACT(data, '$.status'), using a path expression starting with $ to describe where inside the document to look. MySQL also offers the shorthand data->'$.status', which behaves like PostgreSQL’s arrow but keeps the MySQL path syntax. SQL Server uses JSON_VALUE(data, '$.status') for a scalar value and JSON_QUERY when you need an object or array back instead.

At this level, the mental model is simple: one function, one path, one value out. You’re not filtering, you’re not looping over arrays, you’re just reaching into a document and grabbing what you need — the JSON equivalent of referencing a single cell in a spreadsheet.

Advanced Level: Reshaping and Querying JSON Structures

Production queries rarely stop at pulling one field. Once JSON is involved in a WHERE clause, a JOIN condition, or an aggregation, the requirements shift from “get this value” to “treat this value like a real column for the rest of the query.”

That shift usually means casting the extracted value to its real type before comparing it — (data->>'price')::numeric > 100 in PostgreSQL, since ->> always returns text and a text comparison against a number will not behave the way you expect. It also means reaching for JSON_TABLE (MySQL, Oracle, and newer SQL Server equivalents) or jsonb_to_recordset (PostgreSQL) when a single JSON column needs to become several proper relational columns for the rest of the query to join against or aggregate over.

The advanced habit worth building early: stop treating JSON extraction as a standalone operation and start treating it as a preprocessing step that produces ordinary, correctly-typed columns, which then flow into the rest of the query exactly like any other column would.

Beginner Level: Checking Whether a Key Exists

Not every row’s JSON document has the same shape. Some rows might be missing a field entirely, and checking for its existence — before trying to read its value — avoids null-versus-missing confusion down the line.

PostgreSQL offers ? for exactly this: data ? 'discount' returns true if the key exists anywhere at the top level of that document, regardless of what its value is. MySQL provides JSON_CONTAINS_PATH(data, 'one', '$.discount') for the same purpose, phrased more verbosely but doing the same job.

This distinction matters because a missing key and a key set to null are not the same thing, even though both can look identical once you’ve extracted a value and compared it against nothing. Checking existence first, before reading the value, is the safer default whenever a document’s shape isn’t guaranteed to be consistent.

Advanced Level: Filtering Rows Based on Nested JSON Conditions

Existence checks handle simple cases. Real filtering usually needs to reach several levels deep, sometimes into an array, sometimes based on a combination of conditions across multiple JSON fields at once.

PostgreSQL’s jsonb type supports containment queries with @> — data @> '{"status": "active", "region": "west"}' matches any document that contains both of those key-value pairs, nested structure and all, without you writing out separate extraction expressions for each field. This is considerably more compact than the equivalent chain of ->> comparisons, and it can use a GIN index, which matters once the table grows past a few thousand rows.

For array-based conditions — “does this order’s items array contain a product with this SKU” — the pattern shifts toward jsonb_array_elements combined with a WHERE clause, or MySQL’s JSON_CONTAINS(data, '"SKU123"', '$.items'). Either way, the underlying idea is the same: you’re no longer asking about a single value, you’re asking a question about the shape or contents of a nested structure, and the function you reach for needs to understand that structure rather than just returning one flat value.

Beginner Level: Reading a JSON Array

Arrays inside a JSON column show up constantly — a list of tags, a list of order items, a list of phone numbers. The beginner-level need is usually just to read one element out of that array by position.

data->'tags'->>0 in PostgreSQL reads the first element of the tags array as text. MySQL’s equivalent is JSON_EXTRACT(data, '$.tags[0]'). Both rely on the same idea: array indexes inside a JSON path expression, counted from zero, sitting right alongside the object key syntax you’d use for a regular nested field.

This works fine when you know exactly which position you need and the array’s length is predictable. It breaks down quickly once the array’s length varies row to row, or once you need every element rather than just one — which is where the next level takes over.

Advanced Level: Expanding a JSON Array Into Rows

Turning a JSON array into actual rows is one of the most useful — and most underused — JSON operations in SQL, because it converts a nested structure into something GROUP BY, JOIN, and ORDER BY can all work with directly.

PostgreSQL’s jsonb_array_elements(data->'items') takes an array and produces one output row per element, which you then join back against the original row to keep the surrounding columns attached. MySQL and SQL Server reach the same destination through JSON_TABLE, describing the array’s shape as a virtual table definition right inside the FROM clause.

Once an array is unpacked into rows, everything gets simpler: counting items per order becomes a plain COUNT, summing item totals becomes a plain SUM, and filtering for a specific item becomes a plain WHERE clause — none of it requiring any JSON-specific syntax anymore, because the data has already been reshaped into the relational form SQL was built to handle. This step — unpack first, then query normally — is worth treating as a default habit any time a JSON array is involved in an aggregation.

Beginner Level: Updating a Value Inside JSON

Editing JSON data in place, rather than replacing the whole document, is a common beginner need — changing one field’s value without touching the rest of the structure.

PostgreSQL’s jsonb_set(data, '{status}', '"shipped"') replaces the value at a given path while leaving everything else in the document untouched, and returns the full updated document ready to be written back with an UPDATE statement. MySQL’s JSON_SET(data, '$.status', 'shipped') follows the same pattern with its own path syntax.

At this level, the goal is narrow and self-contained: change one field, keep everything else exactly as it was, write the result back to the same column.

Advanced Level: Building JSON Output From Relational Data

The reverse direction — taking ordinary relational rows and assembling them into a JSON document — shows up constantly once an API or export process needs JSON as its output format rather than its input format.

PostgreSQL’s json_agg combined with json_build_object turns a set of rows into a JSON array of objects directly inside a query: json_agg(json_build_object('id', id, 'name', name)) produces a properly formed array without any application-side serialization step. MySQL’s JSON_ARRAYAGG and JSON_OBJECT cover the same ground, and SQL Server offers FOR JSON PATH as a clause appended to the end of a SELECT statement, converting the entire result set into JSON automatically.

This is where JSON functions stop being about reading data and start being about shaping output — often the last step in a query that otherwise looks like any ordinary relational query, right up until this final layer wraps the result in JSON for whatever’s consuming it downstream.

Performance: Where Beginner Habits Stop Scaling

A query built entirely from ->> extractions and text comparisons works fine on a few thousand rows and gets noticeably slower as a table grows, because most databases can’t index the inside of a JSON blob the same way they index a regular column by default.

The fix, at the advanced level, usually takes one of two forms. PostgreSQL lets you build a GIN index directly on a jsonb column, which speeds up containment queries using @> substantially. Alternatively, a generated (computed) column can extract a frequently-queried JSON field into its own real column, indexed normally, while the underlying JSON stays intact for everything else — giving you index performance on the specific fields your queries actually filter on, without restructuring the whole table.

Neither of these is something a beginner query needs to worry about on day one. Both become close to mandatory once a JSON-heavy table crosses into production-scale row counts and JSON filtering starts showing up in a query’s execution plan as the slowest step.

A Side-by-Side Summary

The pattern across every row of that table is consistent: beginner-level JSON work treats a document as something to read from, one value at a time, while advanced-level work treats it as a structure to reshape, filter, index, or rebuild — depending on which direction the data needs to flow.

Which side of that table matches what you’re working on right now — pulling a value out of a JSON column, or filtering and reshaping JSON at a scale where performance has started to matter? Tell me which database you’re using and what the JSON looks like, and I can help you find the exact function for it.