📦

MariaDB JSON Functions

MariaDB

Complete reference for MariaDB JSON functions covering JSON_EXTRACT, JSON_SET, JSON_CONTAINS, JSON_TABLE, JSON_MERGE_PATCH, JSON_ARRAYAGG, and 34+ more. Every function includes syntax, path expression examples, and differences from MySQL JSON functions. Updated for MariaDB 11.x.

31 functions

What are MariaDB JSON Functions?

MariaDB JSON functions provide full support for storing, querying, and modifying JSON data stored in TEXT or JSON column types. JSON_EXTRACT() retrieves values using path expressions (e.g. '$.key' or '$[0]'), JSON_SET() modifies values, JSON_CONTAINS() tests for value membership, and JSON_TABLE() (added in MariaDB 10.6) shreds a JSON array into a relational table. MariaDB JSON functions are broadly compatible with MySQL JSON functions but differ from PostgreSQL's JSONB operators.

JSON_EXTRACT

MariaDB 10.2+→ JSON

Extracts a value from a JSON document at the specified JSONPath location(s).

Signature

JSON_EXTRACT(json_doc, path[, path]...)

Parameters

Parameter	Type	Description
json_doc	JSON	The JSON document (stored as LONGTEXT in MariaDB)
path	VARCHAR	JSONPath expression such as $.key or $[0]

Examples

Extract scalar value

sql

SELECT JSON_EXTRACT('{"a":1}', '$.a');

→-- 1

Array index (zero-based)

sql

SELECT JSON_EXTRACT('[1,2,3]', '$[1]');

→-- 2

->> operator returns unquoted text

sql

SELECT col->>'$.name' FROM users;

→-- Alice

Nested path

sql

SELECT JSON_EXTRACT(data, '$.addr.city') FROM customers;

→-- "London"

Wildcard path returns array

sql

SELECT JSON_EXTRACT(data, '$.tags[*]') FROM posts;

→-- ["sql","db"]

⚠Anti-Pattern— Forgetting the $ root token

Omitting the leading $ in the path (e.g. passing 'name' instead of '$.name') returns NULL silently.

✓ Instead: Always start paths with $ and test with JSON_VALID() before querying production data.

The -> operator is shorthand for JSON_EXTRACT and returns a quoted JSON value, while ->> returns the unquoted scalar text. In most queries you want ->> to avoid having to call JSON_UNQUOTE separately.

example

SELECT data->>'$.name' AS name, data->'$.tags' AS tags FROM profiles WHERE id = 1;

→name: 'Alice', tags: '["sql","db"]'

◆MariaDB Note

MariaDB stores JSON columns as LONGTEXT with a CHECK constraint; the value is validated on insert.

⇄ vs MySQL: Identical in MySQL 5.7+. The -> and ->> operators also work in both.

⇄ vs PostgreSQL: PostgreSQL uses the -> and ->> operators or jsonb_extract_path(); there is no JSON_EXTRACT() function.

JSON_VALUE

MariaDB 10.6.1+→ VARCHAR / typed via RETURNING clause

Extracts a scalar value from a JSON document at the given path and returns it as a SQL type.

Signature

JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])

Parameters

Parameter	Type	Description
json_doc	JSON	The JSON document
path	VARCHAR	JSONPath expression pointing to a scalar
RETURNING type	keyword	Optional SQL type to cast the result to (e.g. INT, DECIMAL, DATE)
on_empty	keyword	NULL ON EMPTY, ERROR ON EMPTY, or DEFAULT val ON EMPTY
on_error	keyword	NULL ON ERROR, ERROR ON ERROR, or DEFAULT val ON ERROR

Examples

Typed extraction

sql

SELECT JSON_VALUE('{"price":9.99}', '$.price' RETURNING DECIMAL(10,2));

→-- 9.99

Extract integer from nested key

sql

SELECT JSON_VALUE(data, '$.user.id' RETURNING INT) FROM orders;

→-- 42

Default when path is absent

sql

SELECT JSON_VALUE('{}', '$.missing' DEFAULT 0 ON EMPTY);

→-- 0

Return as DATE type

sql

SELECT JSON_VALUE(data, '$.created_at' RETURNING DATE) FROM events;

→-- 2024-01-15

Suppress conversion errors

sql

SELECT JSON_VALUE(data, '$.score' RETURNING INT NULL ON ERROR) FROM results;

→-- NULL

⚠Anti-Pattern— Using JSON_VALUE on non-scalar paths

Pointing JSON_VALUE at an object or array path returns NULL (or triggers ON ERROR) because it only returns scalars.

✓ Instead: Use JSON_EXTRACT for objects/arrays and JSON_VALUE only for leaf scalar values.

Without RETURNING, JSON_VALUE returns a VARCHAR. Specifying RETURNING INT, RETURNING DATE, etc. avoids implicit type conversions and makes comparisons and ORDER BY work correctly.

example

SELECT JSON_VALUE(data, '$.price' RETURNING DECIMAL(10,2)) AS price FROM products ORDER BY price DESC;

→price: 199.99, 49.99, 9.99

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Available in MySQL 8.0.21+ with the same syntax.

⇄ vs PostgreSQL: PostgreSQL uses ->> or json_extract_path_text() for untyped text; explicit CAST is separate.

JSON_SET

MariaDB 10.2+→ JSON

Inserts or updates values in a JSON document, replacing existing values and adding missing ones.

Signature

JSON_SET(json_doc, path, val[, path, val]...)

Parameters

Parameter	Type	Description
json_doc	JSON	The source JSON document
path	VARCHAR	JSONPath expression pointing to the target location
val	ANY	The new value to set at the given path

Examples

Update existing key

sql

SELECT JSON_SET('{"a":1}', '$.a', 2);

→-- {"a": 2}

Insert new key

sql

SELECT JSON_SET('{"a":1}', '$.b', 2);

→-- {"a": 1, "b": 2}

Multiple paths in one call

sql

SELECT JSON_SET(data, '$.status', 'active', '$.updated', NOW()) FROM orders WHERE id=1;

→-- updated JSON

In-place column update

sql

UPDATE users SET profile = JSON_SET(profile, '$.score', score + 1) WHERE id = 42;

→-- 1 row affected

Update array element

sql

SELECT JSON_SET('[1,2,3]', '$[1]', 99);

→-- [1, 99, 3]

⚠Anti-Pattern— Chaining JSON_SET calls in application code

Calling JSON_SET separately for each key in application code generates multiple round-trips and intermediate strings.

✓ Instead: Pass all path-value pairs to a single JSON_SET call: JSON_SET(doc, '$.a', 1, '$.b', 2).

JSON_SET inserts a value if the path does not exist and updates it if it does — making it the safest choice for general-purpose JSON mutation. Use JSON_INSERT when you only want to add new keys, or JSON_REPLACE when you only want to update existing ones.

example

UPDATE profiles SET data = JSON_SET(data, '$.last_login', NOW()) WHERE id = 42;

→1 row affected — $.last_login created or updated

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL uses jsonb_set() for jsonb or the || merge operator; no JSON_SET() function.

JSON_INSERT

MariaDB 10.2+→ JSON

Inserts new values into a JSON document only if the path does not already exist.

Signature

JSON_INSERT(json_doc, path, val[, path, val]...)

Parameters

Parameter	Type	Description
json_doc	JSON	The source JSON document
path	VARCHAR	JSONPath expression for the new value location
val	ANY	The value to insert

Examples

New key inserted

sql

SELECT JSON_INSERT('{"a":1}', '$.b', 2);

→-- {"a": 1, "b": 2}

Existing key is NOT overwritten

sql

SELECT JSON_INSERT('{"a":1}', '$.a', 99);

→-- {"a": 1}

Insert multiple keys

sql

SELECT JSON_INSERT('{}', '$.name', 'Alice', '$.age', 30);

