1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

SQL JSON Functions Explained: Query, Update and Index JSON in SQL, Tested

SQL JSON functions let you read, filter, reshape and update JSON stored in a database column. Learn extraction, the arrow operators, turning arrays into rows, building JSON, safe updates and making JSON lookups fast, with every query run.

10 min read
SQL JSON functions explained: query, update and index JSON in SQL, tested

Key takeaways

  • SQL JSON functions read values with paths like $.user.city. Names differ by database, but the ideas are the same.
  • -> returns JSON; ->> returns a plain SQL value you can compare and sum.
  • json_each turns a JSON array into rows, so you can join and group on its items.
  • JSON lookups scan the table unless you index the expression. Our plan changed from SCAN to SEARCH ... USING COVERING INDEX.

APIs, event logs and settings often arrive as JSON, and many teams store that JSON straight into a text column. That is fine until someone asks, “how many orders came from Pune?” and the answer is buried inside the JSON. SQL JSON functions let the database reach into those documents, pull out values, expand arrays into rows and even update fields in place.

We ran every query below in SQLite 3.50.4 through Python 3.13, and the outputs are copied from that run. PostgreSQL, MySQL and SQL Server versions are listed for comparison and were not run. If you handle the same data in code as well, our Python JSON guide covers the application side.

Why store JSON in a SQL database?

JSON columns suit data whose shape varies or that you mostly store and return as-is: raw webhook payloads, audit events, user preferences or third-party responses. Core business data that you filter, join and enforce rules on usually belongs in normal columns, as our database normalization guide explains. Many real systems mix both.

The sample data

CREATE TABLE events(id INTEGER PRIMARY KEY, payload TEXT);
INSERT INTO events(payload) VALUES
('{"type":"order","user":{"id":7,"city":"Pune"},"items":[{"sku":"PEN","qty":2},{"sku":"INK","qty":1}],"total":180.5}'),
('{"type":"refund","user":{"id":9,"city":"Delhi"},"items":[],"total":-40}'),
('{"type":"order","user":{"id":7,"city":"Pune"},"items":[{"sku":"PAD","qty":5}],"total":250,"coupon":null}'),
('not json at all');

The last row is deliberately broken, because real data always contains a surprise like this.

Validate before you query

SELECT id, json_valid(payload) FROM events;
-- (1,1) (2,1) (3,1) (4,0)

json_valid returns 1 for well-formed JSON and 0 otherwise. Calling extraction functions on invalid JSON raises an error and stops the whole query, so we filtered with WHERE json_valid(payload) in the examples that follow. Better still, add CHECK (json_valid(payload)) to the table so bad rows never get in; our SQL constraints guide shows how.

Reading values with json_extract

SELECT id, json_extract(payload, '$.type'), json_extract(payload, '$.user.city')
FROM events WHERE json_valid(payload);
-- (1,'order','Pune') (2,'refund','Delhi') (3,'order','Pune')

