Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 125 ยท Functions and Expressions

Working with JSON

Store, validate, query and build JSON in SQL. Extract fields with json_extract, -> and ->>, expand arrays with json_each and create JSON results with json_group_array.

JSON is the language of web APIs, configuration files and event logs, so sooner or later JSON ends up in your database: an order's raw payment response, a user's settings, a list of tags. Modern SQL databases don't treat it as an opaque string. They can validate it, reach inside it, filter on its fields, expand its arrays into rows and even produce JSON from ordinary tables.

SQLite has had built-in JSON support since version 3.38, and the functions in this lesson are almost the same as those in MySQL, with PostgreSQL and SQL Server offering close equivalents. You'll learn to validate JSON, extract values with json_extract() and the -> and ->> operators, change documents, turn arrays into rows with json_each(), and build JSON results with json_object() and json_group_array().

Each example creates a small events table, because every run starts from a fresh database. Its payload column holds JSON text such as {"type": "signup", "user": {"name": "Amina"}, "tags": ["mobile"]}.

JSON is stored as text

SQLite has no separate JSON column type. A JSON document is simply text that happens to follow JSON syntax, and the JSON functions parse it when you call them. That means a TEXT column will accept invalid JSON too, so it's worth checking:

SQL Validating JSON

Press Run (or Ctrl + Enter) to see the result here.

json_valid() returns 1 or 0. JSON requires double-quoted keys, so {name: "Amina"} is invalid. json() checks a document and returns it minified, without extra spaces; it raises an error if the text isn't valid JSON. json_type() reports what sits at a path: object, array, text, integer, real, true, false or null.

To make sure a column only ever contains valid JSON, add a check constraint when you create the table: payload TEXT NOT NULL CHECK (json_valid(payload)). The Constraints lesson covers CHECK in detail.

Extracting values with json_extract()

json_extract(json, path) returns the value at a path. Paths always start with $, which means "the whole document":