→-- {"name": "Alice", "age": 30}

Append to array

sql

SELECT JSON_INSERT('[1,2]', '$[2]', 3);

→-- [1, 2, 3]

Conditional insert in update

sql

UPDATE orders SET meta = JSON_INSERT(meta, '$.created_at', NOW()) WHERE meta->>'$.created_at' IS NULL;

→-- rows affected

⚠Anti-Pattern— Using JSON_INSERT expecting it to update

Developers coming from key-value stores expect JSON_INSERT to overwrite existing keys like a set operation.

✓ Instead: Use JSON_SET when you want upsert (insert-or-replace) semantics.

JSON_INSERT adds a key only if it is absent; it never overwrites an existing value. This makes it safe for initializing default fields on existing JSON documents without clobbering data.

example

UPDATE users SET prefs = JSON_INSERT(prefs, '$.theme', 'light') WHERE prefs IS NOT NULL;

→theme set to 'light' only for rows where $.theme did not already exist

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL has no direct equivalent; use jsonb_set() with create_missing=true or the || operator.

JSON_REPLACE

MariaDB 10.2+→ JSON

Replaces existing values in a JSON document, ignoring paths that do not exist.

Signature

JSON_REPLACE(json_doc, path, val[, path, val]...)

Parameters

Parameter	Type	Description
json_doc	JSON	The source JSON document
path	VARCHAR	JSONPath expression of the value to replace
val	ANY	The replacement value

Examples

Existing key replaced

sql

SELECT JSON_REPLACE('{"a":1}', '$.a', 99);

→-- {"a": 99}

Non-existent key silently ignored

sql

SELECT JSON_REPLACE('{"a":1}', '$.b', 99);

→-- {"a": 1}

Safe in-place update

sql

UPDATE users SET prefs = JSON_REPLACE(prefs, '$.theme', 'dark') WHERE id = 5;

→-- 1 row affected

Replace array element

sql

SELECT JSON_REPLACE('[1,2,3]', '$[0]', 10);

→-- [10, 2, 3]

Multiple replacements

sql

SELECT JSON_REPLACE(data, '$.status', 'closed', '$.closed_at', NOW()) FROM tickets;

→-- updated JSON

⚠Anti-Pattern— Expecting JSON_REPLACE to add missing keys

JSON_REPLACE silently skips paths that do not exist, so missing keys produce no error and no change.

✓ Instead: Use JSON_SET if you want insert-or-update behavior.

Unlike JSON_SET, JSON_REPLACE returns the document unchanged if the specified path does not exist. Use it when you want to guard against accidentally adding unexpected keys.

example

UPDATE orders SET meta = JSON_REPLACE(meta, '$.status', 'shipped') WHERE id = 101;

→meta updated only if $.status already existed; no change otherwise

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL uses jsonb_set() with create_missing=false for update-only behavior.

JSON_REMOVE

MariaDB 10.2+→ JSON

Removes one or more values from a JSON document at the specified paths.

Signature

JSON_REMOVE(json_doc, path[, path]...)

Parameters

Parameter	Type	Description
json_doc	JSON	The source JSON document
path	VARCHAR	JSONPath expression of the element to remove

Examples

Remove a key

sql

SELECT JSON_REMOVE('{"a":1,"b":2}', '$.b');

→-- {"a": 1}

Remove array element by index

sql

SELECT JSON_REMOVE('[1,2,3]', '$[1]');

→-- [1, 3]

Remove multiple sensitive keys

sql

SELECT JSON_REMOVE(data, '$.password', '$.secret') FROM users;

→-- sanitised JSON

Conditional removal

sql

UPDATE profiles SET data = JSON_REMOVE(data, '$.tmp') WHERE JSON_CONTAINS_PATH(data, 'one', '$.tmp');

→-- rows affected

Nested path removal

sql

SELECT JSON_REMOVE('{"a":{"b":1,"c":2}}', '$.a.c');

→-- {"a": {"b": 1}}

⚠Anti-Pattern— Removing array elements by value

JSON_REMOVE only accepts path expressions (positions), not values — you cannot pass a value to remove.

✓ Instead: Use JSON_SEARCH to find the path of the value first, then pass that path to JSON_REMOVE.

JSON_REMOVE accepts multiple path arguments and removes all matching paths in left-to-right order. Removing a parent path automatically removes all children — you do not need to list nested keys separately.

example

SELECT JSON_REMOVE(data, '$.internal_id', '$.debug_info') FROM documents WHERE id = 5;

→JSON document with both $.internal_id and $.debug_info removed

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL uses the #- operator (e.g. doc #- '{key}') or jsonb_delete_path() for jsonb.

JSON_MERGE

MariaDB 10.2+ (deprecated)→ JSON

Merges two or more JSON documents, combining duplicate keys into arrays (deprecated alias for JSON_MERGE_PRESERVE).

Signature

JSON_MERGE(json_doc, json_doc[, json_doc]...)

Parameters

Parameter	Type	Description
json_doc	JSON	A JSON document to merge; two or more required

Examples

Non-overlapping keys merged

sql

SELECT JSON_MERGE('{"a":1}', '{"b":2}');

→-- {"a": 1, "b": 2}

Duplicate keys combined into array

sql

SELECT JSON_MERGE('{"a":1}', '{"a":2}');

→-- {"a": [1, 2]}

Arrays concatenated

sql

SELECT JSON_MERGE('[1,2]', '[3,4]');

→-- [1, 2, 3, 4]

Three-way merge

sql

SELECT JSON_MERGE('{"a":1}', '{"a":2}', '{"a":3}');

→-- {"a": [1, 2, 3]}

Column merge

sql

SELECT JSON_MERGE(base, overrides) FROM config_table;

→-- merged JSON per row

⚠Anti-Pattern— Expecting JSON_MERGE to overwrite duplicates

Unlike a typical dictionary merge, JSON_MERGE collects duplicate key values into an array rather than replacing them.

✓ Instead: Use JSON_MERGE_PATCH if you want last-writer-wins semantics on duplicate keys.

JSON_MERGE is an alias for JSON_MERGE_PRESERVE and generates a deprecation warning. JSON_MERGE_PATCH (RFC 7396) replaces duplicate keys with the new value, which is usually what you want for config updates. JSON_MERGE_PRESERVE combines arrays for duplicate keys.

example

SELECT JSON_MERGE_PATCH('{"a":1,"b":2}', '{"b":3,"c":4}');

→{"a": 1, "b": 3, "c": 4}

◆MariaDB Note

Deprecated alias — use JSON_MERGE_PRESERVE in new code.

⇄ vs MySQL: Available in MySQL 5.7+ but deprecated in MySQL 8.0; prefer JSON_MERGE_PRESERVE.

⇄ vs PostgreSQL: PostgreSQL uses the || operator on jsonb for shallow merge (last-writer-wins).

JSON_MERGE_PATCH

MariaDB 10.2.25+ / 10.3.15+ / 10.4.5+→ JSON

Merges JSON documents following RFC 7396 — duplicate keys in later documents overwrite earlier ones, and null values remove keys.

Signature

JSON_MERGE_PATCH(json_doc, json_doc[, json_doc]...)

Parameters

Parameter	Type	Description
json_doc	JSON	A JSON document to merge; the rightmost document wins on conflicts

Examples

Later value wins for key b

sql

SELECT JSON_MERGE_PATCH('{"a":1,"b":2}', '{"b":99,"c":3}');

→-- {"a": 1, "b": 99, "c": 3}

null patch value removes the key

sql

SELECT JSON_MERGE_PATCH('{"a":1,"b":2}', '{"b":null}');

→-- {"a": 1}

Apply a partial update patch

sql

UPDATE settings SET config = JSON_MERGE_PATCH(config, ?) WHERE user_id = ?;

