SQL JSON Functions Explained: Working with JSON Data in SQL

Say you are trying to pull a single field out of a JSON column — something like a customer’s shipping city, buried inside a preferences or metadata column that your application stored as a JSON blob instead of separate columns. You write what looks like a reasonable query, run it, and get back either the entire JSON document, a value wrapped in quotation marks you didn’t ask for, or a flat NULL where you were sure there was data. None of that means you wrote broken SQL. It means JSON functions behave differently from ordinary column access, and the failure modes are consistent enough to work through one at a time.

That’s the approach this post takes. Instead of walking through every JSON function in the order a manual would list them, it’s organized around the specific symptoms you’re likely to hit, what’s actually causing each one, and the fix that resolves it.

Symptom: Your Query Returns the Whole JSON Document, Not the Field You Wanted

You select the column, expecting one value, and instead you get the full {"city": "Austin", "zip": "78701", "country": "US"} staring back at you.

Cause: you queried the column itself, not a path inside it. A JSON column is still just a column from SQL’s point of view — selecting it plainly returns its entire contents, the same way selecting a text column returns the whole string rather than a substring.

Fix: use a path-extraction function to reach inside the document. In PostgreSQL, that’s the -> or ->> operator; in MySQL, it’s JSON_EXTRACT or the ->> shorthand; in SQL Server, it’s JSON_VALUE. The path itself typically mirrors the key name: extracting city from that example means referencing $.city (SQL Server, MySQL) or simply 'city' as the key (PostgreSQL). Get the path wrong — misspell the key, or point to a key that’s nested one level deeper than you assumed — and you’re back to square one, so it’s worth printing the raw document first to confirm the exact key names and nesting before writing the extraction.

Symptom: The Value Comes Back Wrapped in Quotation Marks

You extract city and instead of Austin, you get "Austin" — quotes included, as if it were a literal string representation rather than the value itself.

Cause: some extraction operators return a JSON-typed result rather than a native SQL type. PostgreSQL’s -> operator, for instance, returns jsonb, which prints its string values with quotes because it’s preserving JSON’s own representation of a string. The value is technically correct; it just hasn’t been converted into a plain SQL text type yet.

Fix: switch to the operator or function variant designed to unwrap that JSON string into native text. In PostgreSQL, that’s ->> instead of ->. In SQL Server, JSON_VALUE already returns scalar text, so this particular issue tends to show up more with JSON_QUERY, which is meant for extracting JSON fragments (objects or arrays), not scalars — using it on a plain string value is usually the actual mistake. As a rule: reach for the “value” variant when you want a scalar you can filter or display directly, and reserve the “query” or unwrapped variant for cases where you genuinely need a nested object or array back.

Symptom: The Extraction Returns NULL Even Though You Can See the Data

You look at the raw JSON, the key is clearly there, and yet your extraction function insists on returning NULL.

Cause: this is almost always one of three things — a path typo, a case-sensitivity mismatch, or a nesting mismatch. JSON keys are case-sensitive by definition, so City and city are different keys as far as the JSON functions are concerned, even if your SQL column names are usually case-insensitive. Nesting mismatches are just as common: if city actually sits inside a nested address object rather than at the top level, a path like $.city will quietly return NULL instead of erroring, because as far as the function is concerned, there simply is no top-level city key.

Fix: confirm the exact structure first, rather than guessing at the path. Select the raw JSON column for one row and read it directly, or use a function that lists keys (JSON_OBJECT_KEYS in PostgreSQL, for example) to confirm what’s actually present at each level. Once the real path is confirmed — $.address.city instead of $.city, say — the extraction typically starts returning exactly what you expected.

Symptom: Filtering on a JSON Field Doesn’t Behave Like Filtering on a Normal Column

You try to add a WHERE clause on an extracted JSON value, and it either errors out with a type mismatch or silently returns zero rows.

Cause: the value coming out of a JSON extraction is often still typed as JSON or text, even when it looks like a number. Comparing that raw extracted value against a numeric literal — WHERE extracted_value = 100 when extracted_value is still JSON-typed text "100" — can fail the comparison or throw a type error, depending on the database.

Fix: cast the extracted value explicitly before comparing it. In PostgreSQL, that typically means appending ::integer or ::numeric after the extraction; in SQL Server, wrapping the result in CAST or CONVERT; in MySQL, using CAST(... AS SIGNED) or similar. It’s a small extra step, but skipping it is one of the more common reasons a JSON-based WHERE clause returns nothing when the data clearly matches on inspection.

