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.
- 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_eachturns 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
SCANtoSEARCH ... 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:
- Is there a
CHECK (json_valid(...))constraint, or equivalent type, so invalid documents are rejected on insert? - Which fields will queries filter or sort on? Each one needs an expression index or a generated column.
- Do documents carry a version number, so code can handle old and new shapes after a format change?
- Is there a limit on document size, so one huge payload cannot slow every query that reads the row?
- 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
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.