→-- 1 row affected

Sequential patch application

sql

SELECT JSON_MERGE_PATCH(a, b, c) FROM patches ORDER BY version;

→-- final merged doc

Nested objects replaced, not deep-merged

sql

SELECT JSON_MERGE_PATCH('{"x":{"y":1}}', '{"x":{"z":2}}');

→-- {"x": {"z": 2}}

⚠Anti-Pattern— Expecting deep recursive merging

JSON_MERGE_PATCH replaces nested objects wholesale rather than merging them recursively, so sibling keys in a nested object can be lost.

✓ Instead: Use JSON_MERGE_PRESERVE if you need deep array/object merging, or use JSON_SET for targeted key updates.

JSON_MERGE_PATCH follows RFC 7396: top-level keys from the second document overwrite keys in the first. Setting a key to JSON null in the patch removes that key from the result — this is the standard way to delete a field via merge.

example

UPDATE user_prefs SET settings = JSON_MERGE_PATCH(settings, '{"theme":"dark","lang":null}') WHERE user_id = 7;

→theme updated to 'dark'; lang key removed from the document

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Available in MySQL 8.0+ with the same RFC 7396 semantics.

⇄ vs PostgreSQL: PostgreSQL's || operator on jsonb does a shallow last-writer-wins merge; no native RFC 7396 implementation.

JSON_MERGE_PRESERVE

MariaDB 10.2.25+ / 10.3.15+ / 10.4.5+→ JSON

Merges two or more JSON documents, preserving all values for duplicate keys by combining them into arrays.

Signature

JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc]...)

Parameters

Parameter	Type	Description
json_doc	JSON	A JSON document to merge; duplicate keys are collected into arrays

Examples

Duplicate keys combined into array

sql

SELECT JSON_MERGE_PRESERVE('{"a":1}', '{"a":2}');

→-- {"a": [1, 2]}

Arrays concatenated

sql

SELECT JSON_MERGE_PRESERVE('{"tags":["a"]}', '{"tags":["b"]}');

→-- {"tags": ["a", "b"]}

Distinct keys simply combined

sql

SELECT JSON_MERGE_PRESERVE('{"a":1}', '{"b":2}');

→-- {"a": 1, "b": 2}

Join and merge

sql

SELECT JSON_MERGE_PRESERVE(r1.data, r2.data) FROM t AS r1 JOIN t AS r2 ON r1.id = r2.parent_id;

→-- merged JSON

Three-way preserve

sql

SELECT JSON_MERGE_PRESERVE('{"a":1}', '{"a":2}', '{"a":3}');

→-- {"a": [1, 2, 3]}

⚠Anti-Pattern— Using JSON_MERGE_PRESERVE when you want last-writer-wins

If you expect duplicate keys to be overwritten, JSON_MERGE_PRESERVE will silently wrap them in arrays instead.

✓ Instead: Use JSON_MERGE_PATCH for RFC 7396 last-writer-wins semantics.

When both documents have the same key, JSON_MERGE_PRESERVE wraps the values into an array and concatenates them. This is ideal for accumulating tag lists or event arrays across multiple JSON documents.

example

SELECT JSON_MERGE_PRESERVE('{"tags":["a"]}', '{"tags":["b","c"]}');

→{"tags": ["a", "b", "c"]}

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Available in MySQL 8.0+; replaces the deprecated JSON_MERGE().

⇄ vs PostgreSQL: PostgreSQL has no exact equivalent; the || operator on jsonb overwrites duplicates.

JSON_ARRAY

MariaDB 10.2+→ JSON

Creates a JSON array from the supplied list of values.

Signature

JSON_ARRAY([val[, val]...])

Parameters

Parameter	Type	Description
val	ANY	Zero or more values to include in the array; SQL NULL becomes JSON null

Examples

Numeric array

sql

SELECT JSON_ARRAY(1, 2, 3);

→-- [1, 2, 3]

Mixed with JSON null

sql

SELECT JSON_ARRAY('a', 'b', NULL);

→-- ["a", "b", null]

Row as array

sql

SELECT JSON_ARRAY(id, name, score) FROM leaderboard LIMIT 1;

→-- [7, "Alice", 98]

Nested object in array

sql

SELECT JSON_ARRAY(JSON_OBJECT('k', v)) FROM t;

→-- [{"k": "val"}]

Empty array

sql

SELECT JSON_ARRAY();

→-- []

⚠Anti-Pattern— Wrapping an already-JSON value

Passing a JSON string literal to JSON_ARRAY treats it as a plain string and double-encodes it.

✓ Instead: Use JSON_EXTRACT or CAST to materialise the JSON value before passing it, or use JSON_MERGE to combine arrays.

Unlike GROUP_CONCAT which drops NULLs, JSON_ARRAY includes NULL arguments as JSON null values. This makes it the right choice when the position of each element in the array is significant.

example

SELECT JSON_ARRAY(product_id, name, NULL) AS row_tuple FROM products LIMIT 1;

→[101, "Widget", null]

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL uses json_build_array() or the ARRAY[] constructor with to_json().

JSON_OBJECT

MariaDB 10.2+→ JSON

Creates a JSON object from alternating key-value pairs.

Signature

JSON_OBJECT([key, val[, key, val]...])

Parameters

Parameter	Type	Description
key	VARCHAR	String key for the JSON object property
val	ANY	Value for the corresponding key; SQL NULL becomes JSON null

Examples

Basic object construction

sql

SELECT JSON_OBJECT('name', 'Alice', 'age', 30);

→-- {"name": "Alice", "age": 30}

Build from columns

sql

SELECT JSON_OBJECT('id', id, 'email', email) FROM users WHERE id = 1;

→-- {"id": 1, "email": "a@b.com"}

Nested array value

sql

SELECT JSON_OBJECT('data', JSON_ARRAY(1, 2, 3));

→-- {"data": [1, 2, 3]}

Empty object

sql

SELECT JSON_OBJECT();

→-- {}

Function values

sql

SELECT JSON_OBJECT('ts', NOW(), 'user', USER());

→-- {"ts": "2024-...", "user": "root@..."}

⚠Anti-Pattern— Passing an odd number of arguments

Passing an odd number of arguments causes an error because keys and values must be paired.

✓ Instead: Always supply key-value pairs; use NULL as a placeholder value if needed.

JSON_OBJECT alternates key-value arguments. Keys are automatically converted to strings, but passing a NULL key raises an error. Wrap dynamic key expressions in CAST(... AS CHAR) when in doubt.

example

SELECT JSON_OBJECT('id', id, 'score', score, 'grade', IF(score>=90,'A','B')) FROM results WHERE id = 1;

→{"id": 1, "score": 92, "grade": "A"}

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL uses json_build_object() with the same alternating key-value approach.

JSON_QUOTE

MariaDB 10.2+→ JSON

Wraps a string value in double quotes and escapes special characters to produce a valid JSON string literal.

Signature

JSON_QUOTE(string)

Parameters

Parameter	Type	Description
string	VARCHAR	The string to encode as a JSON string literal

Examples

Basic quoting

sql

SELECT JSON_QUOTE('hello');

→-- "hello"

Internal quotes escaped

sql

SELECT JSON_QUOTE('say "hi"');

→-- "say \"hi\""

NULL passthrough

sql

SELECT JSON_QUOTE(NULL);

→-- NULL

Quote a column value

sql

SELECT JSON_QUOTE(name) FROM products LIMIT 1;

→-- "Widget Pro"

Embed newline-containing string safely

sql

SELECT JSON_SET('{}', '$.msg', JSON_QUOTE('line1\nline2'));

→-- {"msg": "line1\nline2"}

⚠Anti-Pattern— Using JSON_QUOTE to produce a scalar number

JSON_QUOTE always produces a string literal; calling JSON_QUOTE(42) returns '"42"' (a string), not the number 42.

