14.17.8 JSON Utility Functions — MySQL 9.2

14.17.8 Функции для работы с JSON

В этом разделе описаны служебные функции, которые работают с JSON-значениями или строками, которые могут быть распарсены как JSON-значения. JSON_PRETTY() выводит JSON-значение в формате, удобном для чтения. JSON_STORAGE_SIZE() и JSON_STORAGE_FREE() соответственно показывают объем занимаемого места в памяти заданным JSON-значением и объем свободного места в JSON столбце после частичного обновления.

JSON_PRETTY(json_val)

Обеспечивает форматированный вывод JSON-значений, аналогично тому, как это реализовано в PHP и других языках и системах баз данных. Предоставляемое значение должно быть JSON-значением или корректным строковым представлением JSON-значения. Излишние пробелы и новые строки в этом значении не влияют на вывод. Для NULL значения функция возвращает NULL. Если значение не является JSON-документом или не может быть распарсено как таковой, функция возвращает ошибку.

Форматирование вывода этой функции соответствует следующим правилам:
- Каждый элемент массива или член объекта отображается на отдельной строке, отступлена на один уровень по сравнению с родительским элементом.
- Каждый уровень отступа добавляет два ведущих пробела.
- Запятая, разделяющая отдельные элементы массива или члены объекта, печатается перед новой строкой, разделяющей два элемента или члена.
- Ключ и значение члена объекта разделяются двоеточием и пробелом (': ').
- Пустой объект или массив выводятся на одной строке. Пробел не печатается между открывающей и закрывающей фигурной скобкой.
- Специальные символы в строковых скалярах и именах ключей экранируются с использованием тех же правил, что и функция JSON_QUOTE().
```
mysql> SELECT JSON_PRETTY('123'); # scalar
+--------------------+
| JSON_PRETTY('123') |
+--------------------+
| 123                |
+--------------------+

mysql> SELECT JSON_PRETTY("[1,3,5]"); # array
+------------------------+
| JSON_PRETTY("[1,3,5]") |
+------------------------+
| [
  1,
  3,
  5
]      |
+------------------------+

mysql> SELECT JSON_PRETTY('{"a":"10","b":"15","x":"25"}'); # object
+---------------------------------------------+
| JSON_PRETTY('{"a":"10","b":"15","x":"25"}') |
+---------------------------------------------+
| {
  "a": "10",
  "b": "15",
  "x": "25"
}   |
+---------------------------------------------+

mysql> SELECT JSON_PRETTY('["a",1,{"key1":
    '>    "value1"},"5",     "77" ,
    '>       {"key2":["value3","valueX",
    '> "valueY"]},"j", "2"   ]')\G  # nested arrays and objects
*************************** 1. row ***************************
JSON_PRETTY('["a",1,{"key1":
             "value1"},"5",     "77" ,
                {"key2":["value3","valuex",
          "valuey"]},"j", "2"   ]'): [
  "a",
  1,
  {
    "key1": "value1"
  },
  "5",
  "77",
  {
    "key2": [
      "value3",
      "valuex",
      "valuey"
    ]
  },
  "j",
  "2"
]
```
JSON_STORAGE_FREE(json_val)

Для значения столбца JSON эта функция показывает, сколько места в памяти было освобождено в его двоичном представлении после его обновления на месте с помощью JSON_SET(), JSON_REPLACE() или JSON_REMOVE(). Аргумент также может быть корректным JSON-документом или строкой, которую можно распарсить как таковой — как буквальное значение, так и значение пользовательской переменной — в этом случае функция возвращает 0. Функция возвращает положительное, ненулевое значение, если аргумент является значением JSON столбца, который был обновлён, как описано ранее, таким образом, что его двоичное представление занимает меньше места, чем до обновления. Для JSON столбца, который был обновлён таким образом, что его двоичное представление равно или больше, чем раньше, или если обновление не смогло воспользоваться частичным обновлением, она возвращает 0; она возвращает NULL, если аргумент является NULL.

Если json_val не NULL, и не является ни корректным JSON-документом, ни может быть успешно распарсено как таковой, возникает ошибка.

