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

Levenshtein Distance: Formula, Worked Table and Python Code

Levenshtein distance explained: the edit operations, the dynamic programming table for kitten and sitting, Python code with two-row memory, normalised…

Read article →
Software Development

URL Encoding Explained: Spaces, Plus Signs and UTF-8

URL encoding explained with tested Python and JavaScript code: percent encoding, reserved characters, %20 vs plus, UTF-8 bytes, encodeURI vs…

Read article →
DevOps

Gitignore: Patterns, Negation and Fixing Tracked Files

A tested gitignore guide: pattern rules, leading and trailing slashes, negation with !, the ignored parent folder trap, git check-ignore…

Read article →
Software Testing

Acceptance Criteria: Given When Then Examples and Checklist

How to write acceptance criteria testers can run: rule-based and Given When Then formats, a worked discount code example turned…

Read article →
Software Testing

State Transition Testing: Diagram, Table and Example

State transition testing explained with a login lockout example: states, events, the diagram, the state table, valid and invalid transitions,…

Read article →
Databases & SQL

Second Highest Salary in SQL: 5 Tested Ways and the Tie Trap

Find the second highest salary in SQL five ways: MAX subquery, LIMIT OFFSET, DENSE_RANK, a correlated subquery and per department,…

Read article →
Databases & SQL

SQL ROW_NUMBER: Top N per Group, Dedupe and Pagination

SQL ROW_NUMBER explained with tested queries: PARTITION BY, top N per group, deleting duplicates, pagination, the tie trap and how…

Read article →
Software Development

JavaScript Set: Union, Intersection and Removing Duplicates

JavaScript Set explained with tested examples: add, has, delete, deduplicating arrays, the new union and intersection methods, equality rules and…

Read article →
Test Automation

Python Unittest: Assert Methods, setUp and Mocks With Examples

A practical Python unittest tutorial: TestCase, assert methods, setUp and tearDown, subTest, skips, expected failures and mock, with a test…

Read article →
Software Development

Python Map and Filter: Examples vs List Comprehensions

Python map and filter explained with tested examples: lazy map objects, multiple iterables, filter(None), chaining, and a timed comparison with…

Read article →
Artificial Intelligence

Jaccard Similarity: Formula, Python, MinHash

Jaccard similarity measures overlap between two sets as intersection over union. Work the formula, compute it in Python for words…

Read article →
Software Development

Unix Timestamp Explained: Seconds vs ms

A Unix timestamp counts seconds since 1 January 1970 UTC. Learn seconds vs milliseconds, converting in Python, JavaScript and SQL,…

Read article →
DevOps

Git Hooks: Pre-commit and Commit-msg Examples

Git hooks run scripts at key moments such as commit and push. See tested pre-commit, commit-msg and prepare-commit-msg hooks, sharing…

Read article →
Test Automation

API Testing in Python: Pytest + Requests Suite

Build an API testing suite in Python with pytest and requests: fixtures, status codes, headers, JSON schema validation, CRUD flows,…

Read article →
Software Testing

Test Design Techniques: 9 Methods + Examples

Test design techniques help you write fewer tests that catch more bugs. Learn 9 black-box, white-box and experience-based methods with…

Read article →
Databases & SQL

SQL Temporary Table: Create, Use and Drop It

SQL temporary tables store interim results for one session. Learn syntax for SQL Server, PostgreSQL, MySQL, Oracle and SQLite, session…

Read article →
Databases & SQL

SQL CROSS JOIN: Every Combination + Examples

SQL CROSS JOIN returns every combination of rows from two tables. See the row math, product variants, calendar grids that…

Read article →
Software Development

JavaScript Date: Format, Parse, Timezone Bugs

JavaScript Date explained with tested Node.js code: zero-based months, the date-only UTC parsing trap, toISOString, getTimezoneOffset, Intl formatting, date math…

Read article →
Software Development

Python Requests: GET, POST, Timeouts, Retries

Python requests explained with code tested against a local API: GET with params, POST json vs data, headers and auth,…

Read article →
Artificial Intelligence

TF-IDF Explained: Formula and Worked Example

TF-IDF scores a word by how often it appears in a document and how rare it is across all documents….

Read article →
DevOps

Git Tag: Create, Push and Delete Release Tags

Git tag explained with tested commands: annotated vs lightweight tags, listing and sorting, pushing with follow-tags, deleting local and remote…

Read article →
Test Automation

Pytest Coverage: pytest-cov and Branch Gaps

Measure pytest coverage with pytest-cov: term-missing reports, branch coverage, HTML reports, pyproject settings and a fail-under gate for CI, with…

Read article →
Software Testing

Error Guessing in Testing: Checklist + Demo

Error guessing uses tester experience to predict where code breaks. Get a ready checklist of risky inputs, a step by…

Read article →
Databases & SQL

SQL LAG and LEAD: Compare Rows, 5 Examples

SQL LAG and LEAD read values from the previous or next row without a self join. Five tested examples: change,…

Read article →
Databases & SQL

SQL STRING_AGG: Combine Rows Into One String

SQL STRING_AGG joins values from many rows into one string per group. See syntax for PostgreSQL, SQL Server, MySQL GROUP_CONCAT,…

Read article →
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 →
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 →
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 →
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 →
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 →
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 →