✓ Instead: Pass numeric values directly to JSON_SET or JSON_OBJECT without quoting.

JSON_QUOTE escapes special characters (backslash, double-quote, control characters) and wraps the value in double quotes. This is essential when building JSON strings by concatenation rather than using JSON_OBJECT.

example

SELECT CONCAT('{"msg":', JSON_QUOTE(user_input), '}') FROM messages LIMIT 1;

→{"msg": "Hello \"world\""}

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL uses to_json() or quote_literal(); no JSON_QUOTE() function.

JSON_UNQUOTE

MariaDB 10.2+→ VARCHAR

Removes the outer JSON string quotes and unescapes escape sequences, returning a plain SQL string.

Signature

JSON_UNQUOTE(json_val)

Parameters

Parameter	Type	Description
json_val	JSON	A JSON string value (with surrounding double quotes) to unquote

Examples

Remove outer quotes

sql

SELECT JSON_UNQUOTE('"hello"');

→-- hello

Common extract-then-unquote pattern

sql

SELECT JSON_UNQUOTE(JSON_EXTRACT('{"name":"Alice"}', '$.name'));

→-- Alice

->> is shorthand for JSON_UNQUOTE(JSON_EXTRACT(...))

sql

SELECT col->>'$.name' FROM users;

→-- Alice

Unescape sequences

sql

SELECT JSON_UNQUOTE('"line1\\nline2"');

→-- line1\nline2

Use in WHERE-style comparison

sql

SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.code')) = 'ABC' FROM orders;

→-- 1 / 0

⚠Anti-Pattern— Applying JSON_UNQUOTE to a non-string JSON value

Calling JSON_UNQUOTE on a JSON number or object just returns the value as-is — it only truly unquotes JSON strings.

✓ Instead: Use CAST or CONVERT when you need to coerce a JSON number or boolean to a SQL type.

JSON_UNQUOTE removes the outer double-quotes from a JSON string value and unescapes backslash sequences. Use the ->> shorthand in queries to save verbosity; use JSON_UNQUOTE explicitly only when chaining with other JSON functions.

example

SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.city')) FROM users WHERE id = 1;

→London (without surrounding quotes)

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL's ->> operator and json_extract_path_text() return unquoted text directly.

JSON_CONTAINS

MariaDB 10.2+→ INT (1 or 0)

Returns 1 if the target JSON document contains the candidate JSON value at the optional path.

Signature

JSON_CONTAINS(target, candidate[, path])

Parameters

Parameter	Type	Description
target	JSON	The JSON document to search within
candidate	JSON	The JSON value to look for (must be valid JSON)
path	VARCHAR	Optional JSONPath to narrow the search to a sub-document

Examples

Scalar contained at path

sql

SELECT JSON_CONTAINS('{"a":1,"b":2}', '1', '$.a');

→-- 1

Value in array

sql

SELECT JSON_CONTAINS('[1,2,3]', '2');

→-- 1

Value not found

sql

SELECT JSON_CONTAINS('[1,2,3]', '5');

→-- 0

Filter rows by tag

sql

SELECT * FROM products WHERE JSON_CONTAINS(tags, '"sale"');

→-- matching rows

Object containment at path

sql

SELECT JSON_CONTAINS('{"a":{"b":1}}', '{"b":1}', '$.a');

→-- 1

⚠Anti-Pattern— Passing an unquoted string as the candidate

The candidate argument must be valid JSON — passing a bare string like 'sale' instead of '"sale"' causes an error.

✓ Instead: Always pass properly quoted JSON: '"sale"' for a string, '42' for a number, 'true' for a boolean.

JSON_CONTAINS can leverage a generated column index on the JSON path being searched, making it significantly faster than a LIKE '%value%' scan. Pair it with a generated column and index for frequent membership checks.

example

SELECT id FROM products WHERE JSON_CONTAINS(tags, '"electronics"', '
    
    
    
  

);

→rows where the tags JSON array includes the string 'electronics'

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL uses the @> containment operator on jsonb (e.g. doc @> '{"a":1}').

JSON_CONTAINS_PATH

MariaDB 10.2+→ INT (1 or 0)

Returns 1 if the JSON document contains data at any or all of the specified paths.

Signature

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path]...)

Parameters

Parameter	Type	Description
json_doc	JSON	The JSON document to inspect
one_or_all	VARCHAR	'one' returns 1 if at least one path exists; 'all' requires all paths to exist
path	VARCHAR	One or more JSONPath expressions to check

Examples

Path exists

sql

SELECT JSON_CONTAINS_PATH('{"a":1}', 'one', '$.a');

→-- 1

Path absent

sql

SELECT JSON_CONTAINS_PATH('{"a":1}', 'one', '$.b');

→-- 0

Check both keys exist

sql

SELECT JSON_CONTAINS_PATH(data, 'all', '$.name', '$.email') FROM users;

→-- 1 or 0 per row

OR logic across two paths

sql

SELECT JSON_CONTAINS_PATH(data, 'one', '$.addr', '$.location') FROM contacts;

→-- 1 if either key exists

Filter by key presence

sql

SELECT * FROM orders WHERE JSON_CONTAINS_PATH(meta, 'one', '$.discount');

→-- rows with discount key

⚠Anti-Pattern— Using JSON_CONTAINS_PATH to check a value, not a path

JSON_CONTAINS_PATH only tests key/path existence, not whether the value at that path matches anything.

✓ Instead: Use JSON_CONTAINS to test for a specific value at a path.

The second argument controls whether ANY of the listed paths must exist ('one') or ALL of them must exist ('all'). 'one' is equivalent to an OR check; 'all' is an AND check across multiple paths.

example

SELECT id FROM documents WHERE JSON_CONTAINS_PATH(data, 'all', '$.name', '$.email', '$.phone');

→rows where data has ALL three keys present

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL uses the ? operator for top-level key existence and jsonb_path_exists() for deeper paths.

JSON_SEARCH

MariaDB 10.2+→ JSON (path string or array of paths)

Searches a JSON document for a given string value and returns the path(s) where it was found.

Signature

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path]])

Parameters

Parameter	Type	Description
json_doc	JSON	The JSON document to search
one_or_all	VARCHAR	'one' returns the first matching path; 'all' returns all matching paths as a JSON array
search_str	VARCHAR	The string to search for; supports % and _ wildcards
escape_char	VARCHAR	Optional escape character for the search string (default is backslash)
path	VARCHAR	Optional path to restrict the search scope

Examples

First occurrence

sql

SELECT JSON_SEARCH('["a","b","a"]', 'one', 'a');

→-- "$[0]"

All occurrences

sql

SELECT JSON_SEARCH('["a","b","a"]', 'all', 'a');

→-- ["$[0]", "$[2]"]

Find key by value

sql

SELECT JSON_SEARCH('{"name":"Alice"}', 'one', 'Alice');

→-- "$.name"

Wildcard search

sql

SELECT JSON_SEARCH(data, 'one', '%admin%') FROM users;

→-- path or NULL

Find path then remove

sql

SELECT JSON_REMOVE(data, JSON_UNQUOTE(JSON_SEARCH(data, 'one', 'old_val'))) FROM t;

→-- doc with value removed

⚠Anti-Pattern— Searching for non-string values

JSON_SEARCH only works on JSON string values — it cannot find numbers, booleans, or null values.

✓ Instead: For non-string values, use JSON_CONTAINS or iterate with JSON_EXTRACT on known paths.

JSON_SEARCH with 'one' mode stops at the first match and is faster than 'all' mode when you only need to confirm presence. Use 'all' mode when you need every matching path, such as for audit or transform operations.

example

SELECT id, JSON_SEARCH(tags, 'one', 'sql') FROM articles WHERE JSON_SEARCH(tags, 'one', 'sql') IS NOT NULL;

