Lesson 3.6 · Reading time: ~8 min

JSON functions let a query look inside a JSON document stored in a column: read a value by its path, filter rows by it, count the items of an array, or turn rows into JSON for an application. In this lesson you'll read values with JSON_VALUE, JSON_EXTRACT and the ->> operator, filter by them, work with arrays and build JSON with JSON_OBJECT and JSON_ARRAYAGG. The examples run on several databases of the site, because each database names these functions a little differently.

JSON Functions in SQL

JSON in a Table Column

Sometimes a row has attributes that differ from row to row, and the table stores them as one JSON document. The machine table of the SQLite database keeps such details:

MySQL and MariaDB have a JSON type, PostgreSQL has json and jsonb, SQLite and SQL Server store JSON as text. In all of them JSON functions work with the document as structured data, not as a plain string.

Reading a Value by Path

A value inside the document is addressed by a path: $ is the whole document, $.acquired is the acquired key, $[0] is the first item of an array, $[0].source is the source key of that item.

SQLite:

SELECT name, json_extract(details, '$.acquired') AS acquired
FROM machine;

Result: WY401 — 2023-05-01, Inphormex — 2021-07-15, AutoPlate 9000 — NULL. A missing key gives NULL, not an error.

SQLite and MySQL also have a short form, the ->> operator: details ->> '$.acquired' returns the same values.

In MySQL and MariaDB there are two functions, and the difference matters:

SELECT JSON_EXTRACT('{ "title": "ACADEMY DINOSAUR", "year": 2006}', '$.title') AS extracted,
       JSON_VALUE('{ "title": "ACADEMY DINOSAUR", "year": 2006}', '$.title') AS value;

Result: extracted is "ACADEMY DINOSAUR" with the quotes — it's still a piece of JSON; value is plain ACADEMY DINOSAUR.

JSON_EXTRACT returns JSON, so to compare it with a string you wrap it in JSON_UNQUOTE. JSON_VALUE returns an ordinary value right away; SQL Server uses the same function.

Filtering by a Value Inside JSON

A JSON function can be used in WHERE like any other expression. Since a missing key gives NULL, IS NULL and IS NOT NULL find the documents with and without a key.

SQLite:

SELECT name, json_extract(details, '$.refurbished') AS refurbished
FROM machine
WHERE json_extract(details, '$.refurbished') IS NOT NULL;

Result: one machine, Inphormex, refurbished on 2023-10-22.

JSON Arrays

In the University database (MariaDB), research_projects.funding holds an array of grants: [{ "source": "Private Foundation", "amount": 244542.96, ...}], or [] for a project without funding. JSON_LENGTH counts the items, and a path with an index reads one of them:

SELECT project_id, JSON_LENGTH(funding) AS grants,
       JSON_UNQUOTE(JSON_EXTRACT(funding, '$[0].source')) AS first_source
FROM research_projects
ORDER BY project_id
LIMIT 3;

Result: project 1 has 1 grant from Private Foundation; projects 2 and 3 have 0 grants, and their first source is NULL.

To check whether any item of an array has a value, MySQL and MariaDB have JSON_SEARCH, which returns the path of the match or NULL:

SELECT COUNT(*) AS nasa_projects
FROM research_projects
WHERE JSON_SEARCH(funding, 'one', 'NASA', NULL, '$[*].source') IS NOT NULL;

Result: 30 projects have a grant from NASA. `$[].source` means "the source of every item".*

PostgreSQL: the -> and ->> Operators

PostgreSQL reads JSON with operators instead of paths: -> returns a JSON value, ->> returns text. In the Bookings database, airport names are stored in several languages as jsonb, such as { "en": "Yakutsk Airport", "ru": "Якутск"}:

SELECT airport_code, airport_name ->> 'en' AS name, city ->> 'en' AS city
FROM airports_data
ORDER BY airport_code
LIMIT 3;

Result: AAQ — Anapa Vityazevo Airport, Anapa; ABA — Abakan Airport, Abakan; AER — Sochi International Airport, Sochi.

Building JSON from Rows

The opposite task is to return a result as JSON, for example for a web application. JSON_OBJECT builds an object from pairs of key and value.

MySQL, Sakila:

SELECT JSON_OBJECT('id', film_id, 'title', title, 'rating', rating) AS film
FROM film
ORDER BY film_id
LIMIT 2;

Result: { "id": 1, "title": "ACADEMY DINOSAUR", "rating": "PG"} and { "id": 2, "title": "ACE GOLDFINGER", "rating": "G"}.

JSON_ARRAYAGG is an aggregate function: it collects the values of many rows into one array.

SELECT JSON_ARRAYAGG(name) AS categories
FROM category;

Result: one row with an array of all 16 categories: ["Action", "Animation", "Children", ...].

In PostgreSQL the same is done by json_build_object and json_agg:

SELECT json_build_object('code', airport_code, 'city', city ->> 'en') AS airport
FROM airports_data
ORDER BY airport_code
LIMIT 2;

Result: { "code" : "AAQ", "city" : "Anapa"} and { "code" : "ABA", "city" : "Abakan"}.

JSON Functions in Different Databases

When to Store JSON

JSON is convenient for attributes that differ between rows or come from an outside system as a document. But a value inside JSON is harder to check, to join and to index than a column. If the same key is present in every row and is used in filters, it deserves its own column.

Key Takeaways:

• A path addresses a value inside JSON: $.key, $[0], $[0].key, $[*].key.
• JSON_VALUE (MySQL, MariaDB, SQL Server), json_extract and ->> (SQLite, MySQL) and ->> (PostgreSQL) read a value as an ordinary value.
• JSON_EXTRACT in MySQL and MariaDB returns JSON: a string comes back in quotes and needs JSON_UNQUOTE.
• A missing key gives NULL, so IS NULL and IS NOT NULL filter documents by the presence of a key.
• JSON_OBJECT builds an object from columns, JSON_ARRAYAGG collects rows into an array; PostgreSQL has json_build_object and json_agg.

Frequently Asked Questions

What is the difference between JSON_EXTRACT and JSON_VALUE?

JSON_EXTRACT returns a piece of JSON: a string comes back with quotes, an object or array as JSON text. JSON_VALUE returns a single scalar value as ordinary text, ready to compare or display.

What does a JSON function return when the key doesn't exist?

NULL. That's why WHERE json_extract(details, '$.refurbished') IS NULL finds the documents without this key.

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

-> returns a JSON value, ->> returns text. Use -> to go deeper into a document and ->> to get the final value.

Interview Questions

When would you store data in a JSON column instead of separate columns?

When the attributes differ from row to row, are rarely filtered on, or arrive as a ready document from another system. Values that every row has and that are used in filters, joins or constraints belong in regular columns.

How do you find rows whose JSON array contains a value?

In MySQL and MariaDB with JSON_SEARCH(doc, 'one', 'value', NULL, '$[*].key') IS NOT NULL or JSON_CONTAINS; in PostgreSQL with the containment operator @> on jsonb.

How do you return query results as JSON?

Build objects with JSON_OBJECT (json_build_object in PostgreSQL) and collect them into an array with JSON_ARRAYAGG (json_agg); SQL Server adds FOR JSON PATH to the query.