В этом примере мы создаём таблицу, содержащую JSON столбец, затем вставляем строку, содержащую JSON-объект:
```
mysql> CREATE TABLE jtable (jcol JSON);
Query OK, 0 rows affected (0.38 sec)

mysql> INSERT INTO jtable VALUES
    ->     ('{"a": 10, "b": "wxyz", "c": "[true, false]"}');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM jtable;
+----------------------------------------------+
| jcol                                         |
+----------------------------------------------+
| {"a": 10, "b": "wxyz", "c": "[true, false]"} |
+----------------------------------------------+
1 row in set (0.00 sec)
```
Теперь мы обновляем значение столбца, используя JSON_SET() таким образом, что может быть выполнено частичное обновление; в данном случае, мы заменяем значение, на которое указывает c ключ (массив [true, false]), на значение, занимающее меньше места (целое число 1):
```
mysql> UPDATE jtable
    ->     SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wxyz", "$.c", 1);
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM jtable;
+--------------------------------+
| jcol                           |
+--------------------------------+
| {"a": 10, "b": "wxyz", "c": 1} |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------+
| JSON_STORAGE_FREE(jcol) |
+-------------------------+
|                      14 |
+-------------------------+
1 row in set (0.00 sec)
```
Эффекты последовательных частичных обновлений на это свободное место являются кумулятивными, как показано в этом примере, использующем JSON_SET(), чтобы уменьшить место, занимаемое значением с ключом b (и не внося других изменений):
```
mysql> UPDATE jtable
    ->     SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wx", "$.c", 1);
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------+
| JSON_STORAGE_FREE(jcol) |
+-------------------------+
|                      16 |
+-------------------------+
1 row in set (0.00 sec)
```
Обновление столбца без использования JSON_SET(), JSON_REPLACE() или JSON_REMOVE() означает, что оптимизатор не может выполнить обновление на месте; в этом случае JSON_STORAGE_FREE() возвращает 0, как показано здесь:
```
mysql> UPDATE jtable SET jcol = '{"a": 10, "b": 1}';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------+
| JSON_STORAGE_FREE(jcol) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)
```
Частичные обновления JSON-документов могут быть выполнены только для значений столбцов. Для пользовательской переменной, хранящей JSON-значение, значение всегда полностью заменяется, даже когда обновление выполняется с помощью JSON_SET():
```
mysql> SET @j = '{"a": 10, "b": "wxyz", "c": "[true, false]"}';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @j = JSON_SET(@j, '$.a', 10, '$.b', 'wxyz', '$.c', '1');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_FREE(@j) AS Free;
+----------------------------------+------+
| @j                               | Free |
+----------------------------------+------+
| {"a": 10, "b": "wxyz", "c": "1"} |    0 |
+----------------------------------+------+
1 row in set (0.00 sec)
```
Для JSON-литерала эта функция всегда возвращает 0:
```
mysql> SELECT JSON_STORAGE_FREE('{"a": 10, "b": "wxyz", "c": "1"}') AS Free;
+------+
| Free |
+------+
|    0 |
+------+
1 row in set (0.00 sec)
```
JSON_STORAGE_SIZE(json_val)

Эта функция возвращает количество байтов, используемых для хранения двоичного представления JSON-документа. Когда аргументом является JSON столбец, это место, используемое для хранения JSON-документа, как он был вставлен в столбец, до любых частичных обновлений, которые могли быть выполнены на нем позже. json_val должен быть корректным JSON-документом или строкой, которая может быть распарсена как таковой. В случае, если это строка, функция возвращает количество места в памяти в двоичном представлении JSON, которое создается при парсинге строки как JSON и преобразовании ее в двоичный вид. Она возвращает NULL, если аргумент является NULL.

Возникает ошибка, когда json_val не NULL и не является — или не может быть успешно распарсен как — JSON-документом.