→id: 3, JSON_SEARCH: '$.tags[0]'

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL uses jsonb_path_query() with SQL/JSON path expressions.

JSON_KEYS

MariaDB 10.2+→ JSON (array of key names)

Returns the keys of the top-level object (or the object at the given path) as a JSON array of strings.

Signature

JSON_KEYS(json_doc[, path])

Parameters

Parameter	Type	Description
json_doc	JSON	The JSON document to inspect
path	VARCHAR	Optional path to a nested object whose keys to return

Examples

Top-level keys

sql

SELECT JSON_KEYS('{"a":1,"b":2}');

→-- ["a", "b"]

Nested object keys

sql

SELECT JSON_KEYS('{"a":{"x":1,"y":2}}', '$.a');

→-- ["x", "y"]

Arrays return NULL

sql

SELECT JSON_KEYS('[1,2,3]');

→-- NULL

Inspect document structure

sql

SELECT JSON_KEYS(data) FROM config WHERE id = 1;

→-- ["host", "port", "db"]

Count top-level keys

sql

SELECT JSON_LENGTH(JSON_KEYS(data)) AS num_keys FROM config;

→-- 3

⚠Anti-Pattern— Calling JSON_KEYS on an array

JSON_KEYS returns NULL for JSON arrays — it only applies to JSON objects.

✓ Instead: Use JSON_LENGTH to count array elements or JSON_EXTRACT with $[*] to enumerate array values.

JSON_KEYS returns the top-level keys of a JSON object as a JSON array. When your schema evolves and columns contain documents with varying keys, JSON_KEYS lets you discover which keys are present without a schema change.

example

SELECT id, JSON_KEYS(metadata) AS field_names FROM records WHERE JSON_KEYS(metadata) IS NOT NULL LIMIT 3;

→id: 1, field_names: '["created","source","priority"]'

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL uses jsonb_object_keys() to return keys as a set of rows.

JSON_LENGTH

MariaDB 10.2+→ INT

Returns the number of elements in a JSON array, keys in a JSON object, or 1 for a scalar.

Signature

JSON_LENGTH(json_doc[, path])

Parameters

Parameter	Type	Description
json_doc	JSON	The JSON document to measure
path	VARCHAR	Optional path to a sub-document to measure instead

Examples

Array length

sql

SELECT JSON_LENGTH('[1,2,3]');

→-- 3

Object key count

sql

SELECT JSON_LENGTH('{"a":1,"b":2}');

→-- 2

Scalar always returns 1

sql

SELECT JSON_LENGTH('"hello"');

→-- 1

Length of nested array

sql

SELECT JSON_LENGTH(data, '$.tags') FROM posts;

→-- 4

Filter by array size

sql

SELECT * FROM orders WHERE JSON_LENGTH(items) > 5;

→-- large orders

⚠Anti-Pattern— Using JSON_LENGTH to check if a document is empty without guarding NULL

JSON_LENGTH('{}') returns 0 and JSON_LENGTH('[]') returns 0, but JSON_LENGTH(NULL) returns NULL — missing the NULL case leads to unexpected query results.

✓ Instead: Use COALESCE(JSON_LENGTH(col), 0) = 0 to treat NULL documents as empty.

JSON_LENGTH returns the count of elements for JSON arrays and the count of keys for JSON objects. For scalar values it returns 1. Use it to quickly filter documents with more than N items without deserializing the full structure.

example

SELECT id FROM orders WHERE JSON_LENGTH(items) > 5;

→orders with more than 5 line items

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL uses jsonb_array_length() for arrays and jsonb_object_keys() count for objects.

JSON_DEPTH

MariaDB 10.2+→ INT

Returns the maximum nesting depth of a JSON document.

Signature

JSON_DEPTH(json_doc)

Parameters

Parameter	Type	Description
json_doc	JSON	The JSON document to analyse

Examples

Empty object = depth 1

sql

SELECT JSON_DEPTH('{}');

→-- 1

Array of scalars = depth 2

sql

SELECT JSON_DEPTH('[1,2,3]');

→-- 2

Three levels of nesting

sql

SELECT JSON_DEPTH('{"a":{"b":{"c":1}}}');

→-- 4

Scalar = depth 1

sql

SELECT JSON_DEPTH('42');

→-- 1

Find most deeply nested documents

sql

SELECT id, JSON_DEPTH(data) FROM documents ORDER BY 2 DESC LIMIT 5;

→-- deepest docs

⚠Anti-Pattern— Using JSON_DEPTH to count array elements

JSON_DEPTH measures nesting levels, not element count — a flat array of 1000 elements has depth 2.

✓ Instead: Use JSON_LENGTH to count elements.

JSON_DEPTH returns 1 for scalars, 2 for a flat array or object, and higher for nested structures. Use JSON_DEPTH in a CHECK constraint or validation query to prevent deeply nested documents that degrade parsing performance.

example

SELECT id FROM configs WHERE JSON_DEPTH(settings) > 4;

→rows with unexpectedly deep nesting — candidates for schema review

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL has no direct equivalent; use a recursive function or extension.

JSON_TYPE

MariaDB 10.2+→ VARCHAR

Returns a string indicating the JSON type of a value: OBJECT, ARRAY, STRING, INTEGER, DOUBLE, BOOLEAN, NULL, or others.

Signature

JSON_TYPE(json_val)

Parameters

Parameter	Type	Description
json_val	JSON	The JSON value whose type to determine

Examples

Object type

sql

SELECT JSON_TYPE('{"a":1}');

→-- OBJECT

Array type

sql

SELECT JSON_TYPE('[1,2]');

→-- ARRAY

String type

sql

SELECT JSON_TYPE('"hello"');

→-- STRING

Integer type

sql

SELECT JSON_TYPE('42');

→-- INTEGER

Check extracted value type

sql

SELECT JSON_TYPE(JSON_EXTRACT(data, '$.score')) FROM results;

→-- INTEGER or DOUBLE

⚠Anti-Pattern— Comparing JSON_TYPE result case-insensitively

JSON_TYPE returns uppercase strings; comparisons are case-sensitive in binary collation contexts.

✓ Instead: Always compare against the exact uppercase literals: 'OBJECT', 'ARRAY', 'STRING', 'INTEGER', 'DOUBLE', 'BOOLEAN', 'NULL'.

JSON_TYPE returns one of: 'OBJECT', 'ARRAY', 'STRING', 'INTEGER', 'DOUBLE', 'DECIMAL', 'BOOLEAN', 'NULL'. Checking the type before operating prevents type-mismatch errors when JSON columns contain mixed structures.

example

SELECT id, CASE JSON_TYPE(payload) WHEN 'ARRAY' THEN JSON_LENGTH(payload) ELSE NULL END AS item_count FROM events;

→item_count populated only for rows where payload is an array

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL uses jsonb_typeof() which returns lowercase type names like 'object', 'array', 'string', 'number', 'boolean', 'null'.

JSON_VALID

MariaDB 10.2+→ INT (1 or 0)

Returns 1 if the argument is valid JSON, 0 if it is invalid, and NULL if the argument is NULL.

Signature

JSON_VALID(val)

Parameters

Parameter	Type	Description
val	VARCHAR / JSON	The value to test for JSON validity

Examples

Valid JSON object

sql

SELECT JSON_VALID('{"a":1}');

→-- 1

Valid JSON array

sql

SELECT JSON_VALID('[1,2,3]');

→-- 1

Invalid JSON

sql

SELECT JSON_VALID('not json');

→-- 0

NULL input returns NULL

sql

SELECT JSON_VALID(NULL);

→-- NULL

Find rows with bad JSON

sql

SELECT * FROM raw_imports WHERE JSON_VALID(payload) = 0;

→-- invalid rows

