JSON_ARRAY()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Returns a JSON array created from the argument list.
USAGE
JSON_ARRAY([value[, value ...])
Argument Name | Description |
|---|---|
| Zero or more values used to populate the array |
DETAILS
JSON_ARRAY() is a JSON construction function that returns a JSON array created from SQL data.
A JSON array is a string representation of a list of values that can be reliably parsed and interpreted:
The representation begins with
[and ends with]Multiple array items are separated by commas
Each individual value can be a double-quoted string, a number, a JSON object, a JSON array, a JSON boolean (
true&false), or the JSONnullvalueJSON constants are case-sensitive, and are always lower case
Space characters outside of double quotes are ignored
SQL constants are translated into JSON constants when present. However, keep in mind that a column with a BOOLEAN type stores TINYINT values that will require conversion into SQL boolean constants if JSON boolean constants are desired in the resulting array.
If JSON_ARRAY() is given no arguments, the result is an empty JSON array
EXAMPLES
With Literal Values
In the following example, JSON_ARRAY() is called with several literal values. The result demonstrates how the values are transformed into a JSON array:
SET @esc_val = '\\a"complex"\t/string';
SELECT @esc_val, JSON_ARRAY(@esc_val, 42, 'another string') AS result\G
*************************** 1. row ***************************
@esc_val: \a"complex" /string
result: ["\\a\"complex\"\t/string", 42, "another string"]
SELECT JSON_ARRAY('a string',
NULL,
JSON_ARRAY(1, 2, 3),
FALSE
) AS result;
+--------------------------------------+
| result |
+--------------------------------------+
| ["a string", null, [1, 2, 3], false] |
+--------------------------------------+
Example Schema and Data
Some of the examples are based on the inventory table:
CREATE TABLE inventory (
item VARCHAR(20),
category VARCHAR(20),
count INT,
on_sale BOOLEAN
);
INSERT INTO inventory VALUES
('Foo','in stock', 42, TRUE), ('Foo','back order', 50, FALSE),
('Bar','in stock', 10, FALSE), ('Bar','back order', 60, TRUE),
('Baz','in stock', 99, TRUE), ('Baz','in stock', 10, FALSE);
Per-row Values
When used in a SELECT, a JSON array could be used to group information into a single string. Note that the SQL BOOLEAN field must be converted into actual TRUE & FALSE values to get JSON boolean constants instead of 0 and 1:
SELECT JSON_ARRAY(item, count, IF(on_sale, TRUE, FALSE)) AS inventory_status
FROM inventory
WHERE category = 'in stock' AND count > 5;
+--------------------+
| inventory_status |
+--------------------+
| ["Foo", 42, true] |
| ["Bar", 10, false] |
| ["Baz", 99, true] |
| ["Baz", 10, false] |
+--------------------+
If the boolean field could also be NULL and you want that distinction preserved, the conversion could be done in this slightly extended manner:
IF(on_sale IS NULL, NULL, IF(on_sale, TRUE, FALSE))