The path starts with $, the whole document. A dot goes into an object key, and [n] picks an array element. SQLite also accepts [#-1] for the last element:

SELECT json_extract(payload,'$.items[0].sku'),
       json_extract(payload,'$.items[#-1].sku'),
       json_array_length(payload,'$.items')
FROM events WHERE id = 1;
-- ('PEN', 'INK', 2)

The -> and ->> operators

The arrow operators are shorter, and they behave differently in an important way:

SELECT payload -> '$.user', payload ->> '$.user.id',
       typeof(payload -> '$.total'), typeof(payload ->> '$.total')
FROM events WHERE id = 1;
-- ('{"id":7,"city":"Pune"}', 7, 'text', 'real')

-> returned JSON text, while ->> returned a real number. Use ->> for anything you compare, sort or add up; use -> when you want to keep a JSON fragment, such as a nested object. PostgreSQL and MySQL use the same two operators with the same split.

Turning arrays into rows with json_each

Line items inside an order are an array. json_each is a table-valued function that returns one row per element, which you can join like any table:

SELECT e.id, j.value ->> '$.sku', j.value ->> '$.qty'
FROM events e, json_each(e.payload, '$.items') j
WHERE json_valid(e.payload);
-- (1,'PEN',2) (1,'INK',1) (3,'PAD',5)

SELECT j.value ->> '$.sku', SUM(j.value ->> '$.qty')
FROM events e, json_each(e.payload, '$.items') j
WHERE json_valid(e.payload) GROUP BY 1;
-- ('INK',1) ('PAD',5) ('PEN',2)

Event 2 has an empty array, so it produced no rows and vanished from the result. That is ordinary inner-join behaviour; use a LEFT JOIN if you need to keep it. Once arrays are rows, every normal tool such as grouping and joins works.

Missing key vs JSON null

Event 3 has "coupon": null, and no event has a nope key:

SELECT json_extract(payload,'$.coupon') IS NULL,
       json_type(payload,'$.coupon'), json_type(payload,'$.nope')
FROM events WHERE id = 3;
-- (1, 'null', NULL)

json_extract returned SQL NULL for both, so you cannot tell them apart that way. json_type can: it returned the string 'null' for the stored null and SQL NULL for the missing key. That difference matters for APIs where “not sent” and “cleared” mean different things. Our SQL NULL guide covers the SQL side.

Building JSON from rows

SELECT json_object('city', payload ->> '$.user.city', 'orders', json_group_array(id))
FROM events WHERE json_valid(payload)
GROUP BY payload ->> '$.user.city';
-- {"city":"Delhi","orders":[2]}
-- {"city":"Pune","orders":[1,3]}

json_object builds an object from key and value pairs, and json_group_array is an aggregate that collects values into an array. Together they let an API endpoint return ready-made JSON straight from SQL, which keeps response shapes consistent with your REST API design.

Updating JSON safely

Four functions change documents, and they differ in whether they create or overwrite keys:

SELECT json_set('{"a":1}', '$.a', 2, '$.b', 3),   -- {"a":2,"b":3}
       json_insert('{"a":1}', '$.a', 9),          -- {"a":1}   (never overwrites)
       json_replace('{"a":1}', '$.z', 9),         -- {"a":1}   (never creates)
       json_remove('{"a":1,"b":2}', '$.b');       -- {"a":1}

SELECT json_patch('{"a":1,"b":{"c":2}}', '{"b":{"c":3},"a":null}');
-- {"b":{"c":3}}

json_patch follows the JSON merge patch rule: objects merge, and a null value deletes the key, which is why a disappeared. To change stored data, use these inside an UPDATE:

UPDATE events SET payload = json_set(payload, '$.status', 'shipped') WHERE id = 1;
-- payload ->> '$.status' is now 'shipped'

Tip: pick the function that matches your intent. json_insert silently does nothing if the key exists, which is a hard bug to spot in a migration.

Comparing JSON documents

SELECT '{"a":1, "b":2}' = '{"b":2,"a":1}', json('{"a":1, "b":2}');
-- (0, '{"a":1,"b":2}')

As plain text, two equivalent documents were not equal because of spacing and key order. json() removed the whitespace, but it did not sort keys. To compare documents reliably, extract the fields you care about, or normalise in application code before storing.

Making JSON lookups fast

We added 50,000 more events and asked for user 7:

EXPLAIN QUERY PLAN SELECT COUNT(*) FROM events WHERE payload ->> '$.user.id' = 7;
-- SCAN events

CREATE INDEX ix_user ON events(payload ->> '$.user.id');
-- SEARCH events USING COVERING INDEX ix_user (<expr>=?)
-- count: 102

Without an index, SQLite read every row and parsed every document. After indexing the exact expression, it searched the index directly. The query must use the same expression as the index, or the planner cannot match it. Another option is a generated column, ALTER TABLE events ADD COLUMN user_id INT GENERATED ALWAYS AS (json_extract(payload,'$.user.id')) VIRTUAL, which returned 7 for the first event and gives the value a normal column name. More on plans in our SQL indexes guide.

SQLite 3.45 and later can also store a binary format called JSONB. For our first event, length(jsonb(payload)) was 102 bytes against 133 bytes of text, and binary JSON avoids re-parsing on each read.

The same ideas in other databases

Task PostgreSQL MySQL SQL Server
Get a scalar doc->>'city' doc->>'$.city' JSON_VALUE(doc,'$.city')
Get an object doc->'user' doc->'$.user' JSON_QUERY(doc,'$.user')
Array to rows jsonb_array_elements JSON_TABLE OPENJSON
Update a key jsonb_set JSON_SET JSON_MODIFY

These are reference names only; we did not run them here. PostgreSQL’s jsonb type also supports GIN indexes for containment searches.

When not to use JSON columns

  • When you filter or join on a field constantly; promote it to a real, indexed column.
  • When you need foreign keys or strict types, which JSON cannot enforce.
  • When many writers update different keys of the same document at once.

A checklist for JSON columns

Before you add a JSON column to a production table, run through these questions with your team:

  1. Is there a CHECK (json_valid(...)) constraint, or equivalent type, so invalid documents are rejected on insert?
  2. Which fields will queries filter or sort on? Each one needs an expression index or a generated column.
  3. Do documents carry a version number, so code can handle old and new shapes after a format change?
  4. Is there a limit on document size, so one huge payload cannot slow every query that reads the row?
  5. Do tests cover missing keys, JSON nulls, empty arrays and wrong types, not just the happy path?

If several answers are “no”, the data may belong in ordinary columns instead.

Final thoughts

SQL JSON functions turn a text blob into data you can query. Validate on the way in, use ->> for values, json_each for arrays, the right update function for your intent, and an expression index for any field you search often. Used that way, JSON columns are a practical tool rather than a hiding place for data.

Frequently asked questions

How do I extract a value from JSON in SQL?

Use json_extract with a path such as $.user.city, or the ->> operator. Function names vary: SQL Server uses JSON_VALUE, while PostgreSQL and MySQL support the arrow operators.

What is the difference between -> and ->> in SQL?

-> returns a JSON value, such as an object or quoted string, while ->> returns a plain SQL value. In our test, total came back as text with -> and as a real number with ->>.

How do I turn a JSON array into rows?

Use a table-valued function: json_each in SQLite, jsonb_array_elements in PostgreSQL, JSON_TABLE in MySQL or OPENJSON in SQL Server. Then join and group on the rows.

Can I index a JSON field?

Yes. Create an index on the exact extraction expression or on a generated column. Our query plan changed from a full scan to an index search after adding one.

Should I store JSON in a relational database?

It works well for variable or rarely queried data such as raw payloads and settings. Move fields you filter, join or validate often into normal columns.

Keep learning

Work with Stack Quora

Need help with testing, WordPress or SEO?

We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
Software Development

HTTP Methods Explained: GET, POST, PUT, PATCH, DELETE, Tested on a Live Server

HTTP methods explained with a small server we ran: GET, POST, PUT, PATCH, DELETE, HEAD and OPTIONS, safe vs idempotent,…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Databases & SQL

SQL ORDER BY Multiple Columns: ASC, DESC, NULLS LAST, Case and Custom Order

SQL ORDER BY with multiple columns explained with tested queries: mixed ASC and DESC, tie-breakers, NULLS FIRST and LAST, case-insensitive…

Read article →
Databases & SQL

SQL Running Total: SUM OVER, Per-Group Totals, Moving Sums and the RANGE Trap

How to calculate a SQL running total with SUM() OVER: per-group totals, daily cumulative sums, moving sums, percent of total,…

Read article →
Software Development

JavaScript Sort Array: Numbers, Objects, Strings and Why 10 Comes Before 5

How to sort an array in JavaScript correctly: numbers with a compare function, objects by one or more fields, strings…

Read article →
Software Development

Python String Methods: Cheat Sheet, Real Examples and the strip() Trap

Python string methods with tested examples: split, join, strip, replace, find, startswith, is-methods, casefold, partition and translate, plus the strip()…

Read article →
Software Development

Python zip() Explained: Loop Two Lists at Once, Unzip, strict=True and Traps

Python zip() pairs items from several lists in one loop. Tested examples for dicts, unzipping with *, strict=True, zip_longest, sliding…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
Software Development

UTF-8 Encoding Explained: How Text Becomes Bytes and How to Fix Garbled Text

UTF-8 encoding explained with byte-level examples we ran in Python: how characters become 1 to 4 bytes, why café turns…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →
Software Testing

Retesting vs Regression Testing: 8 Key Differences with a Real Bug Fix Example

Retesting vs regression testing explained with a real bug fix run in pytest: retesting confirmed the fix, the regression run…

Read article →
Databases & SQL

Delete Duplicate Rows in SQL: 5 Safe Methods, Tested Step by Step

How to delete duplicate rows in SQL safely: find them, preview them, then remove them with ROW_NUMBER, MIN(id), EXISTS, rowid…

Read article →
Databases & SQL

SQL RANK vs DENSE_RANK vs ROW_NUMBER: Differences with Tested Examples

SQL RANK vs DENSE_RANK vs ROW_NUMBER in one table: how each handles ties, top-N per group, the second-highest salary trap…

Read article →
Software Development

JavaScript Optional Chaining (?.) Explained: 8 Examples, ?? Defaults and Traps

JavaScript optional chaining stops “Cannot read properties of undefined” errors. 8 tested examples for properties, arrays and methods, ?? vs…

Read article →
Artificial Intelligence

RAG Chunking Strategies Explained: Fixed, Overlap, Sentence, Recursive and Headings, Measured

RAG chunking strategies compared on one document with tiktoken 0.14: fixed-size, overlap, sentence packing, recursive splitting and heading-based chunks, measuring…

Read article →
DevOps

Git Stash Explained: Save, Apply, Pop and Untracked Files, Tested

Git stash explained with commands we ran on Git 2.56: push with a message, untracked files, list and show, apply…

Read article →
Software Testing

Pairwise Testing Explained: How All-Pairs Cuts 216 Tests to 13, Tested

Pairwise testing explained with a generator we ran in Python 3.13: a checkout with 216 combinations covered by 13 tests,…

Read article →
Test Automation

Test Doubles Explained: Dummy, Stub, Spy, Mock and Fake, with Tested Python Code

Test doubles explained with one Python example run in pytest 9.2: dummy, stub, spy, mock and fake, when to use…

Read article →
Databases & SQL

SQL COALESCE Explained: Defaults, NULLIF, IFNULL vs ISNULL and Traps, Tested

SQL COALESCE explained with queries we ran in SQLite 3.50: first non-null values, empty strings, NULL in maths, NULLIF for…

Read article →
Databases & SQL

SQL Self Join Explained: Employee-Manager, Pairs and Row Comparisons, Tested

SQL self join explained with queries we ran in SQLite 3.50: employee and manager, earning more than the manager, unique…

Read article →
Artificial Intelligence

LLM Context Window Explained: Tokens, Limits and How to Fit More, Measured

The LLM context window explained with token counts we measured using tiktoken: words vs tokens for prose, code and Hindi,…

Read article →
DevOps

Git Cherry-Pick Explained: Copy the Commit You Need, Safely, Tested

Git cherry-pick explained with commands we ran in Git 2.56: copying a hotfix to a release branch, new hashes and…

Read article →
Test Automation

Snapshot Testing Explained: How It Works, When It Helps and When It Hurts

Snapshot testing explained with a pytest and syrupy demo we ran: creating snapshots, reading the diff after a code change,…

Read article →
Software Testing

Mutation Testing Explained: Why 100% Coverage Still Missed Our Bug

Mutation testing explained with a small mutation tool we built and ran: 14 mutants, a test suite with 100% line…

Read article →
Artificial Intelligence

Prompt Injection Explained: How AI Apps Get Tricked and How to Defend Them

Prompt injection explained for builders: direct and indirect attacks, why keyword filters fail (our test blocked 1 of 4), and…

Read article →
DevOps

DNS Records Explained: A, AAAA, CNAME, MX, TXT and TTL With Real Lookups

DNS records explained with lookups we ran: how resolution works, A and AAAA, CNAME rules, MX and null MX, TXT…

Read article →
DevOps

Feature Flags Explained: Safe Releases, Gradual Rollouts and Flag Hygiene

Feature flags explained with a rollout simulation we ran: flag types, percentage rollouts with stable hashing, kill switches, allow lists,…

Read article →
Test Automation

Test Data Generation With Python Faker: Realistic, Repeatable and Safe Data

Test data generation with Python Faker, run and measured: seeding for repeatable data, locales, unique values, domain-shaped records, edge cases…

Read article →
Software Testing

Shift-Left Testing Explained: Find Bugs Earlier Without Slowing the Team

Shift-left testing explained: what it means, why early defects are cheaper, practices from requirement reviews to CI checks, the tester's…

Read article →
Artificial Intelligence

LLM Structured Output Explained: Getting Reliable JSON From AI Models

LLM structured output explained with validation we ran in Pydantic: JSON mode and schemas, constrained decoding, the ways model output…

Read article →
Test Automation

API Contract Testing Explained: Catch Breaking Changes Before They Ship

API contract testing explained with a schema check we ran: what a contract is, consumer-driven contracts, Pact, OpenAPI, which changes…

Read article →
Software Testing

Risk-Based Testing Explained: Score Risks, Build a Matrix and Test What Matters

Risk-based testing explained step by step: identifying risks, scoring likelihood and impact, building a risk matrix, mapping risk to test…

Read article →