⚠Anti-Pattern— Skipping JSON_VALID before parsing untrusted input

Calling JSON_EXTRACT or JSON_SET on invalid JSON causes an error that can crash stored procedures or application queries.

✓ Instead: Validate with JSON_VALID() first, especially for data ingested from external sources.

JSON columns in MariaDB are stored as LONGTEXT with a JSON_VALID constraint applied. Calling JSON_VALID() manually in application code before an INSERT prevents round-trip failures and provides a clear error message.

example

SELECT JSON_VALID(?) AS is_valid;  -- bind parameter from application

→1 if valid JSON, 0 if not

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Identical in MySQL 5.7+.

⇄ vs PostgreSQL: PostgreSQL validates JSON implicitly on cast to json/jsonb; use a TRY CAST or error-handling block for explicit validation.

JSON_DETAILED

MariaDB 10.2.4+→ LONGTEXT

Returns a human-readable, indented (pretty-printed) representation of a JSON document.

Signature

JSON_DETAILED(json_doc[, tab_size])

Parameters

Parameter	Type	Description
json_doc	JSON	The JSON document to format
tab_size	INT	Number of spaces per indent level (default 4)

Examples

Default 4-space indent

sql

SELECT JSON_DETAILED('{"a":1,"b":[1,2]}');

→-- multi-line indented JSON

Custom indent width

sql

SELECT JSON_DETAILED(data, 2) FROM config WHERE id = 1;

→-- 2-space indent

Format constructed object

sql

SELECT JSON_DETAILED(JSON_OBJECT('name','Alice','age',30));

→-- pretty object

Nested structure

sql

SELECT JSON_DETAILED('[1,[2,3],{"x":4}]');

→-- nested indented

Useful in mysql CLI with \G

sql

SELECT JSON_DETAILED(col) FROM t LIMIT 1 \G

→-- readable in terminal

⚠Anti-Pattern— Storing JSON_DETAILED output back into the column

The extra whitespace inflates storage size significantly for large documents.

✓ Instead: Use JSON_DETAILED only for display/debugging; store and transmit compact JSON.

JSON_DETAILED reformats a compact JSON document with indentation and newlines, making it easy to inspect in a terminal or log file. It is equivalent to JSON_PRETTY in MySQL and is intended for development/debugging only.

example

SELECT JSON_DETAILED(config) FROM app_settings WHERE env = 'production' LIMIT 1;

→pretty-printed multi-line JSON output

◆MariaDB Note

MariaDB-specific function; MySQL equivalent is JSON_PRETTY().

⇄ vs MySQL: MySQL uses JSON_PRETTY() instead; JSON_DETAILED() is MariaDB-specific.

⇄ vs PostgreSQL: PostgreSQL uses jsonb_pretty() for indented output.

JSON_COMPACT

MariaDB 10.2.4+→ LONGTEXT

Returns a compact representation of a JSON document with all unnecessary whitespace removed.

Signature

JSON_COMPACT(json_doc)

Parameters

Parameter	Type	Description
json_doc	JSON	The JSON document to compact

Examples

Whitespace stripped

sql

SELECT JSON_COMPACT('{  "a" :  1 ,  "b" :  2  }');

→-- {"a":1,"b":2}

Round-trip pretty then compact

sql

SELECT JSON_COMPACT(JSON_DETAILED(data)) FROM config;

→-- original compact form

Normalise stored JSON in-place

sql

UPDATE docs SET content = JSON_COMPACT(content) WHERE JSON_LENGTH(content) > 0;

→-- rows updated

Measure compacted size

sql

SELECT CHAR_LENGTH(JSON_COMPACT(data)) AS compact_size FROM t;

→-- byte count

Array compaction

sql

SELECT JSON_COMPACT(' [ 1 , 2 , 3 ] ');

→-- [1,2,3]

⚠Anti-Pattern— Applying JSON_COMPACT repeatedly on reads in a hot query path

JSON_COMPACT parses and re-serialises the document on every call, adding CPU overhead that is unnecessary if the data is already compact.

✓ Instead: Compact data once at write time and store it; avoid repeated compaction on reads.

JSON_COMPACT removes all unnecessary whitespace from a JSON document, reducing storage size. Run it on data imported from external APIs or user input that may contain inconsistent formatting.

example

UPDATE configs SET data = JSON_COMPACT(data) WHERE LENGTH(data) > LENGTH(JSON_COMPACT(data));

→updated rows where whitespace was trimmed

◆MariaDB Note

MariaDB-specific function with no MySQL equivalent.

⇄ vs MySQL: MySQL has no JSON_COMPACT(); this is a MariaDB-specific function.

⇄ vs PostgreSQL: PostgreSQL jsonb type is always stored in a normalised binary form; no explicit compact function needed.

JSON_LOOSE

MariaDB 10.2.4+→ LONGTEXT

Returns a loosely formatted JSON document with spaces after colons and commas for light readability.

Signature

JSON_LOOSE(json_doc)

Parameters

Parameter	Type	Description
json_doc	JSON	The JSON document to format loosely

Examples

Space after colon and comma

sql

SELECT JSON_LOOSE('{"a":1,"b":2}');

→-- {"a": 1, "b": 2}

Spaced array

sql

SELECT JSON_LOOSE('[1,2,3]');

→-- [1, 2, 3]

From JSON_OBJECT

sql

SELECT JSON_LOOSE(JSON_OBJECT('x', 1, 'y', 2));

→-- {"x": 1, "y": 2}

Column formatting

sql

SELECT JSON_LOOSE(data) FROM settings WHERE id = 1;

→-- lightly formatted JSON

Nested structure

sql

SELECT JSON_LOOSE('{"arr":[1,2,{"k":"v"}]}');

→-- {"arr": [1, 2, {"k": "v"}]}

⚠Anti-Pattern— Confusing JSON_LOOSE with JSON_DETAILED for deep debugging

JSON_LOOSE adds spaces but does not add newlines or indentation — deeply nested structures remain on one long line.

✓ Instead: Use JSON_DETAILED when you need full indented multi-line output for inspection.

JSON_LOOSE adds spaces after colons and commas for readability without full indentation. It produces a single-line readable format suitable for logs where full JSON_DETAILED output would be too verbose.

example

SELECT JSON_LOOSE('{"a":1,"b":2}');

→{"a": 1, "b": 2}

◆MariaDB Note

MariaDB-specific function with no MySQL equivalent.

⇄ vs MySQL: MySQL has no JSON_LOOSE(); this is a MariaDB-specific function.

⇄ vs PostgreSQL: PostgreSQL has no equivalent; jsonb output is either compact or pretty (jsonb_pretty()).

JSON_PRETTY

MariaDB 10.5.9+ / 10.6.1+→ LONGTEXT

Returns a pretty-printed JSON document with newlines and indentation, providing MySQL-compatible pretty output.

Signature

JSON_PRETTY(json_doc)

Parameters

Parameter	Type	Description
json_doc	JSON	The JSON document to pretty-print

Examples

Standard pretty-print

sql

SELECT JSON_PRETTY('{"a":1,"b":[1,2]}');

→-- indented multi-line JSON

Pretty print a column

sql

SELECT JSON_PRETTY(data) FROM config WHERE id = 1;

→-- human-readable config

Pretty-print constructed object

sql

SELECT JSON_PRETTY(JSON_OBJECT('host','localhost','port',5432));

→-- indented object

NULL passthrough

sql

SELECT JSON_PRETTY(NULL);

→-- NULL

Array of objects

sql

SELECT JSON_PRETTY('[{"id":1},{"id":2}]');

→-- array of objects, indented

⚠Anti-Pattern— Storing JSON_PRETTY output in application variables for large result sets

Storing pretty-printed JSON in application memory bloats RAM usage for large result sets.