Path Meaning
$ the whole document
$.type the type key of the top-level object
$.user.name the name key inside the user object
$.tags[0] the first element of the tags array (arrays count from 0)
$.tags[#-1] the last element of the array
$.items[1].qty the qty key of the second element of items
SQL Reading fields with json_extract

Press Run (or Ctrl + Enter) to see the result here.

JSON strings and numbers come back as ordinary SQL values, so amount is a real number you can add up, and name is plain text without quotes. Objects and arrays come back as JSON text, like {"name":"Amina","country":"KE"}. Two different situations both produce NULL: Mei's tags array is empty, so $.tags[0] doesn't exist, and Amina's amount exists but is JSON null. A missing path is never an error.

The -> and ->> operators

Writing json_extract() again and again gets long, so SQLite 3.38 added two operators that MySQL and PostgreSQL users will recognize:

  • json -> path returns the value as JSON: strings keep their quotes, so '"signup"'.
  • json ->> path returns the value as an SQL value: 'signup', just like json_extract().

For simple keys you can drop the $.: payload ->> 'type' means payload ->> '$.type', and a number selects an array element, so tags ->> 0 is the first element.

SQL Arrow operators

Press Run (or Ctrl + Enter) to see the result here.

The arrows chain: payload -> 'user' ->> 'name' first takes the user object as JSON, then its name as text. In practice, use ->> whenever you compare, sort or calculate, because you want the real value rather than its JSON spelling ('signup' doesn't equal '"signup"'). Use -> when you want to pass a piece of JSON on, such as a sub-object.

Index the fields you filter on. WHERE payload ->> 'type' = 'purchase' has to parse every document. An index on the expression, CREATE INDEX events_type ON events (payload ->> 'type');, lets SQLite find matching rows directly, just like an index on a normal column. See Partial, Expression and Covering Indexes.

Changing JSON documents

JSON functions never modify a document in place; they return a new document, which you can select or store back with UPDATE. Each function differs in how it treats keys that already exist:

Function If the path exists If the path is missing
json_set(json, path, value, ...) overwrites creates
json_insert(json, path, value, ...) leaves it alone creates
json_replace(json, path, value, ...) overwrites does nothing
json_remove(json, path, ...) removes it does nothing
json_patch(json, patch) merges an object in; null values delete keys adds the keys
SQL Modifying JSON

Press Run (or Ctrl + Enter) to see the result here.

Look at '$.tags[#]': the # means "one past the end", so json_set() appends to the array. Also notice json('true') in the first column. If you pass the SQL value 1 = 1, you get the number 1 in the JSON; wrapping a JSON literal in json() tells the function to insert it as JSON (here, the boolean true) rather than as a string.

To change stored documents, combine these functions with UPDATE, which you'll meet in UPDATE:

SQL
UPDATE events
SET payload = json_set(payload, '$.reviewed', json('true'))
WHERE payload ->> 'type' = 'purchase';

Turning arrays into rows with json_each()

json_each(json) is a table-valued function: you use it in FROM like a table, and it returns one row per element of an array (or per key of an object). Its most useful columns are key (the array index or object key), value, type and fullkey (the full path).

SQL Expanding arrays with json_each

Press Run (or Ctrl + Enter) to see the result here.

The comma in FROM events, json_each(events.payload, '$.tags') pairs each event with each of its own tags. Amina's event appears twice (once per tag), and Mei's event disappears because her array is empty. This "one row per array element" shape is what you need for filtering by array contents, as in the last query, and for counting tags with GROUP BY. The comma is a kind of join; the Joins chapter explains it properly.

json_tree() works the same way but walks every level of a nested document, which is handy for exploring JSON whose structure you don't know yet:

SQL
SELECT fullkey, type, atom
FROM json_tree('{"user": {"name": "Mei"}, "tags": ["x"]}');
-- $            object  NULL
-- $.user       object  NULL
-- $.user.name  text    Mei
-- $.tags       array   NULL
-- $.tags[0]    text    x

Building JSON from tables

The functions also work in the other direction, turning rows and columns into JSON. That's useful for APIs, exports and nested results:

Function Builds
json_object(key1, value1, key2, value2, ...) one object from pairs
json_array(value1, value2, ...) one array from values
json_group_array(value) an aggregate: one array from all rows
json_group_object(key, value) an aggregate: one object from all rows
SQL Building JSON results ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The last query produces a complete JSON array of objects, ready to send to a web page. The aggregates collapse all the matching rows into one value, like count() and sum() do (see Aggregate Functions). With GROUP BY you get one array per group, for example one product list per category.

Note that in_stock became 1 rather than true, because SQLite booleans are integers. If your API needs a real JSON boolean, use iif(stock > 0, json('true'), json('false')).

JSON text inside JSON functions. If you pass the text '[1,2]' to json_array(), you get ["[1,2]"]: an array containing a string. Values that come straight from another JSON function (such as json_object() inside json_group_array()) are recognized as JSON automatically; for anything else, wrap it in json() to embed it as JSON: json_array(json('[1,2]')) gives [[1,2]].

JSONB and JSON in other databases

SQLite 3.45 added JSONB, a binary encoding of JSON stored as a BLOB. Every function has a jsonb_ twin (jsonb(), jsonb_set(), jsonb_extract()...), and all JSON functions accept either form. Storing JSONB saves the parsing work on every read, which helps when tables are large and documents are queried often. Use json(column) to turn it back into readable text.

Other databases have dedicated JSON types and their own function names:

Task SQLite PostgreSQL MySQL SQL Server
Column type TEXT or JSONB BLOB jsonb (recommended) or json JSON nvarchar(max) (json in 2025)
Value as text ->>, json_extract ->>, #>> ->>, JSON_UNQUOTE(JSON_EXTRACT()) JSON_VALUE(doc, '$.a')
Sub-document -> ->, #> -> JSON_QUERY(doc, '$.a')
Array to rows json_each jsonb_array_elements JSON_TABLE OPENJSON
Build an object json_object jsonb_build_object JSON_OBJECT JSON_OBJECT (2022+)
Aggregate to array json_group_array jsonb_agg JSON_ARRAYAGG FOR JSON PATH
Contains json_each + WHERE doc @> '{"type":"signup"}' JSON_CONTAINS OPENJSON + WHERE
SQL
-- PostgreSQL: keys are plain names, not $ paths
SELECT payload ->> 'type', payload -> 'user' ->> 'name'
FROM events
WHERE payload @> '{"type": "purchase"}';

-- MySQL
SELECT payload ->> '$.type', JSON_LENGTH(payload, '$.tags')
FROM events;

-- SQL Server
SELECT JSON_VALUE(payload, '$.type'), JSON_VALUE(payload, '$.user.name')
FROM events;

When to use JSON columns

JSON is ideal for data whose shape varies or that you mostly store and return as a whole: raw API responses, per-user preferences, flexible metadata. It's a poor fit for values you constantly filter, join, sort or validate. Those belong in normal columns with proper types, constraints and foreign keys, where the database can protect and index them. A common design keeps the important fields as columns and the rest as a JSON column next to them. The Normalization and SQL vs NoSQL lessons discuss the trade-offs.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does '{"user": {"name": "Mei"}}' ->> '$.user.name' return?

    ->> returns an SQL value, so the string comes back without JSON quotes. -> would return the JSON text with quotes.
  2. 2. What does json_extract('{"tags": []}', '$.tags[0]') return?

    The array is empty, so there is no element 0. A path that doesn't exist returns NULL, never an error.
  3. 3. Which function adds a key only if it doesn't exist yet, leaving existing values untouched?

    json_insert creates missing paths and never overwrites. json_set overwrites and creates; json_replace only overwrites.
  4. 4. An event's tags array has 3 elements. How many rows does it produce in FROM events, json_each(events.payload, '$.tags')?

    json_each returns one row per array element, and each one is paired with its event. An event with an empty array produces no rows.
  5. 5. Which query returns all product names in category 4 as a single JSON array?

    json_group_array is an aggregate, so it combines all matching rows into one array. json_array builds a one-element array per row.

Summary

  • SQLite stores JSON as TEXT (or as a JSONB BLOB since 3.45); json_valid() checks it and json() validates and minifies it.
  • Paths start with $: $.user.name, $.tags[0], $.tags[#-1]. Missing paths return NULL.
  • json_extract() and ->> return SQL values for comparing and calculating; -> returns JSON text.
  • json_set, json_insert, json_replace, json_remove and json_patch return modified copies of a document.
  • json_each() turns an array into rows, and json_tree() walks a whole nested document.
  • json_object(), json_array(), json_group_array() and json_group_object() build JSON from tables.
  • PostgreSQL (jsonb), MySQL (JSON) and SQL Server (JSON_VALUE, OPENJSON) offer the same ideas with different names.
  • Keep frequently filtered and joined values in real columns; use JSON for flexible, document-shaped data.

Next: Aggregate Functions, where you'll count, sum and average whole tables.