Symptom: You Can’t Get a JSON Array Into Separate Rows

Your JSON field holds an array — a list of tags, or a list of line items on an order — and you need each element as its own row, not one row containing the whole array as a single value.

Cause: JSON functions like JSON_EXTRACT or ->> are built to pull out a single value or a single nested fragment. They were never meant to expand an array into multiple rows; that’s a fundamentally different operation from extraction, closer to what a JOIN does than what a SELECT column does.

Fix: use the function each database provides specifically for this — json_array_elements (or jsonb_array_elements) in PostgreSQL, JSON_TABLE in MySQL and SQL Server. These functions take a JSON array and expand it into one row per element, which you can then JOIN back against your original row using a lateral join or CROSS APPLY, depending on the database. This is the step most people miss the first time: expanding an array isn’t a plain function call, it’s a table-producing operation, so it belongs in your FROM clause alongside a join, not in your SELECT list alongside a normal function.

Symptom: Aggregating Rows Back Into a Single JSON Document Feels Like It Needs a Loop

You have several rows — maybe one row per line item on an order — and you need them combined into a single JSON array or object, attached to the parent order.

Cause: this is the reverse of the previous problem, and it’s easy to assume it needs procedural logic (a loop building up a string) simply because that’s how you’d solve it in application code.

Fix: every major database has a JSON aggregate function built for exactly this. PostgreSQL offers json_agg (or jsonb_agg) to build an array, and json_object_agg to build a key-value object, both usable directly alongside GROUP BY. MySQL provides JSON_ARRAYAGG and JSON_OBJECTAGG. SQL Server generally handles this through FOR JSON PATH at the end of a query rather than a per-group aggregate function, which is the one case here where the mental model shifts slightly — you’re formatting the whole result set as JSON, not aggregating within a GROUP BY. In all three cases, the point is the same: this is a built-in aggregate, not something that needs a client-side loop.

Symptom: Queries Against a JSON Column Are Slow, No Matter How You Write Them

Everything works correctly, but a query filtering on a JSON field takes noticeably longer than an equivalent query filtering on a normal column, especially as the table grows.

Cause: most databases can’t use a standard index on a raw JSON column the way they can on an integer or text column, because the value inside needs to be parsed and traversed at query time rather than compared directly. Every row’s JSON gets parsed fresh, for every query, unless you’ve told the database to do something smarter.

Fix: the exact mechanism depends on the database, but the underlying idea is consistent — index the extracted value, not the raw JSON. PostgreSQL supports both a GIN index on a jsonb column for general containment queries, and a plain B-tree index on a computed expression (an index built directly on the result of a JSON extraction) if you’re consistently filtering on one specific key. MySQL supports generated columns: define a virtual column that extracts the value you care about, then index that generated column. SQL Server allows a computed column over JSON_VALUE, indexed the same way. None of these require restructuring your table entirely — they just require identifying the one or two fields you actually filter or sort on regularly, and giving the database a normal index to use for those specifically.

Symptom: A Function That Worked Fine Last Week Now Throws a Syntax Error

You move a query from one environment to another — development to production, or one database engine to another — and a JSON function that ran without issue suddenly isn’t recognized at all.

Cause: JSON functions are one of the least standardized corners of SQL. PostgreSQL, MySQL, and SQL Server each implemented their own JSON support on different timelines, with different function names, different path syntax, and in PostgreSQL’s case, an entirely separate json versus jsonb type distinction with its own performance and indexing implications. A function name or operator that’s correct in one system frequently doesn’t exist at all in another.

Fix: treat JSON function names as database-specific from the outset, the same way you’d treat a proprietary string function. Before writing a query, confirm which engine you’re targeting and check that engine’s specific JSON documentation rather than assuming syntax transfers. If your codebase genuinely needs to support multiple database engines, it’s often worth isolating JSON-handling logic into its own layer or set of view definitions, so the engine-specific syntax lives in one place instead of scattered across every query that happens to touch a JSON column.

A Quick Reference for the Symptoms Above

Most JSON troubleshooting in SQL comes down to one of these eight patterns. Once you can recognize which one you’re looking at, the fix is usually a single function swap rather than a rewrite of the whole query.

Which of these symptoms matches what you’re seeing right now — a NULL you can’t explain, a slow query, or an array you can’t flatten? Describe what your JSON structure looks like and what you’re trying to pull out of it, and I can help you find the exact function and path for it.