✓ Instead: Apply JSON_PRETTY only in the final display layer; operate on compact JSON internally.

JSON_PRETTY was added to MariaDB for MySQL compatibility. Both JSON_PRETTY and JSON_DETAILED produce indented output, but JSON_DETAILED is the original MariaDB function. Use JSON_PRETTY in code that must run on both MySQL and MariaDB.

example

SELECT JSON_PRETTY('{"id":1,"name":"Alice"}');

→

{
  "id": 1,
  "name": "Alice"
}

◆MariaDB Note

Functionally equivalent to JSON_DETAILED with default indent; added for MySQL compatibility.

⇄ vs MySQL: Available in MySQL 8.0+ with the same signature — use JSON_PRETTY for cross-compatibility.

⇄ vs PostgreSQL: PostgreSQL uses jsonb_pretty() on jsonb values.

JSON_ARRAYAGG

MariaDB 10.5.0+→ JSON

Aggregate function that collects values from multiple rows into a single JSON array.

Signature

JSON_ARRAYAGG(val [ORDER BY col])

Parameters

Parameter	Type	Description
val	ANY	The expression or column to collect into the JSON array

Examples

Collect all names

sql

SELECT JSON_ARRAYAGG(name) FROM users;

→-- ["Alice","Bob","Carol"]

Grouped and ordered

sql

SELECT dept_id, JSON_ARRAYAGG(name ORDER BY name) FROM employees GROUP BY dept_id;

→-- per-department sorted arrays

Distinct values only

sql

SELECT JSON_ARRAYAGG(DISTINCT status) FROM orders;

→-- ["pending","shipped"]

Array of objects

sql

SELECT JSON_ARRAYAGG(JSON_OBJECT('id',id,'name',name)) FROM products;

→-- [{"id":1,"name":"..."},...]

Ordered numeric array

sql

SELECT JSON_ARRAYAGG(score ORDER BY score DESC) FROM scores WHERE user_id = 1;

→-- [98, 87, 75]

⚠Anti-Pattern— Using JSON_ARRAYAGG without GROUP BY on large tables

Without GROUP BY, JSON_ARRAYAGG collects every row in the table into one array in memory, which can exhaust server RAM.

✓ Instead: Always pair with GROUP BY or use LIMIT / a subquery to cap the input set.

Without ORDER BY, the order of elements in the resulting JSON array is unspecified. Specify ORDER BY inside the aggregate to guarantee a stable output order, which is important for caching and regression testing.

example

SELECT category, JSON_ARRAYAGG(name ORDER BY name ASC) AS items FROM products GROUP BY category;

→["Gadget", "Gizmo", "Widget"] (alphabetically ordered)

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Available in MySQL 5.7.22+ with the same syntax.

⇄ vs PostgreSQL: PostgreSQL uses json_agg() or jsonb_agg(); ordering uses json_agg(val ORDER BY col).

JSON_OBJECTAGG

MariaDB 10.5.0+→ JSON

Aggregate function that builds a JSON object from key-value pairs across multiple rows.

Signature

JSON_OBJECTAGG(key, value)

Parameters

Parameter	Type	Description
key	VARCHAR	Expression used as the JSON object key (should be unique per group)
value	ANY	Expression used as the corresponding JSON object value

Examples

Key-value pairs from rows

sql

SELECT JSON_OBJECTAGG(code, value) FROM settings;

→-- {"theme":"dark","lang":"en"}

Grouped pivot

sql

SELECT dept, JSON_OBJECTAGG(name, salary) FROM staff GROUP BY dept;

→-- per-dept salary map

Nested object values

sql

SELECT JSON_OBJECTAGG(id, JSON_OBJECT('name',name,'age',age)) FROM users;

→-- {"1":{"name":"Alice",...}}

Build translation map

sql

SELECT JSON_OBJECTAGG(lang, translation) FROM i18n WHERE key = 'greeting';

→-- {"en":"Hello","fr":"Bonjour"}

Daily KPI map

sql

SELECT JSON_OBJECTAGG(metric, val) FROM kpis WHERE date = CURDATE();

→-- {"revenue":5000,"orders":42}

⚠Anti-Pattern— Assuming duplicate keys are handled deterministically

If multiple rows produce the same key, JSON_OBJECTAGG produces a document with duplicate keys — the behaviour is undefined and results vary by version.

✓ Instead: Ensure keys are unique before aggregation, using DISTINCT on the key expression or pre-filtering.

JSON_OBJECTAGG silently uses the last value when duplicate keys appear. If your key column might have duplicates within a group, add a DISTINCT or pre-aggregate to ensure each key appears once.

example

SELECT JSON_OBJECTAGG(setting_key, setting_value) FROM user_settings WHERE user_id = 1;

→{"theme": "dark", "lang": "en", "notifications": "1"}

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Available in MySQL 5.7.22+ with the same syntax.

⇄ vs PostgreSQL: PostgreSQL uses json_object_agg(key, value) or jsonb_object_agg(key, value).

JSON_TABLE

MariaDB 10.6+→ TABLE (relational rowset)

Shreds a JSON document into a relational table, mapping JSON paths to columns with optional type coercion and error handling.

Signature

JSON_TABLE(json_doc, path COLUMNS (col_def[, col_def]...)) [AS] alias

Parameters

Parameter	Type	Description
json_doc	JSON	The JSON document or column to shred
path	VARCHAR	JSONPath to the array element to iterate over (e.g. '$[*]')
col_def	keyword	Column definitions using FOR ORDINALITY, PATH, EXISTS PATH, or NESTED PATH clauses

Examples

Array of objects to rows

sql

SELECT * FROM JSON_TABLE('[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]', '$[*]' COLUMNS(id INT PATH '$.id', name VARCHAR(50) PATH '$.name')) AS jt;

→

-- id | name
-- 1  | Alice
-- 2  | Bob

Lateral join to explode order items

sql

SELECT jt.* FROM orders, JSON_TABLE(orders.items, '$[*]' COLUMNS(sku VARCHAR(20) PATH '$.sku', qty INT PATH '$.qty')) AS jt;

→-- one row per order item

Scalar array to rows

sql

SELECT n FROM JSON_TABLE('[1,2,3,4,5]', '$[*]' COLUMNS(n INT PATH '
    
    
    
  

)) AS jt;

→

With row ordinal number

sql

SELECT * FROM JSON_TABLE(data, '$[*]' COLUMNS(ord FOR ORDINALITY, v VARCHAR(100) PATH '
    
    
    
  

)) AS jt;

→-- ord | v

Extract nested array

sql

SELECT * FROM JSON_TABLE('{"users":[{"id":1},{"id":2}]}', '$.users[*]' COLUMNS(id INT PATH '$.id')) AS jt;

→

-- 1
2

⚠Anti-Pattern— Using JSON_TABLE on a non-array path

If the path expression does not point to an array, JSON_TABLE returns at most one row, which is often not what is intended.

✓ Instead: Ensure the path ends with [*] or points to a JSON array; use JSON_TYPE to verify beforehand.

JSON_TABLE converts a JSON array into a virtual relational table inline. This allows full SQL operations (JOIN, WHERE, GROUP BY) against JSON data without application-side parsing. It is the most powerful way to consume JSON arrays from an API or log column.

example

SELECT jt.* FROM orders, JSON_TABLE(items, '$[*]' COLUMNS (sku VARCHAR(20) PATH '$.sku', qty INT PATH '$.qty')) AS jt WHERE order_id = 42;

→sku: 'ABC-1', qty: 3 | sku: 'XYZ-9', qty: 1

◆MariaDB Note

Introduced in MariaDB 10.6 following the SQL/JSON standard.

⇄ vs MySQL: Available in MySQL 8.0+ with the same SQL/JSON standard syntax.

⇄ vs PostgreSQL: PostgreSQL uses json_to_recordset() or jsonb_to_recordset() for similar array-to-rows conversion.