Чтобы проиллюстрировать поведение этой функции, когда она используется со JSON столбцом в качестве аргумента, мы создаём таблицу с именем jtable, содержащую JSON столбец jcol, вставляем JSON-значение в таблицу, а затем получаем занимаемое место в памяти этого столбца с помощью JSON_STORAGE_SIZE(), как показано здесь:
```
mysql> CREATE TABLE jtable (jcol JSON);
Query OK, 0 rows affected (0.42 sec)

mysql> INSERT INTO jtable VALUES
    ->     ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT
    ->     jcol,
    ->     JSON_STORAGE_SIZE(jcol) AS Size,
    ->     JSON_STORAGE_FREE(jcol) AS Free
    -> FROM jtable;
+-----------------------------------------------+------+------+
| jcol                                          | Size | Free |
+-----------------------------------------------+------+------+
| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   47 |    0 |
+-----------------------------------------------+------+------+
1 row in set (0.00 sec)
```
Согласно выводу JSON_STORAGE_SIZE(), JSON-документ, вставленный в столбец, занимает 47 байтов. Мы также проверили количество памяти, освобожденной любыми предыдущими частичными обновлениями столбца, используя JSON_STORAGE_FREE(); поскольку никаких обновлений ещё не было выполнено, это 0, как ожидалось.

Далее мы выполним UPDATE над таблицей, которая должна привести к частичному обновлению документа, хранящегося в jcol, и затем проверим результат, как показано здесь:
```
mysql> UPDATE jtable SET jcol = 
    ->     JSON_SET(jcol, "$.b", "a");
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT
    ->     jcol,
    ->     JSON_STORAGE_SIZE(jcol) AS Size,
    ->     JSON_STORAGE_FREE(jcol) AS Free
    -> FROM jtable;
+--------------------------------------------+------+------+
| jcol                                       | Size | Free |
+--------------------------------------------+------+------+
| {"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"} |   47 |    3 |
+--------------------------------------------+------+------+
1 row in set (0.00 sec)
```
Значение, возвращаемое JSON_STORAGE_FREE() в предыдущем запросе, указывает, что было выполнено частичное обновление JSON-документа, и что это освободило 3 байта памяти, используемой для его хранения. Результат, возвращаемый JSON_STORAGE_SIZE(), не изменяется частичным обновлением.

Частичные обновления поддерживаются для обновлений с помощью JSON_SET(), JSON_REPLACE() или JSON_REMOVE(). Прямая присваивание значения JSON столбцу не может быть частично обновлено; после такого обновления JSON_STORAGE_SIZE() всегда показывает место, используемое для нового значения:
```
mysql> UPDATE jtable
mysql>     SET jcol = '{"a": 4.55, "b": "wxyz", "c": "[true, false]"}';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT
    ->     jcol,
    ->     JSON_STORAGE_SIZE(jcol) AS Size,
    ->     JSON_STORAGE_FREE(jcol) AS Free
    -> FROM jtable;
+------------------------------------------------+------+------+
| jcol                                           | Size | Free |
+------------------------------------------------+------+------+
| {"a": 4.55, "b": "wxyz", "c": "[true, false]"} |   56 |    0 |
+------------------------------------------------+------+------+
1 row in set (0.00 sec)
```
Пользовательская переменная JSON не может быть частично обновлена. Это означает, что эта функция всегда показывает место, которое в настоящее время используется для хранения JSON-документа в пользовательской переменной:
```
mysql> SET @j = '[100, "sakila", [1, 3, 5], 425.05]';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+------------------------------------+------+
| @j                                 | Size |
+------------------------------------+------+
| [100, "sakila", [1, 3, 5], 425.05] |   45 |
+------------------------------------+------+
1 row in set (0.00 sec)

mysql> SET @j = JSON_SET(@j, '$[1]', "json");
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+----------------------------------+------+
| @j                               | Size |
+----------------------------------+------+
| [100, "json", [1, 3, 5], 425.05] |   43 |
+----------------------------------+------+
1 row in set (0.00 sec)

mysql> SET @j = JSON_SET(@j, '$[2][0]', JSON_ARRAY(10, 20, 30));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+---------------------------------------------+------+
| @j                                          | Size |
+---------------------------------------------+------+
| [100, "json", [[10, 20, 30], 3, 5], 425.05] |   56 |
+---------------------------------------------+------+
1 row in set (0.00 sec)
```
Для JSON-литерала эта функция всегда возвращает текущее занимаемое место:
```
mysql> SELECT
    ->     JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
    ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
    ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C,
    ->     JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;
+----+----+----+----+
| A  | B  | C  | D  |
+----+----+----+----+
| 45 | 44 | 47 | 56 |
+----+----+----+----+
1 row in set (0.00 sec)
```