JSON_EXISTS

MariaDB 10.6.1+→ INT (1 or 0)

Returns 1 if the SQL/JSON path expression identifies any value in the JSON document, 0 otherwise.

Signature

JSON_EXISTS(json_doc, path [on_error])

Parameters

Parameter	Type	Description
json_doc	JSON	The JSON document to query
path	VARCHAR	SQL/JSON path expression to check for existence
on_error	keyword	Optional: TRUE ON ERROR, FALSE ON ERROR, or UNKNOWN ON ERROR (default UNKNOWN)

Examples

Key exists

sql

SELECT JSON_EXISTS('{"a":1}', '$.a');

→-- 1

Key absent

sql

SELECT JSON_EXISTS('{"a":1}', '$.b');

→-- 0

Filter by key presence

sql

SELECT * FROM orders WHERE JSON_EXISTS(meta, '$.promo_code');

→-- orders with promo

Out-of-bounds index

sql

SELECT JSON_EXISTS('[1,2,3]', '$[5]');

→-- 0

Strict mode with error handling

sql

SELECT JSON_EXISTS(data, 'strict $.required_field' FALSE ON ERROR) FROM docs;

→-- 0 on invalid JSON

⚠Anti-Pattern— Using JSON_EXISTS on MariaDB versions before 10.6.1

JSON_EXISTS is only available from MariaDB 10.6.1+; using it on earlier versions causes a function-not-found error.

✓ Instead: Use JSON_CONTAINS_PATH for compatibility with MariaDB 10.2–10.5 installations.

JSON_EXISTS returns TRUE/FALSE and short-circuits on first match. It is slightly more concise than JSON_CONTAINS_PATH(..., 'one', path) and maps directly to the SQL/JSON standard, making code more portable.

example

SELECT id FROM documents WHERE JSON_EXISTS(data, '$.billing_address');

→rows where billing_address key is present in data

◆MariaDB Note

MariaDB-specific implementation of the SQL/JSON standard JSON_EXISTS predicate.

⇄ vs MySQL: Not available in MySQL; MySQL uses JSON_CONTAINS_PATH() instead.

⇄ vs PostgreSQL: PostgreSQL uses jsonb_path_exists() with the same SQL/JSON standard path syntax.

JSON_SCHEMA_VALID

MariaDB 10.6.1+→ INT (1 or 0)

Validates a JSON document against a JSON Schema (draft-07) and returns 1 if valid, 0 otherwise.

Signature

JSON_SCHEMA_VALID(schema, document)

Parameters

Parameter	Type	Description
schema	JSON	A valid JSON Schema document (draft-07) describing the expected structure
document	JSON	The JSON document to validate against the schema

Examples

Required property present

sql

SELECT JSON_SCHEMA_VALID('{"type":"object","required":["name"]}', '{"name":"Alice"}');

→-- 1

Required property missing

sql

SELECT JSON_SCHEMA_VALID('{"type":"object","required":["name"]}', '{"age":30}');

→-- 0

Scalar validation

sql

SELECT JSON_SCHEMA_VALID('{"type":"integer","minimum":0}', '42');

→-- 1

Enforce schema at DDL level

sql

ALTER TABLE orders ADD CONSTRAINT chk_meta CHECK (JSON_SCHEMA_VALID('{"type":"object"}', meta));

→-- constraint added

Find schema-violating rows

sql

SELECT id FROM uploads WHERE JSON_SCHEMA_VALID(@schema, payload) = 0;

→-- invalid rows

⚠Anti-Pattern— Using very large or deeply recursive schemas

Highly complex JSON Schema documents with $ref cycles or allOf/anyOf chains can cause significant validation overhead per row.

✓ Instead: Keep schemas focused on essential constraints and validate complex business rules in the application layer.

JSON_SCHEMA_VALID validates a JSON document against a JSON Schema. Adding it as a CHECK constraint on a JSON column enforces structure at the database level, catching malformed documents on INSERT/UPDATE.

example

ALTER TABLE events ADD CONSTRAINT chk_payload CHECK (JSON_SCHEMA_VALID('{"type":"object","required":["type","ts"]}', payload));

→INSERT rejected if payload lacks 'type' or 'ts' keys

◆MariaDB Note

Available in all MariaDB versions.

⇄ vs MySQL: Available in MySQL 8.0.17+ with the same JSON Schema draft-07 support.

⇄ vs PostgreSQL: PostgreSQL has no built-in JSON Schema validation; use the pg_jsonschema extension.

JSON_NORMALIZE

MariaDB 10.7+→ LONGTEXT

Returns a normalised canonical form of a JSON document: keys sorted alphabetically, whitespace removed, and number representation standardised.

Signature

JSON_NORMALIZE(json_doc)

Parameters

Parameter	Type	Description
json_doc	JSON	The JSON document to normalise

Examples

Keys sorted alphabetically

sql

SELECT JSON_NORMALIZE('{"b":2,"a":1}');

→-- {"a":1,"b":2}

Whitespace removed

sql

SELECT JSON_NORMALIZE('{ "a" :  1 }');

→-- {"a":1}

Number normalisation (1.0 becomes 1)

sql

SELECT JSON_NORMALIZE('{"n":1.0}');

→-- {"n":1}

Semantic equality check

sql

SELECT JSON_NORMALIZE(a) = JSON_NORMALIZE(b) AS equal FROM comparisons;

→-- 1 or 0

Generate stable content hash

sql

SELECT MD5(JSON_NORMALIZE(data)) AS fingerprint FROM configs;

→-- stable hash

⚠Anti-Pattern— Relying on JSON_NORMALIZE to sort array elements

JSON_NORMALIZE sorts object keys but does not sort array elements — two arrays with the same elements in different order will not normalise to the same string.

✓ Instead: Sort array contents in the application layer before storing if order-independent array equality is required.

JSON_NORMALIZE sorts object keys and removes insignificant whitespace. Two JSON documents representing the same data compare equal after normalization, making it safe to use = or checksum comparisons on JSON columns.

example

SELECT JSON_NORMALIZE('{"b":2,"a":1}') = JSON_NORMALIZE('{"a":1,"b":2}');

→1 (true — same content, different key order)

◆MariaDB Note

MariaDB-specific function; particularly useful for content-addressable caching and change detection.

⇄ vs MySQL: Not available in MySQL; this is a MariaDB-specific function.

⇄ vs PostgreSQL: PostgreSQL jsonb type automatically normalises key order and number representation on storage.

Related MariaDB Categories

Aggregate Functions →String Functions →Control Flow →

MariaDB JSON Functions

Jump to function

What are MariaDB JSON Functions?

JSON_EXTRACT

Signature

Parameters

Examples

JSON_VALUE

Signature

Parameters

Examples

JSON_SET

Signature

Parameters

Examples

JSON_INSERT

Signature

Parameters

Examples

JSON_REPLACE

Signature

Parameters

Examples

JSON_REMOVE

Signature

Parameters

Examples

JSON_MERGE

Signature

Parameters

Examples

JSON_MERGE_PATCH

Signature

Parameters

Examples

JSON_MERGE_PRESERVE

Signature

Parameters

Examples

JSON_ARRAY

Signature

Parameters

Examples

JSON_OBJECT

Signature

Parameters

Examples

JSON_QUOTE

Signature

Parameters

Examples

JSON_UNQUOTE

Signature

Parameters

Examples

JSON_CONTAINS

Signature

Parameters

Examples

JSON_CONTAINS_PATH

Signature

Parameters

Examples

JSON_SEARCH

Signature

Parameters

Examples

JSON_KEYS

Signature

Parameters

Examples

JSON_LENGTH

Signature

Parameters

Examples

JSON_DEPTH

Signature

Parameters

Examples

JSON_TYPE