Тип таблицы CONNECT JSON

Обзор

JSON (JavaScript Object Notation) — это лёгкий формат обмена данными, широко используемый в Интернете. Многие приложения, как правило, написанные на JavaScript или PHP, используют и генерируют данные JSON, которые обмениваются как файлы разных физических форматов. Данные JSON часто возвращаются из запросов REST.

Также можно запрашивать, создавать или обновлять такую информацию по принципу базы данных. MongoDB делает это с помощью языка, похожего на JavaScript. PostgreSQL включает эти возможности, используя определённый тип данных и связанные функции, такие как динамические столбцы.

Двигатель CONNECT добавляет эту возможность в MariaDB, поддерживая таблицы, основанные на файлах с данными JSON. Это делается так же, как для XML-таблиц, создавая таблицы, описывающие то, что должно быть извлечено из файла и как он должен обрабатываться.

Начиная с версии 1.07.0002, внутренний способ парсинга и обработки JSON был изменён. Основное преимущество нового способа заключается в уменьшении памяти, необходимой для парсинга JSON. Раньше требовалось от 6 до 10 раз больше памяти, чем объём исходного файла JSON, а теперь только в 2-4 раза больше. Однако этот режим находится в стадии бета-тестирования, и JSON-таблицы по-прежнему обрабатываются в старом режиме. Для использования нового режима таблицы должны создаваться с TABLE_TYPE=BSON. Другой способ — установить системную переменную connect_force_bson в 1 или ON. Тогда все JSON-таблицы будут обрабатываться как BSON. Конечно, это временное решение, и когда оно будет успешно протестировано, новый метод заменит старый, и все таблицы будут создаваться как JSON.

Давайте начнём с файла “biblio3.json”, который является JSON-эквивалентом XML-файла Xsample, описанного в главе о XML-таблицах:

[
  {
    "ISBN": "9782212090819",
    "LANG": "fr",
    "SUBJECT": "applications",
    "AUTHOR": [
      {
        "FIRSTNAME": "Jean-Christophe",
        "LASTNAME": "Bernadac"
      },
      {
        "FIRSTNAME": "François",
        "LASTNAME": "Knab"
      }
    ],
    "TITLE": "Construire une application XML",
    "PUBLISHER": {
      "NAME": "Eyrolles",
      "PLACE": "Paris"
    },
    "DATEPUB": 1999
  },
  {
    "ISBN": "9782840825685",
    "LANG": "fr",
    "SUBJECT": "applications",
    "AUTHOR": [
      {
        "FIRSTNAME": "William J.",
        "LASTNAME": "Pardi"
      }
    ],
    "TITLE": "XML en Action",
    "TRANSLATED": {
       "PREFIX": "adapté de l'anglais par",
       "TRANSLATOR": {
          "FIRSTNAME": "James",
        "LASTNAME": "Guerin"
        }
    },
    "PUBLISHER": {
      "NAME": "Microsoft Press",
      "PLACE": "Paris"
    },
    "DATEPUB": 1999
  }
]

Этот файл содержит различные элементы, существующие в формате JSON.

• Arrays: Они заключены в квадратные скобки и содержат список значений, разделённых запятыми.
• Objects: Они заключены в фигурные скобки. Они содержат список пар, разделённых запятыми, каждая пара состоит из имени ключа в двойных кавычках, за которым следует символ ‘:’, а затем значение.
• Values: Значения могут быть массивом или объектом. Они также могут быть строкой в двойных кавычках, целым или дробным числом, логическим значением или значением null. Самый простой способ для CONNECT найти таблицу в таком файле — массив, содержащий список объектов (это то, что MongoDB называет коллекцией документов). Каждое значение массива будет строкой таблицы, а каждая пара объектов строки будет представлять столбец, ключом являясь имя столбца, а значением — значение столбца.

Первая попытка создать таблицу на основе этого файла будет заключаться в том, чтобы использовать внешний массив в качестве таблицы:

create table jsample (
ISBN char(15),
LANG char(2),
SUBJECT char(32),
AUTHOR char(128),
TITLE char(32),
TRANSLATED char(80),
PUBLISHER char(20),
DATEPUB int(4))
engine=CONNECT table_type=JSON
File_name='biblio3.json';

Если мы выполним запрос:

select isbn, author, title, publisher from jsample;

Мы получим результат:

Обратите внимание, что по умолчанию значения столбцов, которые являются объектами, были установлены как конкатенация всех строковых значений объекта, разделённых пробелом. Когда значение столбца является массивом, извлекается только первый элемент массива (это изменится в будущих версиях Connect).

Однако, ситуация обычно сложнее. Если JSON-файлы не содержат атрибутов (хотя пары объектов похожи на атрибуты), они содержат новый элемент — массивы. Мы видели, что они могут использоваться как несколько узлов XML, здесь для указания нескольких авторов, но они более универсальны, так как могут содержать объекты разных типов, хотя это может быть нежелательно.

Вот почему CONNECT позволяет указать опцию столбца field_format «JPATH» (FIELD_FORMAT до Connect 1.6), которая используется для точного описания того, где находятся элементы для отображения и как обрабатывать массивы.

Вот пример новой таблицы, которая может быть создана в том же файле, позволяя выбирать имена столбцов, получать некоторые вложенные объекты и определять способ обработки массива авторов.

До Connect 1.5:

create table jsampall (
ISBN char(15),
Language char(2) field_format='LANG',
Subject char(32) field_format='SUBJECT',
Author char(128) field_format='AUTHOR:[" and "]',
Title char(32) field_format='TITLE',
Translation char(32) field_format='TRANSLATOR:PREFIX',
Translator char(80) field_format='TRANSLATOR',
Publisher char(20) field_format='PUBLISHER:NAME',
Location char(16) field_format='PUBLISHER:PLACE',
Year int(4) field_format='DATEPUB')
engine=CONNECT table_type=JSON File_name='biblio3.json';

Начиная с Connect 1.6:

create table jsampall (
ISBN char(15),
Language char(2) field_format='LANG',
Subject char(32) field_format='SUBJECT',
Author char(128) field_format='AUTHOR.[" and "]',
Title char(32) field_format='TITLE',
Translation char(32) field_format='TRANSLATOR.PREFIX',
Translator char(80) field_format='TRANSLATOR',
Publisher char(20) field_format='PUBLISHER.NAME',
Location char(16) field_format='PUBLISHER.PLACE',
Year int(4) field_format='DATEPUB')
engine=CONNECT table_type=JSON File_name='biblio3.json';

Начиная с Connect 1.07.0002

create table jsampall (
ISBN char(15),
Language char(2) jpath='$.LANG',
Subject char(32) jpath='$.SUBJECT',
Author char(128) jpath='$.AUTHOR[" and "]',
Title char(32) jpath='$.TITLE',
Translation char(32) jpath='$.TRANSLATOR.PREFIX',
Translator char(80) jpath='$.TRANSLATOR',
Publisher char(20) jpath='$.PUBLISHER.NAME',
Location char(16) jpath='$.PUBLISHER.PLACE',
Year int(4) jpath='$.DATEPUB')
engine=CONNECT table_type=JSON File_name='biblio3.json';

При выполнении запроса:

select title, author, publisher, location from jsampall;

Результат:

Примечание: JPATH не был указан для столбца ISBN, потому что он по умолчанию соответствует имени столбца.

Вот другой пример, показывающий, что можно выбрать, что извлечь из файла и как «расширить» массив, то есть сгенерировать одну строку для каждого значения массива:

До Connect 1.5:

create table jsampex (
ISBN char(15),
Title char(32) field_format='TITLE',
AuthorFN char(128) field_format='AUTHOR:[X]:FIRSTNAME',
AuthorLN char(128) field_format='AUTHOR:[X]:LASTNAME',
Year int(4) field_format='DATEPUB')
engine=CONNECT table_type=JSON File_name='biblio3.json';

Начиная с Connect 1.6:

create table jsampex (
ISBN char(15),
Title char(32) field_format='TITLE',
AuthorFN char(128) field_format='AUTHOR.[X].FIRSTNAME',
AuthorLN char(128) field_format='AUTHOR.[X].LASTNAME',
Year int(4) field_format='DATEPUB')
engine=CONNECT table_type=JSON File_name='biblio3.json';

Начиная с Connect 1.06.006:

create table jsampex (
ISBN char(15),
Title char(32) field_format='TITLE',
AuthorFN char(128) field_format='AUTHOR[*].FIRSTNAME',
AuthorLN char(128) field_format='AUTHOR[*].LASTNAME',
Year int(4) field_format='DATEPUB')
engine=CONNECT table_type=JSON File_name='biblio3.json';

Начиная с Connect 1.07.0002

create table jsampex (
ISBN char(15),
Title char(32) jpath='TITLE',
AuthorFN char(128) jpath='AUTHOR[*].FIRSTNAME',
AuthorLN char(128) jpath='AUTHOR[*].LASTNAME',
Year int(4) jpath='DATEPUB')
engine=CONNECT table_type=JSON File_name='biblio3.json';

Вывод:

Примечание: Приведенный выше пример демонстрирует, что префикс ‘$.’, означающий начало пути, может быть опущен.

Спецификация Jpath

До Connect 1.5, это описание пути, которому следует следовать, чтобы достичь требуемого элемента. Каждый шаг — это имя ключа (регистрозависимое) пары при переходе через объект и номер значения в квадратных скобках при переходе через массив. Каждая спецификация разделяется символом ‘:’.

Начиная с Connect 1.6, это описание пути, которому следует следовать, чтобы достичь требуемого элемента. Каждый шаг — это имя ключа (регистрозависимое) пары при переходе через объект и номер позиции значения при переходе через массив. Спецификации ключей разделяются символом ‘.’.

Например, в вышеприведённом файле фамилия второго автора книги достигается путём:

$.AUTHOR[1].LASTNAME стандартный стиль $AUTHOR.1.LASTNAME стиль MongoDB AUTHOR:[1]:LASTNAME старый стиль при SEP_CHAR=’:’ или до Connect 1.5

Префикс ‘$’ или “$.” указывает на корень пути и может быть опущен в CONNECT.

Спецификация массива также может указать, как он должен обрабатываться:

Например, в вышеприведенном файле фамилия второго автора книги достигается путём:

AUTHOR:[1]:LASTNAME

Спецификация массива также может указать, как он должен обрабатываться:

Примечание 1: Когда ограничение LIMIT применимо, используются только первые m элементов массива, где m — значение опции LIMIT (указывается в списке опций). Значение LIMIT по умолчанию — 10.

Примечание 2: Альтернативный способ указать, что нужно расширить, — использовать опцию expand в списке опций, например:

OPTION_LIST='Expand=AUTHOR'

AUTHOR — здесь ключ пары, содержащей массив в качестве значения (регистрозависимый). Расширение ограничено только одной ветвью (расширенные массивы должны находиться в одном объекте).

Давайте возьмём в качестве примера файл expense.json (находится здесь). Таблица jexpall расширяет все элементы внутри и включая массив week:

Начиная с Connect 1.07.0002

create table jexpall (
WHO char(12),
WEEK int(2) jpath='$.WEEK[*].NUMBER',
WHAT char(32) jpath='$.WEEK[*].EXPENSE[*].WHAT',
AMOUNT double(8,2) jpath='$.WEEK[*].EXPENSE[*].AMOUNT')
engine=CONNECT table_type=JSON File_name='expense.json';

Начиная с Connect 1.6

create table jexpall (
WHO char(12),
WEEK int(2) field_format='$.WEEK[*].NUMBER',
WHAT char(32) field_format='$.WEEK[*].EXPENSE[*].WHAT',
AMOUNT double(8,2) field_format='$.WEEK[*].EXPENSE[*].AMOUNT')
engine=CONNECT table_type=JSON File_name='expense.json';

До Connect 1.5:

create table jexpall (
WHO char(12),
WEEK int(2) field_format='WEEK:[x]:NUMBER',
WHAT char(32) field_format='WEEK:[x]:EXPENSE:[x]:WHAT',
AMOUNT double(8,2) field_format='WEEK:[x]:EXPENSE:[x]:AMOUNT')
engine=CONNECT table_type=JSON File_name='expense.json';

Таблица jexpw показывает, что было куплено, а также сумму и среднее значение сумм для каждого человека и недели:

Из Connect 1.07.0002

create table jexpw (
WHO char(12) not null,
WEEK int(2) not null jpath='$.WEEK[*].NUMBER',
WHAT char(32) not null jpath='$.WEEK[].EXPENSE[", "].WHAT',
SUM double(8,2) not null jpath='$.WEEK[].EXPENSE[+].AMOUNT',
AVERAGE double(8,2) not null jpath='$.WEEK[].EXPENSE[!].AMOUNT')
engine=CONNECT table_type=JSON File_name='expense.json';

Из Connect 1.6:

create table jexpw (
WHO char(12) not null,
WEEK int(2) not null field_format='$.WEEK[*].NUMBER',
WHAT char(32) not null field_format='$.WEEK[].EXPENSE[", "].WHAT',
SUM double(8,2) not null field_format='$.WEEK[].EXPENSE[+].AMOUNT',
AVERAGE double(8,2) not null field_format='$.WEEK[].EXPENSE[!].AMOUNT')
engine=CONNECT table_type=JSON File_name='expense.json';

До Connect 1.5:

create table jexpw (
WHO char(12) not null,
WEEK int(2) not null field_format='WEEK:[x]:NUMBER',
WHAT char(32) not null field_format='WEEK::EXPENSE:[", "]:WHAT',
SUM double(8,2) not null field_format='WEEK::EXPENSE:[+]:AMOUNT',
AVERAGE double(8,2) not null field_format='WEEK::EXPENSE:[!]:AMOUNT')
engine=CONNECT table_type=JSON File_name='expense.json';

Давайте посмотрим, что делает таблица jexpz:

Из Connect 1.6:

create table jexpz (
WHO char(12) not null,
WEEKS char(12) not null field_format='WEEK[", "].NUMBER',
SUMS char(64) not null field_format='WEEK["+"].EXPENSE[+].AMOUNT',
SUM double(8,2) not null field_format='WEEK[+].EXPENSE[+].AMOUNT',
AVGS char(64) not null field_format='WEEK["+"].EXPENSE[!].AMOUNT',
SUMAVG double(8,2) not null field_format='WEEK[+].EXPENSE[!].AMOUNT',
AVGSUM double(8,2) not null field_format='WEEK[!].EXPENSE[+].AMOUNT',
AVERAGE double(8,2) not null field_format='WEEK[!].EXPENSE[*].AMOUNT')
engine=CONNECT table_type=JSON File_name='expense.json';

Из Connect 1.07.0002

create table jexpz (
WHO char(12) not null,
WEEKS char(12) not null jpath='WEEK[", "].NUMBER',
SUMS char(64) not null jpath='WEEK["+"].EXPENSE[+].AMOUNT',
SUM double(8,2) not null jpath='WEEK[+].EXPENSE[+].AMOUNT',
AVGS char(64) not null jpath='WEEK["+"].EXPENSE[!].AMOUNT',
SUMAVG double(8,2) not null jpath='WEEK[+].EXPENSE[!].AMOUNT',
AVGSUM double(8,2) not null jpath='WEEK[!].EXPENSE[+].AMOUNT',
AVERAGE double(8,2) not null jpath='WEEK[!].EXPENSE[*].AMOUNT')
engine=CONNECT table_type=JSON File_name='expense.json';

До Connect 1.5:

create table jexpz (
WHO char(12) not null,
WEEKS char(12) not null field_format='WEEK:[", "]:NUMBER',
SUMS char(64) not null field_format='WEEK:["+"]:EXPENSE:[+]:AMOUNT',
SUM double(8,2) not null field_format='WEEK:[+]:EXPENSE:[+]:AMOUNT',
AVGS char(64) not null field_format='WEEK:["+"]:EXPENSE:[!]:AMOUNT',
SUMAVG double(8,2) not null field_format='WEEK:[+]:EXPENSE:[!]:AMOUNT',
AVGSUM double(8,2) not null field_format='WEEK:[!]:EXPENSE:[+]:AMOUNT',
AVERAGE double(8,2) not null field_format='WEEK:[!]:EXPENSE:[x]:AMOUNT')
engine=CONNECT table_type=JSON
File_name='E:/Data/Json/expense2.json';

Для всех лиц:

• В колонке 1 показано имя человека.
• В колонке 2 показаны недели, для которых рассчитываются значения.
• В колонке 3 перечислены суммы расходов за каждую неделю.
• В колонке 4 рассчитывается сумма всех расходов по каждому лицу.
• В колонке 5 показаны средние расходы за неделю.
• В колонке 6 рассчитывается сумма этих средних значений.
• В колонке 7 рассчитывается среднее значение суммы расходов за неделю.
• В колонке 8 рассчитывается средний расход на человека.

Получить этот результат из таблицы jexpall с помощью запроса SQL будет очень сложно, если вообще возможно.

Обработка значений NULL

Json имеет явное значение null, которое может встречаться в массивах или значениях ключей объекта. При рассмотрении json как реляционной таблицы значение столбца может быть null, потому что соответствующий элемент json явным образом равен null или неявно, потому что соответствующий элемент отсутствует в массиве или объекте. CONNECT не различает явные и неявные значения null.

Однако можно указать, как обрабатывать и представлять значения null. Это делается с помощью установки строковой сессионной переменной connect_json_null. Значение по умолчанию для connect_json_null — «<null>»; его можно изменить, например, следующим образом:

SET connect_json_null='NULL';

Это изменяет его представление при отображении текста объекта или конкатенации значений массива.

Также можно указать CONNECT игнорировать значения null, выполнив:

SET connect_json_null=NULL;

В этом случае значения null не отображаются в тексте объекта или списках массивов. Однако это не меняет поведение расчета массивов и результата подсчета элементов массива.

Определение столбцов по обнаружению

Можно позволить процессу обнаружения MariaDB выполнить работу по определению столбцов. Если столбцы не определены в операторе create table, CONNECT пытается проанализировать файл JSON и предоставить спецификации столбцов. Это возможно только для таблиц, представленных массивом объектов, потому что CONNECT извлекает имена столбцов из ключей пар объекта и их определения из значений пар объекта. Например, таблицу jsample можно создать, сказав:

create table jsample engine=connect table_type=JSON file_name='biblio3.json';

Проверим, как она была фактически определена, используя оператор show create table:

CREATE TABLE `jsample` (
  `ISBN` char(13) NOT NULL,
  `LANG` char(2) NOT NULL,
  `SUBJECT` char(12) NOT NULL,
  `AUTHOR` varchar(256) DEFAULT NULL,
  `TITLE` char(30) NOT NULL,
  `TRANSLATED` varchar(256) DEFAULT NULL,
  `PUBLISHER` varchar(256) DEFAULT NULL,
  `DATEPUB` int(4) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='JSON' `FILE_NAME`='biblio3.json';

Это эквивалентно, за исключением размеров столбцов, которые были рассчитаны из файла как максимальная длина соответствующего столбца при нормальном значении. Для столбцов, которые являются массивами или объектами JSON, столбец определяется как строка varchar длиной 256, предположительно достаточно большая для хранения конкатенированных значений подобъектов. Nullable устанавливается в true, если столбец равен null или отсутствует в некоторых строках или если его JPATH содержит массивы.

Если требуется более сложное определение, вы можете попросить CONNECT проанализировать JPATH до заданной глубины, используя параметр DEPTH или LEVEL в списке параметров. Его значение по умолчанию равно 0, но его можно изменить, задав сессионную переменную connect_default_depth (в будущих версиях значение по умолчанию будет 5). Значение глубины — это количество подобъектов, которые берутся в JPATH2 (это отличается от того, что определено и возвращено функцией Json_Depth).

Например:

create table jsampall2 engine=connect table_type=JSON 
  file_name='biblio3.json' option_list='level=1';

Это определит таблицу как:

Из Connect 1.07.0002

CREATE TABLE `jsampall2` (
  `ISBN` char(13) NOT NULL,
  `LANG` char(2) NOT NULL,
  `SUBJECT` char(12) NOT NULL,
  `AUTHOR_FIRSTNAME` char(15) NOT NULL `JPATH`='$.AUTHOR.[0].FIRSTNAME',
  `AUTHOR_LASTNAME` char(8) NOT NULL `JPATH`='$.AUTHOR.[0].LASTNAME',
  `TITLE` char(30) NOT NULL,
  `TRANSLATED_PREFIX` char(23) DEFAULT NULL `JPATH`='$.TRANSLATED.PREFIX',
  `TRANSLATED_TRANSLATOR` varchar(256) DEFAULT NULL `JPATH`='$.TRANSLATED.TRANSLATOR',
  `PUBLISHER_NAME` char(15) NOT NULL `JPATH`='$.PUBLISHER.NAME',
  `PUBLISHER_PLACE` char(5) NOT NULL `JPATH`='$.PUBLISHER.PLACE',
  `DATEPUB` int(4) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='JSON' 
  `FILE_NAME`='biblio3.json' `OPTION_LIST`='depth=1';

Из Connect 1.6:

CREATE TABLE `jsampall2` (
  `ISBN` char(13) NOT NULL,
  `LANG` char(2) NOT NULL,
  `SUBJECT` char(12) NOT NULL,
  `AUTHOR_FIRSTNAME` char(15) NOT NULL `FIELD_FORMAT`='AUTHOR..FIRSTNAME',
  `AUTHOR_LASTNAME` char(8) NOT NULL `FIELD_FORMAT`='AUTHOR..LASTNAME',
  `TITLE` char(30) NOT NULL,
  `TRANSLATED_PREFIX` char(23) DEFAULT NULL `FIELD_FORMAT`='TRANSLATED.PREFIX',
  `TRANSLATED_TRANSLATOR` varchar(256) DEFAULT NULL `FIELD_FORMAT`='TRANSLATED.TRANSLATOR',
  `PUBLISHER_NAME` char(15) NOT NULL `FIELD_FORMAT`='PUBLISHER.NAME',
  `PUBLISHER_PLACE` char(5) NOT NULL `FIELD_FORMAT`='PUBLISHER.PLACE',
  `DATEPUB` int(4) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='JSON' 
  `FILE_NAME`='biblio3.json' `OPTION_LIST`='level=1';

До Connect 1.5:

CREATE TABLE `jsampall2` (
  `ISBN` char(13) NOT NULL,
  `LANG` char(2) NOT NULL,
  `SUBJECT` char(12) NOT NULL,
  `AUTHOR_FIRSTNAME` char(15) NOT NULL `FIELD_FORMAT`='AUTHOR::FIRSTNAME',
  `AUTHOR_LASTNAME` char(8) NOT NULL `FIELD_FORMAT`='AUTHOR::LASTNAME',
  `TITLE` char(30) NOT NULL,
  `TRANSLATED_PREFIX` char(23) DEFAULT NULL `FIELD_FORMAT`='TRANSLATED:PREFIX',
  `TRANSLATED_TRANSLATOR` varchar(256) DEFAULT NULL `FIELD_FORMAT`='TRANSLATED:TRANSLATOR',
  `PUBLISHER_NAME` char(15) NOT NULL `FIELD_FORMAT`='PUBLISHER:NAME',
  `PUBLISHER_PLACE` char(5) NOT NULL `FIELD_FORMAT`='PUBLISHER:PLACE',
  `DATEPUB` int(4) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='JSON' `
  FILE_NAME`='biblio3.json' `OPTION_LIST`='level=1';

Для столбцов, которые являются простым значением, JPATH — это имя столбца. Это значение по умолчанию, когда параметр Jpath не указан, поэтому он не был указан для таких столбцов. Однако вы можете заставить обнаружение указать его, установив переменную connect_all_path в 1 или ON. Это может быть полезно, если вы планируете изменить имена таких столбцов и избавит вас от ручного указания пути (в противном случае он будет установлен по умолчанию на новое имя и приведет к тому, что столбец не будет или неправильно найден).

Другая проблема заключается в том, что CONNECT не может угадать, что вы хотите сделать с массивами. Здесь массив AUTHOR установлен в 0, что означает, что будет извлечено только его первое значение, если вы не укажите также “Expand=AUTHOR” в списке параметров. Но, конечно, вы можете заменить его любым другим значением.

Этот метод может быть использован как быстрый способ создания “шаблона” определения таблицы, который впоследствии можно отредактировать, чтобы получить желаемое определение. В частности, имена столбцов формируются из всех ключей объекта в их пути, чтобы иметь различные имена столбцов. Это можно вручную отредактировать, чтобы получить желаемые имена, при условии, что имена ключей JPATH не изменены.

DEPTH также можно задать значением -1, чтобы создать только столбцы, которые являются простыми значениями (без массивов или объектов). Обычно он имеет значение по умолчанию 0, но это можно изменить, задав переменную connect_default_depth.

Примечание: начиная с версии 1.6.4, CONNECT удаляет столбцы, которые являются «пустыми» или тип которых нельзя определить. Например, с файлом sresto.json:

{"_id":1,"name":"Corner Social","cuisine":"American","grades":[{"grade":"A","score":6}]}
{"_id":2,"name":"La Nueva Clasica Antillana","cuisine":"Spanish","grades":[]}

Ранее, при использовании обнаружения, создание таблицы с помощью:

create table sjr0
engine=connect table_type=JSON file_name='sresto.json'
option_list='Pretty=0,Depth=1' lrecl=128;

Таблица ранее создавалась как:

CREATE TABLE `sjr0` (
  `_id` bigint(1) NOT NULL,
  `name` char(26) NOT NULL,
  `cuisine` char(8) NOT NULL,
  `grades` char(1) DEFAULT NULL,
  `grades_grade` char(1) DEFAULT NULL `JPATH`='$.grades[0].grade',
  `grades_score` bigint(1) DEFAULT NULL `JPATH`='$.grades[0].score'
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='JSON'
  `FILE_NAME`='sresto.json' 
  `OPTION_LIST`='Pretty=0,Depth=1,Accept=1' `LRECL`=128;

Столбец «grades» был добавлен из-за пустого массива в строке 2. Сейчас этот столбец пропускается и больше не отображается (если параметр Accept=1 не добавлен в список параметров).

Таблицы каталога JSON

Другой способ увидеть спецификации столбцов таблицы JSON — использовать таблицу каталога. Например:

create table bibcol engine=connect table_type=JSON file_name='biblio3.json' 
  option_list='level=2' catfunc=columns;
select column_name, type_name type, column_size size, jpath from bibcol;

что возвращает:

Из Connect 1.07.0002:

Из Connect 1.6:

До версии Connect 1.5:

Всё это в основном пригодится при создании таблицы на удалённом файле, который трудно просмотреть.

Поиск таблицы в файле JSON

Учитывая файл «facebook.json»:

{
   "data": [
      {
         "id": "X999_Y999",
         "from": {
            "name": "Tom Brady", "id": "X12"
         },
         "message": "Looking forward to 2010!",
         "actions": [
            {
               "name": "Comment",
               "link": "http://www.facebook.com/X999/posts/Y999"
            },
            {
               "name": "Like",
               "link": "http://www.facebook.com/X999/posts/Y999"
            }
         ],
         "type": "status",
         "created_time": "2010-08-02T21:27:44+0000",
         "updated_time": "2010-08-02T21:27:44+0000"
      },
      {
         "id": "X998_Y998",
         "from": {
            "name": "Peyton Manning", "id": "X18"
         },
         "message": "Where's my contract?",
         "actions": [
            {
               "name": "Comment",
               "link": "http://www.facebook.com/X998/posts/Y998"
            },
            {
               "name": "Like",
               "link": "http://www.facebook.com/X998/posts/Y998"
            }
         ],
         "type": "status",
         "created_time": "2010-08-02T21:27:44+0000",
         "updated_time": "2010-08-02T21:27:44+0000"
      }
   ]
}

Таблица, которую мы хотим проанализировать, представлена массивом значений объекта «data». Вот как это задано в инструкции CREATE TABLE:

С версии Connect 1.07.0002:

create table jfacebook (
`ID` char(10) jpath='id',
`Name` char(32) jpath='from.name',
`MyID` char(16) jpath='from.id',
`Message` varchar(256) jpath='message',
`Action` char(16) jpath='actions..name',
`Link` varchar(256) jpath='actions..link',
`Type` char(16) jpath='type',
`Created` datetime date_format='YYYY-MM-DD\'T\'hh:mm:ss' jpath='created_time',
`Updated` datetime date_format='YYYY-MM-DD\'T\'hh:mm:ss' jpath='updated_time')
engine=connect table_type=JSON file_name='facebook.json' option_list='Object=data,Expand=actions';

С версии Connect 1.6:

create table jfacebook (
`ID` char(10) field_format='id',
`Name` char(32) field_format='from.name',
`MyID` char(16) field_format='from.id',
`Message` varchar(256) field_format='message',
`Action` char(16) field_format='actions..name',
`Link` varchar(256) field_format='actions..link',
`Type` char(16) field_format='type',
`Created` datetime date_format='YYYY-MM-DD\'T\'hh:mm:ss' field_format='created_time',
`Updated` datetime date_format='YYYY-MM-DD\'T\'hh:mm:ss' field_format='updated_time')
engine=connect table_type=JSON file_name='facebook.json' option_list='Object=data,Expand=actions';

До версии Connect 1.5:

create table jfacebook (
`ID` char(10) field_format='id',
`Name` char(32) field_format='from:name',
`MyID` char(16) field_format='from:id',
`Message` varchar(256) field_format='message',
`Action` char(16) field_format='actions::name',
`Link` varchar(256) field_format='actions::link',
`Type` char(16) field_format='type',
`Created` datetime date_format='YYYY-MM-DD\'T\'hh:mm:ss' field_format='created_time',
`Updated` datetime date_format='YYYY-MM-DD\'T\'hh:mm:ss' field_format='updated_time')
engine=connect table_type=JSON file_name='facebook.json' option_list='Object=data,Expand=actions';

Это вариант объекта, который задаёт Jpath таблицы. Обратите также внимание на альтернативный способ объявления массива, расширяемый параметром expand опции option_list.

Поскольку некоторые строковые значения содержат представление даты, соответствующие столбцы объявляются как datetime, а для них задан формат даты.

Jpath параметра объекта имеет тот же синтаксис, что и Jpath столбца, но, конечно, все шаги массива должны быть указаны в формате [n] (до версии Connect 1.5) или n (с версии Connect 1.6).

Примечание: Это относится ко всему документу для таблиц, содержащих PRETTY = 2 (см. ниже). В противном случае это относится к объектам документа каждого файла записей.

Форматы файлов JSON

Приведённые ранее примеры — это файлы, которые, хотя и могут быть отформатированы различными способами (пробелы, табуляции, возвраты каретки и переводы строк игнорируются при их разборе), соблюдают синтаксис JSON и состоят только из одного элемента (объекта или массива). Как и в случае с XML-файлами, они полностью анализируются, и создаётся представление в памяти, используемое для их обработки. Это подразумевает, что их размер разумный, чтобы избежать ситуации «недостаточно памяти». Таблицы, основанные на таких файлах, распознаются параметром Pretty=2, который мы не указывали выше, поскольку это значение по умолчанию.

Альтернативный формат, который является форматом экспортированных файлов MongoDB, — это файл, где каждая строка физически хранится в одной записи файла. Например:

{ "_id" : "01001", "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA" }
{ "_id" : "01002", "city" : "CUSHMAN", "loc" : [ -72.51564999999999, 42.377017 ], "pop" : 36963, "state" : "MA" }
{ "_id" : "01005", "city" : "BARRE", "loc" : [ -72.1083540000001, 42.409698 ], "pop" : 4546, "state" : "MA" }
{ "_id" : "01007", "city" : "BELCHERTOWN", "loc" : [ -72.4109530000001, 42.275103 ], "pop" : 10579, "state" : "MA" }
…
{ "_id" : "99929", "city" : "WRANGELL", "loc" : [ -132.352918, 56.433524 ], "pop" : 2573, "state" : "AK" }
{ "_id" : "99950", "city" : "KETCHIKAN", "loc" : [ -133.18479, 55.942471 ], "pop" : 422, "state" : "AK" }

Исходный файл «cities.json» содержит 29352 записи. Для создания таблицы на основе этого файла необходимо указать параметр Pretty=0 в списке параметров. Например:

С версии Connect 1.07.0002:

create table cities (
`_id` char(5) key,
`city` char(32),
`lat` double(12,6) jpath='loc.0',
`long` double(12,6) jpath='loc.1',
`pop` int(8),
`state` char(2) distrib='clustered')
engine=CONNECT table_type=JSON file_name='cities.json' lrecl=128 option_list='pretty=0';

С версии Connect 1.6:

create table cities (
`_id` char(5) key,
`city` char(32),
`lat` double(12,6) field_format='loc.0',
`long` double(12,6) field_format='loc.1',
`pop` int(8),
`state` char(2) distrib='clustered')
engine=CONNECT table_type=JSON file_name='cities.json' lrecl=128 option_list='pretty=0';

До версии Connect 1.5:

create table cities (
`_id` char(5) key,
`city` char(32),
`long` double(12,6) field_format='loc:[0]',
`lat` double(12,6) field_format='loc:[1]',
`pop` int(8),
`state` char(2) distrib='clustered')
engine=CONNECT table_type=JSON file_name='cities.json' lrecl=128 option_list='pretty=0';

Обратите внимание на использование спецификаций массивов [n] (до версии Connect 1.5) или n (с версии Connect 1.6) для столбцов longitude и latitude.

При использовании этого формата таблица обрабатывается CONNECT как таблица DOS, CSV или FMT. Строки извлекаются и анализируются по записям, и таблица может быть очень большой. Ещё одним преимуществом является то, что такая таблица может быть индексирована, что может быть очень полезно для очень больших таблиц. Параметр «distrib» столбца «state» сообщает CONNECT использовать индексирование блоков, когда это возможно.

Для таких таблиц, а также для таблиц с pretty=1, размер записи должен быть указан с помощью параметра LRECL. Убедитесь, что вы не задаёте слишком маленькое значение, так как оно используется для выделения буферов чтения/записи и памяти, используемой для анализа строк. В случае сомнений, будьте щедры, так как это не сильно влияет на выделение памяти.

Существует ещё один формат, обозначаемый Pretty=1, который похож на этот, но имеет некоторые дополнения для представления массива JSON. Добавляются заголовок и трейлерные записи, содержащие открывающую и закрывающую квадратную скобку, и все записи, кроме последней, сопровождаются запятой. Он имеет те же преимущества для чтения и обновления, но вставки и удаления выполняются способом pretty=2.

Альтернативная организация таблиц

Мы видели, что наиболее естественный способ представить таблицу в файле JSON — это сделать её массивом объектов. Однако существуют и другие возможности. Таблица может быть массивом массивов, таблица с одним столбцом может быть массивом значений, а таблица с одной строкой может быть просто одним объектом или одним значением. Таблицы с одной строкой обрабатываются внутри добавлением массива с одним значением вокруг них.

Давайте посмотрим, как обработать, например, таблицу, которая является массивом массивов. Файл:

[
  [56, "Coucou", 500.00],
  [[2,0,1,4], "Hello World", 2.0316],
  ["1784", "John Doo", 32.4500],
  [1914, ["Nabucho","donosor"], 5.12],
  [7, "sept", [0.77,1.22,2.01]],
  [8, "huit", 13.0]
]

Таблица может быть создана в этом файле следующим образом:

С версии Connect 1.07.0002:

create table xjson (
`a` int(6) jpath='1',
`b` char(32) jpath='2',
`c` double(10,4) jpath='3')
engine=connect table_type=JSON file_name='test.json' option_list='Pretty=1,Jmode=1,Base=1' lrecl=128;

С версии Connect 1.6:

create table xjson (
`a` int(6) field_format='1',
`b` char(32) field_format='2',
`c` double(10,4) field_format='3')
engine=connect table_type=JSON file_name='test.json' option_list='Pretty=1,Jmode=1,Base=1' lrecl=128;

До версии Connect 1.5:

create table xjson (
`a` int(6) field_format='[1]',
`b` char(32) field_format='[2]',
`c` double(10,4) field_format='[3]')
engine=connect table_type=JSON file_name='test.json'
option_list='Pretty=1,Jmode=1,Base=1' lrecl=128;

Столбцы задаются их позицией в массивах строк. По умолчанию это нумерация с нуля, но для этой таблицы база была установлена в 1 параметром Base списка параметров. Ещё одна новая опция в списке параметров — Jmode=1. Она указывает, какой тип таблицы это.

Значения Jmode:

1. Массив объектов. Это значение по умолчанию.
2. Массив массивов. Как и в этом примере.
3. Массив значений.

При чтении это не требуется, так как тип элементов массива задан для столбцов; однако это требуется при вставке новых строк, чтобы CONNECT знал, что вставлять. Например:

insert into xjson values(25, 'Breakfast', 1.414);

После этого отображается:

Незаданные значения массива представлены их первым элементом.

Получение и установка JSON-представления столбца

Мы видели, что столбцы, соответствующие объекту или массиву Json, извлекаются по умолчанию как конкатенация всех его значений, разделённых пробелами. Также можно извлечь и отобразить содержимое такого столбца как полную JSON-строку, соответствующую ему в файле JSON. Это указывается в JPATH символом «*» там, где объект или массив были бы указаны.

Примечание: При наличии столбцов, сгенерированных с помощью обнаружения, это можно указать, добавив опцию STRINGIFY в ON или 1 в списке параметров.

Например:

С версии Connect 1.07.0002:

create table jsample2 (
ISBN char(15),
Lng char(2) jpath='LANG',
json_Author char(255) jpath='AUTHOR.*',
Title char(32) jpath='TITLE',
Year int(4) jpath='DATEPUB')
engine=CONNECT table_type=JSON file_name='biblio3.json';

С версии Connect 1.6:

create table jsample2 (
ISBN char(15),
Lng char(2) field_format='LANG',
json_Author char(255) field_format='AUTHOR.*',
Title char(32) field_format='TITLE',
Year int(4) field_format='DATEPUB')
engine=CONNECT table_type=JSON file_name='biblio3.json';

До версии Connect 1.5:

create table jsample2 (
ISBN char(15),
Lng char(2) field_format='LANG',
json_Author char(255) field_format='AUTHOR:*',
Title char(32) field_format='TITLE',
Year int(4) field_format='DATEPUB')
engine=CONNECT table_type=JSON file_name='biblio3.json';

Теперь запрос:

select json_Author from jsample2;

вернёт и отобразит:

Примечание: Префикс json_ имени столбца необязателен, но полезен при использовании столбца в качестве аргумента функций Connect UDF, гарантируя, что он будет распознан как действительный JSON без псевдонима.

Это также работает при вводе, когда столбец задан так, что он может быть непосредственно установлен на действительную JSON-строку.

Эта функция очень полезна, как мы увидим ниже.

Операции создания, чтения, обновления и удаления для JSON-таблиц

SQL-команды INSERT, UPDATE и DELETE полностью поддерживаются для JSON-таблиц, за исключением тех, что возвращаются запросами REST. Для INSERT и UPDATE, если целевые значения являются простыми значениями, проблем нет.

Однако существуют проблемы, когда добавленные или изменённые значения являются объектами или массивами.

Что касается объектов, существуют те же проблемы, что и с типом XML. Добавляемый или изменяемый объект будет иметь формат, описанный в определении таблицы, который может отличаться от формата файла JSON. Изменения должны выполняться с помощью файла, указывающего полный путь изменённых объектов.

Возникают новые проблемы при попытке изменить значения массива. Обновления могут выполняться только в исходной таблице. Прежде всего, для того, чтобы значения массива были отличными значениями, все операции обновления, касающиеся значений массива, должны выполняться с использованием таблицы, расширяющей этот массив.

Например, для изменения авторов таблицы biblio.json должна использоваться таблица jsampex. Сделав это, обновление и удаление авторов возможно с помощью стандартных SQL-команд. Например, чтобы изменить имя Knab с François на John:

update jsampex set authorfn = 'John' where authorln = 'Knab';

Однако неправильно делать:

update jsampex set authorfn = 'John' where isbn = '9782212090819';

Потому что это изменит имя обоих авторов, так как у них один и тот же ISBN.

Где становится сложнее, так это когда пытаетесь удалить или вставить автора книги. Действительно, команда DELETE удалит всю книгу, а команда INSERT добавит новую полную строку вместо добавления нового автора в тот же массив. Здесь мы сталкиваемся с ограничением языка SQL, который не позволяет нам это указать. Что-то вроде:

update jsampex add authorfn = 'Charles', authorln = 'Dickens'
where title = 'XML en Action';

Однако этого нет в SQL. Значит ли это, что сделать это невозможно? Нет, но нам нужно использовать таблицу, указанную в том же файле, но адаптированную для этой задачи. Один из способов сделать это — указать таблицу, для которой авторы больше не являются расширенным массивом. Предположим, мы хотим добавить автора к книге «XML en Action». Мы сделаем это в таблице, содержащей только автора(ов) этой книги, которая является второй книгой в таблице.

Из Connect 1.6:

create table jauthor (
FIRSTNAME char(64),
LASTNAME char(64))
engine=CONNECT table_type=JSON File_name='biblio3.json' option_list='Object=1.AUTHOR';

До Connect 1.5

create table jauthor (
FIRSTNAME char(64),
LASTNAME char(64))
engine=CONNECT table_type=JSON File_name='biblio3.json' option_list='Object=[1]:AUTHOR';

Команда:

select * from jauthor;

возвращает:

Это стандартная таблица JSON, которая представляет собой массив объектов, в который мы можем свободно вставлять или удалять строки.

insert into jauthor values('Charles','Dickens');

Мы можем проверить, что это было сделано правильно, выполнив:

select * from jsampex;

Это отобразит:

Примечание: Если эта таблица была большой таблицей со многими книгами, было бы трудно определить порядок конкретной книги в таблице. Это можно найти, добавив специальный столбец ROWID в таблицу.

Однако альтернативный способ сделать это — использовать прямое представление столбца JSON, как в таблице JSAMPLE2. Это можно сделать, выполнив:

update jsample2 set json_Author =
'[{"FIRSTNAME":"William J.","LASTNAME":"Pardi"},
  {"FIRSTNAME":"Charles","LASTNAME":"Dickens"}]'
where isbn = '9782840825685';

В этом случае нам не нужно было находить индекс подмассива для изменения. Однако это не совсем удовлетворительно, потому что нам пришлось вручную написать все значение JSON для установки в столбец json_Author.

Поэтому нам нужны специализированные функции для этого. Они представлены сейчас.

Пользовательские функции JSON

Хотя такие функции, написанные другими сторонами, существуют,^[2] CONNECT предоставляет собственные UDF, специально адаптированные к типу таблицы JSON и легко доступные, потому что, находясь внутри библиотеки или DLL CONNECT, они не требуют загрузки дополнительного модуля (см. CONNECT — Компиляция JSON-UDF в отдельной библиотеке, чтобы создать эти функции в отдельном модуле библиотеки).

В частности, MariaDB 10.2 и 10.3 имеют собственные функции JSON. В некоторых случаях можно использовать эти встроенные функции. Однако смешивание встроенных и UDF-функций JSON в одном запросе часто не работает, потому что способ распознавания ими своих аргументов отличается и может даже привести к сбою сервера.

Вот список функций CONNECT; можно добавить больше, если необходимо.

Строковые значения отображаются в JSON-строках. Эти строки автоматически экранируются для соответствия синтаксису JSON. Автоматическое экранирование пропускается, когда значение имеет псевдоним, начинающийся с ‘json_’. Это автоматически происходит, когда аргумент JSON-UDF является другим JSON-UDF, имя которого начинается с «json_» (регистр не учитывается). Вот почему все функции, которые не возвращают JSON-элемент, не имеют префикса «json_».

Строковые аргументы для некоторых функций могут альтернативно быть именами файлов json. Когда это неоднозначно, используйте псевдоним jfile_. Следует использовать полные пути, так как функции UDF не могут определить текущую базу данных. Похоже, что если путь к имени файла не является полным, он базируется на каталоге данных MariaDB, но я не уверен, что это всегда верно.

Числовые значения представляют собой (большие) целые числа, значения с плавающей точкой двойной точности или десятичные значения. Десятичные значения — это строковые значения, содержащие числовое представление, и они обрабатываются как строки. Значения с плавающей точкой содержат десятичную точку и/или экспоненту. Целые числа записываются без десятичных точек.

Для установки этих функций выполните следующие команды:^[3]

В системах Unix (с Connect 1.7.02):

create function jsonvalue returns string soname 'ha_connect.so';
create function json_make_array returns string soname 'ha_connect.so';
create function json_array_add_values returns string soname 'ha_connect.so';
create function json_array_add returns string soname 'ha_connect.so';
create function json_array_delete returns string soname 'ha_connect.so';
create function json_make_object returns string soname 'ha_connect.so';
create function json_object_nonull returns string soname 'ha_connect.so';
create function json_object_key returns string soname 'ha_connect.so';
create function json_object_add returns string soname 'ha_connect.so';
create function json_object_delete returns string soname 'ha_connect.so';
create function json_object_list returns string soname 'ha_connect.so';
create function json_object_values returns string soname 'ha_connect.so';
create function jsonset_grp_size returns integer soname 'ha_connect.so';
create function jsonget_grp_size returns integer soname 'ha_connect.so';
create aggregate function json_array_grp returns string soname 'ha_connect.so';
create aggregate function json_object_grp returns string soname 'ha_connect.so';
create function jsonlocate returns string soname 'ha_connect.so';
create function json_locate_all returns string soname 'ha_connect.so';
create function jsoncontains returns integer soname 'ha_connect.so';
create function jsoncontains_path returns integer soname 'ha_connect.so';
create function json_item_merge returns string soname 'ha_connect.so';
create function json_get_item returns string soname 'ha_connect.so';
create function jsonget_string returns string soname 'ha_connect.so';
create function jsonget_int returns integer soname 'ha_connect.so';
create function jsonget_real returns real soname 'ha_connect.so';
create function json_set_item returns string soname 'ha_connect.so';
create function json_insert_item returns string soname 'ha_connect.so';
create function json_update_item returns string soname 'ha_connect.so';
create function json_file returns string soname 'ha_connect.so';
create function jfile_make returns string soname 'ha_connect.so';
create function jfile_convert returns string soname 'ha_connect.so';
create function jfile_bjson returns string soname 'ha_connect.so';
create function json_serialize returns string soname 'ha_connect.so';
create function jbin_array returns string soname 'ha_connect.so';
create function jbin_array_add_values returns string soname 'ha_connect.so';
create function jbin_array_add returns string soname 'ha_connect.so';
create function jbin_array_delete returns string soname 'ha_connect.so';
create function jbin_object returns string soname 'ha_connect.so';
create function jbin_object_nonull returns string soname 'ha_connect.so';
create function jbin_object_key returns string soname 'ha_connect.so';
create function jbin_object_add returns string soname 'ha_connect.so';
create function jbin_object_delete returns string soname 'ha_connect.so';
create function jbin_object_list returns string soname 'ha_connect.so';
create function jbin_item_merge returns string soname 'ha_connect.so';
create function jbin_get_item returns string soname 'ha_connect.so';
create function jbin_set_item returns string soname 'ha_connect.so';
create function jbin_insert_item returns string soname 'ha_connect.so';
create function jbin_update_item returns string soname 'ha_connect.so';
create function jbin_file returns string soname 'ha_connect.so';

В системах Unix (с Connect 1.6):

create function jsonvalue returns string soname 'ha_connect.so';
create function json_make_array returns string soname 'ha_connect.so';
create function json_array_add_values returns string soname 'ha_connect.so';
create function json_array_add returns string soname 'ha_connect.so';
create function json_array_delete returns string soname 'ha_connect.so';
create function json_make_object returns string soname 'ha_connect.so';
create function json_object_nonull returns string soname 'ha_connect.so';
create function json_object_key returns string soname 'ha_connect.so';
create function json_object_add returns string soname 'ha_connect.so';
create function json_object_delete returns string soname 'ha_connect.so';
create function json_object_list returns string soname 'ha_connect.so';
create function jsonset_grp_size returns integer soname 'ha_connect.so';
create function jsonget_grp_size returns integer soname 'ha_connect.so';
create aggregate function json_array_grp returns string soname 'ha_connect.so';
create aggregate function json_object_grp returns string soname 'ha_connect.so';
create function jsonlocate returns string soname 'ha_connect.so';
create function json_locate_all returns string soname 'ha_connect.so';
create function jsoncontains returns integer soname 'ha_connect.so';
create function jsoncontains_path returns integer soname 'ha_connect.so';
create function json_item_merge returns string soname 'ha_connect.so';
create function json_get_item returns string soname 'ha_connect.so';
create function jsonget_string returns string soname 'ha_connect.so';
create function jsonget_int returns integer soname 'ha_connect.so';
create function jsonget_real returns real soname 'ha_connect.so';
create function json_set_item returns string soname 'ha_connect.so';
create function json_insert_item returns string soname 'ha_connect.so';
create function json_update_item returns string soname 'ha_connect.so';
create function json_file returns string soname 'ha_connect.so';
create function jfile_make returns string soname 'ha_connect.so';
create function json_serialize returns string soname 'ha_connect.so';
create function jbin_array returns string soname 'ha_connect.so';
create function jbin_array_add_values returns string soname 'ha_connect.so';
create function jbin_array_add returns string soname 'ha_connect.so';
create function jbin_array_delete returns string soname 'ha_connect.so';
create function jbin_object returns string soname 'ha_connect.so';
create function jbin_object_nonull returns string soname 'ha_connect.so';
create function jbin_object_key returns string soname 'ha_connect.so';
create function jbin_object_add returns string soname 'ha_connect.so';
create function jbin_object_delete returns string soname 'ha_connect.so';
create function jbin_object_list returns string soname 'ha_connect.so';
create function jbin_item_merge returns string soname 'ha_connect.so';
create function jbin_get_item returns string soname 'ha_connect.so';
create function jbin_set_item returns string soname 'ha_connect.so';
create function jbin_insert_item returns string soname 'ha_connect.so';
create function jbin_update_item returns string soname 'ha_connect.so';
create function jbin_file returns string soname 'ha_connect.so';

В системах Unix (с MariaDB 10.1.9 до Connect 1.5):

create function jsonvalue returns string soname 'ha_connect.so';
create function json_array returns string soname 'ha_connect.so';
create function json_array_add_values returns string soname 'ha_connect.so';
create function json_array_add returns string soname 'ha_connect.so';
create function json_array_delete returns string soname 'ha_connect.so';
create function json_object returns string soname 'ha_connect.so';
create function json_object_nonull returns string soname 'ha_connect.so';
create function json_object_key returns string soname 'ha_connect.so';
create function json_object_add returns string soname 'ha_connect.so';
create function json_object_delete returns string soname 'ha_connect.so';
create function json_object_list returns string soname 'ha_connect.so';
create function jsonset_grp_size returns integer soname 'ha_connect.so';
create function jsonget_grp_size returns integer soname 'ha_connect.so';
create aggregate function json_array_grp returns string soname 'ha_connect.so';
create aggregate function json_object_grp returns string soname 'ha_connect.so';
create function jsonlocate returns string soname 'ha_connect.so';
create function json_locate_all returns string soname 'ha_connect.so';
create function jsoncontains returns integer soname 'ha_connect.so';
create function jsoncontains_path returns integer soname 'ha_connect.so';
create function json_item_merge returns string soname 'ha_connect.so';
create function json_get_item returns string soname 'ha_connect.so';
create function jsonget_string returns string soname 'ha_connect.so';
create function jsonget_int returns integer soname 'ha_connect.so';
create function jsonget_real returns real soname 'ha_connect.so';
create function json_set_item returns string soname 'ha_connect.so';
create function json_insert_item returns string soname 'ha_connect.so';
create function json_update_item returns string soname 'ha_connect.so';
create function json_file returns string soname 'ha_connect.so';
create function jfile_make returns string soname 'ha_connect.so';
create function json_serialize returns string soname 'ha_connect.so';
create function jbin_array returns string soname 'ha_connect.so';
create function jbin_array_add_values returns string soname 'ha_connect.so';
create function jbin_array_add returns string soname 'ha_connect.so';
create function jbin_array_delete returns string soname 'ha_connect.so';
create function jbin_object returns string soname 'ha_connect.so';
create function jbin_object_nonull returns string soname 'ha_connect.so';
create function jbin_object_key returns string soname 'ha_connect.so';
create function jbin_object_add returns string soname 'ha_connect.so';
create function jbin_object_delete returns string soname 'ha_connect.so';
create function jbin_object_list returns string soname 'ha_connect.so';
create function jbin_item_merge returns string soname 'ha_connect.so';
create function jbin_get_item returns string soname 'ha_connect.so';
create function jbin_set_item returns string soname 'ha_connect.so';
create function jbin_insert_item returns string soname 'ha_connect.so';
create function jbin_update_item returns string soname 'ha_connect.so';
create function jbin_file returns string soname 'ha_connect.so';

В системах Windows (с Connect 1.7.02):

create function jsonvalue returns string soname 'ha_connect';
create function json_make_array returns string soname 'ha_connect';
create function json_array_add_values returns string soname 'ha_connect';
create function json_array_add returns string soname 'ha_connect';
create function json_array_delete returns string soname 'ha_connect';
create function json_make_object returns string soname 'ha_connect';
create function json_object_nonull returns string soname 'ha_connect';
create function json_object_key returns string soname 'ha_connect';
create function json_object_add returns string soname 'ha_connect';
create function json_object_delete returns string soname 'ha_connect';
create function json_object_list returns string soname 'ha_connect';
create function json_object_values returns string soname 'ha_connect';
create function jsonset_grp_size returns integer soname 'ha_connect';
create function jsonget_grp_size returns integer soname 'ha_connect';
create aggregate function json_array_grp returns string soname 'ha_connect';
create aggregate function json_object_grp returns string soname 'ha_connect';
create function jsonlocate returns string soname 'ha_connect';
create function json_locate_all returns string soname 'ha_connect';
create function jsoncontains returns integer soname 'ha_connect';
create function jsoncontains_path returns integer soname 'ha_connect';
create function json_item_merge returns string soname 'ha_connect';
create function json_get_item returns string soname 'ha_connect';
create function jsonget_string returns string soname 'ha_connect';
create function jsonget_int returns integer soname 'ha_connect';
create function jsonget_real returns real soname 'ha_connect';
create function json_set_item returns string soname 'ha_connect';
create function json_insert_item returns string soname 'ha_connect';
create function json_update_item returns string soname 'ha_connect';
create function json_file returns string soname 'ha_connect';
create function jfile_make returns string soname 'ha_connect';
create function jfile_convert returns string soname 'ha_connect';
create function jfile_bjson returns string soname 'ha_connect';
create function json_serialize returns string soname 'ha_connect';
create function jbin_array returns string soname 'ha_connect';
create function jbin_array_add_values returns string soname 'ha_connect';
create function jbin_array_add returns string soname 'ha_connect';
create function jbin_array_delete returns string soname 'ha_connect';
create function jbin_object returns string soname 'ha_connect';
create function jbin_object_nonull returns string soname 'ha_connect';
create function jbin_object_key returns string soname 'ha_connect';
create function jbin_object_add returns string soname 'ha_connect';
create function jbin_object_delete returns string soname 'ha_connect';
create function jbin_object_list returns string soname 'ha_connect';
create function jbin_item_merge returns string soname 'ha_connect';
create function jbin_get_item returns string soname 'ha_connect';
create function jbin_set_item returns string soname 'ha_connect';
create function jbin_insert_item returns string soname 'ha_connect';
create function jbin_update_item returns string soname 'ha_connect';
create function jbin_file returns string soname 'ha_connect';

В системах Windows (с Connect 1.6):

create function jsonvalue returns string soname 'ha_connect';
create function json_make_array returns string soname 'ha_connect';
create function json_array_add_values returns string soname 'ha_connect';
create function json_array_add returns string soname 'ha_connect';
create function json_array_delete returns string soname 'ha_connect';
create function json_make_object returns string soname 'ha_connect';
create function json_object_nonull returns string soname 'ha_connect';
create function json_object_key returns string soname 'ha_connect';
create function json_object_add returns string soname 'ha_connect';
create function json_object_delete returns string soname 'ha_connect';
create function json_object_list returns string soname 'ha_connect';
create function jsonset_grp_size returns integer soname 'ha_connect';
create function jsonget_grp_size returns integer soname 'ha_connect';
create aggregate function json_array_grp returns string soname 'ha_connect';
create aggregate function json_object_grp returns string soname 'ha_connect';
create function jsonlocate returns string soname 'ha_connect';
create function json_locate_all returns string soname 'ha_connect';
create function jsoncontains returns integer soname 'ha_connect';
create function jsoncontains_path returns integer soname 'ha_connect';
create function json_item_merge returns string soname 'ha_connect';
create function json_get_item returns string soname 'ha_connect';
create function jsonget_string returns string soname 'ha_connect';
create function jsonget_int returns integer soname 'ha_connect';
create function jsonget_real returns real soname 'ha_connect';
create function json_set_item returns string soname 'ha_connect';
create function json_insert_item returns string soname 'ha_connect';
create function json_update_item returns string soname 'ha_connect';
create function json_file returns string soname 'ha_connect';
create function jfile_make returns string soname 'ha_connect';
create function json_serialize returns string soname 'ha_connect';
create function jbin_array returns string soname 'ha_connect';
create function jbin_array_add_values returns string soname 'ha_connect';
create function jbin_array_add returns string soname 'ha_connect';
create function jbin_array_delete returns string soname 'ha_connect';
create function jbin_object returns string soname 'ha_connect';
create function jbin_object_nonull returns string soname 'ha_connect';
create function jbin_object_key returns string soname 'ha_connect';
create function jbin_object_add returns string soname 'ha_connect';
create function jbin_object_delete returns string soname 'ha_connect';
create function jbin_object_list returns string soname 'ha_connect';
create function jbin_item_merge returns string soname 'ha_connect';
create function jbin_get_item returns string soname 'ha_connect';
create function jbin_set_item returns string soname 'ha_connect';
create function jbin_insert_item returns string soname 'ha_connect';
create function jbin_update_item returns string soname 'ha_connect';
create function jbin_file returns string soname 'ha_connect';

В системах Windows (до Connect 1.5):

create function jsonvalue returns string soname 'ha_connect';
create function json_array returns string soname 'ha_connect';
create function json_array_add_values returns string soname 'ha_connect';
create function json_array_add returns string soname 'ha_connect';
create function json_array_delete returns string soname 'ha_connect';
create function json_object returns string soname 'ha_connect';
create function json_object_nonull returns string soname 'ha_connect';
create function json_object_key returns string soname 'ha_connect';
create function json_object_add returns string soname 'ha_connect';
create function json_object_delete returns string soname 'ha_connect';
create function json_object_list returns string soname 'ha_connect';
create function jsonset_grp_size returns integer soname 'ha_connect';
create function jsonget_grp_size returns integer soname 'ha_connect';
create aggregate function json_array_grp returns string soname 'ha_connect';
create aggregate function json_object_grp returns string soname 'ha_connect';
create function jsonlocate returns string soname 'ha_connect';
create function json_locate_all returns string soname 'ha_connect';
create function jsoncontains returns integer soname 'ha_connect';
create function jsoncontains_path returns integer soname 'ha_connect';
create function json_item_merge returns string soname 'ha_connect';
create function json_get_item returns string soname 'ha_connect';
create function jsonget_string returns string soname 'ha_connect';
create function jsonget_int returns integer soname 'ha_connect';
create function jsonget_real returns real soname 'ha_connect';
create function json_set_item returns string soname 'ha_connect';
create function json_insert_item returns string soname 'ha_connect';
create function json_update_item returns string soname 'ha_connect';
create function json_file returns string soname 'ha_connect';
create function jfile_make returns string soname 'ha_connect';
create function json_serialize returns string soname 'ha_connect';
create function jbin_array returns string soname 'ha_connect';
create function jbin_array_add_values returns string soname 'ha_connect';
create function jbin_array_add returns string soname 'ha_connect';
create function jbin_array_delete returns string soname 'ha_connect';
create function jbin_object returns string soname 'ha_connect';
create function jbin_object_nonull returns string soname 'ha_connect';
create function jbin_object_key returns string soname 'ha_connect';
create function jbin_object_add returns string soname 'ha_connect';
create function jbin_object_delete returns string soname 'ha_connect';
create function jbin_object_list returns string soname 'ha_connect';
create function jbin_item_merge returns string soname 'ha_connect';
create function jbin_get_item returns string soname 'ha_connect';
create function jbin_set_item returns string soname 'ha_connect';
create function jbin_insert_item returns string soname 'ha_connect';
create function jbin_update_item returns string soname 'ha_connect';
create function jbin_file returns string soname 'ha_connect';

Функция Jfile_Bjson

Jfile_Bjson(in_file_name, out_file_name, lrecl)

Преобразует первый аргумент файл json pretty=0 в файл Bjson. B(inary)json — это предварительно разобранный формат json. Он описан ниже в главе «Производительность» (доступной в следующих версиях Connect).

Jfile_Convert

Jfile_Convert(in_file_name, out_file_name, lrecl)

Преобразует первый аргумент файл json в другой файл json pretty=0. Третий целочисленный аргумент — длина записи для использования. Это часто требуется для обработки огромных файлов json, которые будут очень медленными, если они будут в формате pretty=2.

Это выполняется без полного разбора файла, очень быстро и не требует большого объёма памяти.

Jfile_Make

Jfile_Make(arg1, arg2, [arg3], …)

Первый аргумент должен быть элементом json (если это просто строка, Jfile_Make попытается определить, является ли это элементом json или именем файла входных данных). Следующие аргументы — имя файла в виде строки и целое значение pretty (по умолчанию 2) в любом порядке. Эта функция создаёт файл json, содержащий первый аргумент — элемент.

Возвращаемое строковое значение — имя созданного файла. Если не указано в качестве аргумента, имя файла в некоторых случаях может быть получено из первого аргумента; в таких случаях сам файл изменяется.

Эта функция может использоваться для создания или форматирования файла json. Например, предположим, что мы хотим отформатировать файл tb.json, это можно сделать с помощью запроса:

select Jfile_Make('tb.json' jfile_, 2);

Файл tb.json будет изменён на:

[
  {
    "_id": 5,
    "type": "food",
    "ratings": [
      5,
      8,
      9
    ]
  },
  {
    "_id": 6,
    "type": "car",
    "ratings": [
      5,
      9
    ]
  }
]

Json_Array_Add

Json_Array_Add(arg1, arg2, [arg3][, arg4][, ...])

Примечание: в версии CONNECT 1.3 (до MariaDB 10.1.9) эта функция работала как новая Json_Array_Add_Values функция. Следующее описание этой функции только для версии CONNECT 1.4 (с MariaDB 10.1.9). Первый аргумент должен быть массивом JSON. Второй аргумент добавляется в качестве члена этого массива. Например:

select Json_Array_Add(Json_Array(56,3.1416,'machin',NULL),
'One more') Array;

Примечание: первый массив не экранирован, его (псевдоним) имя начинается с «json_».

Теперь мы можем увидеть, как добавление автора в таблицу JSAMPLE2 можно сделать альтернативно:

update jsample2 set 
  json_author = json_array_add(json_author, json_object('Charles' FIRSTNAME, 'Dickens' LASTNAME)) 
  where isbn = '9782840825685';

Примечание: называние столбца, возвращающего JSON, с префиксом json_ (например, json_author здесь) — хорошая практика и устраняет необходимость давать ему псевдоним для предотвращения экранирования при использовании в качестве аргумента.

Дополнительные аргументы: если задан третий целочисленный аргумент, он указывает позицию (с нуля) добавляемого значения:

select Json_Array_Add('[5,3,8,7,9]' json_, 4, 2) Array;

Если добавлен строковый аргумент, он указывает путь Json к массиву, который нужно изменить. Например:

select Json_Array_Add('{"a":1,"b":2,"c":[3,4]}' json_, 5, 1, 'c');

Json_Array_Add_Values

Json_Array_Add_Values, добавленная в CONNECT 1.4, заменяет функцию Json_Array_Add версии CONNECT 1.3 (до MariaDB 10.1.9).

Json_Array_Add_Values(arg, arglist)

Первый аргумент должен быть строкой массива JSON. Затем все остальные аргументы добавляются в качестве элементов этого массива. Например:

select Json_Array_Add_Values
  (Json_Array(56, 3.1416, 'machin', NULL), 'One more', 'Two more') Array;

Json_Array_Delete

Json_Array_Delete(arg1, arg2 [,arg3] [...])

Первый аргумент должен быть массивом JSON. Второй аргумент — целое число, указывающее ранг (с нуля, в соответствии с общим использованием json) элемента для удаления. Например:

select Json_Array_Delete(Json_Array(56,3.1416,'foo',NULL),1) Array;

Теперь мы можем увидеть, как удалить второго автора из таблицы JSAMPLE2:

update jsample2 set json_author = json_array_delete(json_author, 1) 
  where isbn = '9782840825685';

Путь Json можно указать в качестве третьего строкового аргумента

Json_Array_Grp

Json_Array_Grp(arg)

Это агрегатная функция, которая создаёт массив, заполненный значениями из строк, полученных из запроса. Предположим, у нас есть таблица pet:

Запрос:

select name, json_array_grp(race) from pet group by name;

вернёт:

Одна проблема с функциями агрегации JSON заключается в том, что они строят свой результат в памяти и не могут знать необходимый объём памяти, не зная количество строк используемой таблицы.

Поэтому количество значений для каждой группы ограничено. Это ограничение — значение JsonGrpSize, значение по умолчанию — 10, но его можно установить с помощью функции JsonSet_Grp_Size. Тем не менее, работа с большей таблицей возможна, но только после установки JsonGrpSize до потолка числа строк на группу для таблицы. Старайтесь не устанавливать его в очень большое значение, чтобы избежать исчерпания памяти.

JsonContains

JsonContains(json_doc, item [, int])<

Эта функция может использоваться для проверки, содержится ли элемент в документе. Её аргументы такие же, как у функции JsonLocate; изменяется только значение возврата. Возвращаемое целое значение 1, если элемент содержится в документе, или 0 в противном случае.

JsonContains_Path

JsonContains_Path(json_doc, path)

Эта функция может использоваться для проверки, содержится ли путь Json в документе. Возвращаемое целое значение 1, если путь содержится в документе, или 0 в противном случае.

Json_File

Json_File(arg1, [arg2, [arg3]], …)

Первый аргумент — имя файла. Эта функция возвращает текст файла, который предполагается, что является файлом json. Если указан только один аргумент, текст файла возвращается без разбора. Можно указать до двух дополнительных аргументов:

Строковый аргумент — путь к подэлементу, который нужно вернуть. Целочисленный аргумент — значение pretty-формата файла.

Эта функция главным образом используется для получения аргумента элемента json других функций json из файла json. Например, предположим, что файл tb.json:

{ "_id" : 5, "type" : "food", "ratings" : [ 5, 8, 9 ] }
{ "_id" : 6, "type" : "car", "ratings" : [ 5, 9 ] }

Извлечение значения из него можно осуществить с помощью запроса, такого как:

select JsonGet_String(Json_File('tb.json', 0), '[1]:type') "Type";

или, начиная с MariaDB 10.2.8:

select JsonGet_String(Json_File('tb.json', 0), '$[1].type') "Type";

Этот запрос возвращает:

Однако мы увидим, что в большинстве случаев лучше использовать Jbin_File или непосредственно указывать имя файла в запросах. В частности, эта функция не должна использоваться для запросов, которые должны изменять элемент json, потому что, даже если возвращается изменённый json, сам файл не изменится.

Json_Get_Item

Json_Get_Item(arg1, arg2, …)

Эта функция возвращает подмножество документа json, переданного в качестве первого аргумента. Второй аргумент — путь json элемента, который нужно вернуть, и должен возвращать элемент json (заканчивающийся на «*»). В противном случае функция попытается исправить это, но это не гарантированно. Например:

select Json_Get_Item(Json_Object('foo' as "first", Json_Array('a', 33) 
  as "json_second"), 'second') as "item";

Правильным путём должен был быть «second:*» (или, начиная с MariaDB 10.2.8, «second.*»), но в этом простом случае функция смогла исправить это. Возвращаемый элемент:

Примечание: массив имеет псевдоним «json_second», чтобы указать, что это элемент json, и избежать экранирования. Однако префикс «json_» пропущен при создании объекта и не должен добавляться к пути.

JsonGet_Grp_Size

JsonGet_Grp_Size(val)

Эта функция возвращает значение JsonGrpSize.

JsonGet_String / JsonGet_Int / JsonGet_Real

JsonGet_String(arg1, arg2, [arg3] …)
JsonGet_Int(arg1, arg2, [arg3] …)
JsonGet_Real(arg1, arg2, [arg3] …)

Первый аргумент должен быть JSON-элементом. Если это строка без псевдонима, она будет преобразована в JSON-элемент. Второй аргумент — путь к элементу, который необходимо найти в первом аргументе и вернуть, в конечном итоге преобразованному в соответствии с используемой функцией. Например:

select 
JsonGet_String('{"qty":7,"price":29.50,"garanty":null}','price') "String",
JsonGet_Int('{"qty":7,"price":29.50,"garanty":null}','price') "Int",
JsonGet_Real('{"qty":7,"price":29.50,"garanty":null}','price') "Real";

Этот запрос возвращает:

Функции JsonGet_Real можно передать третий аргумент, чтобы указать количество десятичных знаков возвращаемого значения. Например:

select 
JsonGet_Real('{"qty":7,"price":29.50,"garanty":null}','price',4) "Real";

Этот запрос возвращает:

Указанный путь может указывать все операторы для массивов, кроме оператора «расширения» [X] (или, начиная с MariaDB 10.2.8, оператора «расширения» [*]). Например:

select 
JsonGet_Int(Json_Array(45,28,36,45,89), '[4]') "Rank",
JsonGet_Int(Json_Array(45,28,36,45,89), '[#]') "Number",
JsonGet_String(Json_Array(45,28,36,45,89), '[","]') "Concat",
JsonGet_Int(Json_Array(45,28,36,45,89), '[+]') "Sum",
JsonGet_Real(Json_Array(45,28,36,45,89), '[!]', 2) "Avg";

Результат:

Json_Item_Merge

Json_Item_Merge(arg1, arg2, …)

Эта функция объединяет два массива или два объекта. Для массивов это делается путём добавления всех значений второго массива в первый массив. Например:

select Json_Item_Merge(Json_Array('a','b','c'), Json_Array('d','e','f')) as "Result"; 

Функция возвращает:

Для объектов пары второго объекта добавляются к первому объекту, если ключ ещё не существует в нём; в противном случае пара первого объекта устанавливается со значением соответствующей пары второго объекта. Например:

select Json_Item_Merge(Json_Object(1 "a", 2 "b", 3 "c"), Json_Object(4 "d",5 "b",6 "f")) 
  as "Result"; 

Функция возвращает:

JsonLocate

JsonLocate(arg1, arg2, [arg3], …):

Первый аргумент должен быть JSON-деревом. Второй аргумент — элемент, который нужно найти. Элемент, который нужно найти, может быть константой или JSON-элементом. Константы должны совпадать по типу и значению, которые нужно найти. Это «поверхностное равенство» — строки, целые числа и числа с плавающей точкой не будут совпадать.

Эта функция возвращает JSON-путь к найденному элементу или null, если он не найден. Например:

select JsonLocate('{"AUTHORS":[{"FN":"Jules", "LN":"Verne"}, 
  {"FN":"Jack", "LN":"London"}]}' json_, 'Jack') Path;

Этот запрос возвращает:

или, начиная с MariaDB 10.2.8:

Синтаксис пути такой же, как используется в таблицах JSON CONNECT.

По умолчанию возвращается путь к первому вхождению элемента. Третий параметр может быть использован для указания вхождения, путь к которому должен быть возвращён. Например:

select 
JsonLocate('[45,28,[36,45],89]',45) first,
JsonLocate('[45,28,[36,45],89]',45,2) second,
JsonLocate('[45,28,[36,45],89]',45.0) `wrong type`,
JsonLocate('[45,28,[36,45],89]','[36,45]' json_) json;

или, начиная с MariaDB 10.2.8:

Для строковых элементов сравнение по умолчанию чувствительно к регистру. Однако можно указать строку для сравнения без учёта регистра, придав ей псевдоним, начинающийся с «ci»:

select JsonLocate('{"AUTHORS":[{"FN":"Jules", "LN":"Verne"}, 
  {"FN":"Jack", "LN":"London"}]}' json_, 'VERNE' ci) Path;

или, начиная с MariaDB 10.2.8:

Json_Locate_All

Json_Locate_All(arg1, arg2, [arg3], …):

Первый аргумент должен быть JSON-элементом. Второй аргумент — элемент, который нужно найти. Эта функция возвращает пути ко всем местоположениям элемента в виде массива строк. Например:

select Json_Locate_All('[[45,28],[[36,45],89]]',45);

Этот запрос возвращает:

или, начиная с MariaDB 10.2.8:

Возвращаемый массив может быть применён к другим функциям. Например, чтобы получить количество вхождений элемента в JSON-дереве, можно сделать:

select JsonGet_Int(Json_Locate_All('[[45,28],[[36,45],89]]',45), '[#]') "Nb of occurs";

или, начиная с MariaDB 10.2.8:

select JsonGet_Int(Json_Locate_All('[[45,28],[[36,45],89]]',45), '$[#]') "Nb of occurs";

Отображённый результат:

Если задан третий целочисленный аргумент, он устанавливает глубину поиска в документе. Это означает максимальное количество элементов в путях (до MariaDB 10.2.7, количество символов разделителя «:» в них плюс один). По умолчанию это значение равно 10, но его можно увеличить для сложных документов или уменьшить, чтобы установить максимальную желаемую глубину возвращаемых путей.

Json_Make_Array

Json_Make_Array(val1, …, valn)

Эта функция называлась «Json_Array» в предыдущих версиях CONNECT. Она была переименована, потому что MariaDB 10.2 содержит встроенные JSON-функции, включая функцию Json_Array. Встроенная функция делает почти то же самое, что и UDF-функция, но не принимает специфичные для CONNECT аргументы, такие как результат функций JBIN.

Json_Make_Array возвращает строку, обозначающую JSON-массив со всеми её аргументами в качестве членов. Например:

select Json_Make_Array(56, 3.1416, 'My name is "Foo"', NULL);

Примечание: список аргументов может быть пустым. В этом случае возвращается пустой массив.

Эта функция называлась «Json_Array» в предыдущих версиях CONNECT. Она была переименована, потому что MariaDB 10.2 содержит встроенные JSON-функции, включая функцию «Json_Array». Встроенная функция делает почти то же самое, что и UDF-функция, но не принимает специфичные для CONNECT аргументы, такие как результат функций JBIN.

Json_Make_Object

Json_Make_Object(arg1, …, argn)

Эта функция называлась «Json_Object» в предыдущих версиях CONNECT. Она была переименована, потому что MariaDB 10.2 содержит встроенные JSON-функции, включая функцию Json_Object. Встроенная функция делает то же, что и UDF Json_Object_Key.

Json_Make_Object возвращает строку, обозначающую JSON-объект. Например:

select Json_Make_Object(56, 3.1416, 'machin', NULL);

Объект заполняется парами, соответствующими заданным аргументам. Ключ каждой пары создаётся из псевдонима аргумента (по умолчанию или указанного).

При необходимости можно указать ключи, задав псевдоним для аргументов:

select Json_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty);

Если псевдоним начинается с «json_» (чтобы избежать экранирования), имя ключа удаляется из этого префикса.

Эта функция полезна, когда необходимо вводить значения, извлечённые из таблицы, ключ по умолчанию — имя столбца:

select Json_Make_Object(matricule, nom, titre, salaire) from connect.employe where nom = 'PANTIER';

Эта функция называлась «Json_Object» в предыдущих версиях CONNECT. Она была переименована, потому что MariaDB 10.2 содержит встроенные JSON-функции, включая функцию «Json_Object». Встроенная функция делает то же, что и UDF Json_Object_Key.

Json_Object_Add

Json_Object_Add(arg1, arg2, [arg3] …)

Первый аргумент должен быть JSON-объектом. Второй аргумент добавляется как пара в этот объект. Например:

select Json_Object_Add
  ('{"item":"T-shirt","qty":27,"price":24.99}' json_old,'blue' color) newobj;

Примечание: если указанный ключ уже существует в объекте, его значение заменяется новым.

Третий строковый аргумент — JSON-путь к целевому объекту.

Json_Object_Delete

Json_Object_Delete(arg1, arg2, [arg3] …):

Первый аргумент должен быть JSON-объектом. Второй аргумент — ключ пары, которую нужно удалить. Например:

select Json_Object_Delete('{"item":"T-shirt","qty":27,"price":24.99}' json_old, 'qty') newobj;

Третий строковый аргумент — JSON-путь к объекту, который должен быть целью удаления.

Json_Object_Grp

Json_Object_Grp(arg1,arg2)

Эта функция работает как Json_Array_Grp. Она создаёт JSON-объект, заполненный парами значений, ключи которых передаются из первого аргумента, а значения — из второго аргумента.

Это можно увидеть в запросе:

select name, json_object_grp(number,race) from pet group by name;

Этот запрос возвращает:

Json_Object_Key

Json_Object_Key([key1, val1 [, …, keyn, valn]])

Возвращает строку, обозначающую JSON-объект. Например:

select Json_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty', NULL);

Объект заполняется парами, составленными из каждого ключа/значения аргументов.

Список JSON-объектов

Json_Object_List(arg1, …):

Первый аргумент должен быть JSON-объектом. Эта функция возвращает массив, содержащий список всех ключей, существующих в объекте. Например:

select Json_Object_List(Json_Object(56 qty,3.1416 price,'machin' truc, NULL garanty))
  "Key List";

Json_Object_Nonull

Json_Object_Nonull(arg1, …, argn)

Эта функция работает так же, как Json_Make_Object, но аргументы «null» игнорируются и не вставляются в объект. Аргументы считаются «null», если они являются JSON-значениями null, пустыми массивами или объектами, или массивами или объектами, содержащими только нулевые члены.

Она в основном используется для предотвращения создания бесполезных нулевых элементов при преобразовании таблиц (см. ниже).

Значения JSON-объектов

Json_Object_Values(json_object)

Первый аргумент должен быть JSON-объектом. Эта функция возвращает массив, содержащий список всех значений, существующих в объекте. Например:

select Json_Object_Values('{"One":1,"Two":2,"Three":3}') "Value List";

JsonSet_Grp_Size

JsonSet_Grp_Size(val)

Эта функция используется для установки значения JsonGrpSize. Это значение используется следующими агрегатными функциями в качестве максимального значения числа элементов в каждой группе. Она возвращает значение JsonGrpSize, которое может быть его значением по умолчанию, если в качестве аргумента передается 0.

Json_Set_Item / Json_Insert_Item / Json_Update_Item

Json_{Set | Insert | Update}_Item(json_doc, [item, path [, val, path …]])

Эти функции вставляют или обновляют данные в JSON-документ и возвращают результат. Пары значение/путь оцениваются слева направо. Документ, полученный в результате оценки одной пары, становится новым значением, по отношению к которому оценивается следующая пара.

• Json_Set_Item заменяет существующие значения и добавляет несуществующие.
• Json_Insert_Item вставляет значения без замены существующих.
• Json_Update_Item заменяет только существующие значения.

Пример:

set @j = Json_Array(1, 2, 3, Json_Object_Key('quatre', 4));
select Json_Set_Item(@j, 'foo', '[1]', 5, '[3]:cinq') as "Set",
Json_Insert_Item(@j, 'foo', '[1]', 5, '[3]:cinq') as "Insert",
Json_Update_Item(@j, 'foo', '[1]', 5, '[3]:cinq') as "Update";

или, из MariaDB 10.2.8:

set @j = Json_Array(1, 2, 3, Json_Object_Key('quatre', 4));
select Json_Set_Item(@j, 'foo', '$[1]', 5, '$[3].cinq') as "Set",
Json_Insert_Item(@j, 'foo', '$[1]', 5, '$[3].cinq') as "Insert",
Json_Update_Item(@j, 'foo', '$[1]', 5, '$[3].cinq') as "Update";

Этот запрос возвращает:

JsonValue

JsonValue (val)

Возвращает значение JSON в виде строки, например:

select JsonValue(3.1416);

До MariaDB 10.1.9 эта функция называлась Json_Value, но была переименована, чтобы избежать конфликта с функцией JSON_VALUE.

Тип возвращаемого значения «JBIN»

Почти все функции, возвращающие строку json - имя которых начинается с Json_ - имеют аналог с именем, начинающимся с Jbin_. Это делается как для производительности (скорость и память), так и для лучшего контроля над тем, что должны делать функции.

Это связано со способом работы UDF CONNECT. Функции Json, принимая json-строки в качестве параметров, разбирают их и строят двоичное дерево в памяти. Они работают с этим деревом и перед возвратом сериализуют это дерево, чтобы вернуть новую json-строку.

Если json-документ большой, это может занять много времени и занимать много места в памяти. Это нормально, когда вызывается одна простая функция json - это необходимо сделать в любом случае - но это пустая трата времени и памяти, когда функции json используются в качестве параметров другим функциям json.

Чтобы избежать многократной сериализации и разбора, следует использовать функции Jbin в качестве параметров для других функций. Действительно, они не сериализуют дерево двоичного документа в памяти, а возвращают структуру, позволяющую получающей функции получить прямой доступ к дереву в памяти. Это сохраняет этапы сериализации-разбора, которые в противном случае необходимы для передачи аргумента, и отменяет необходимость перераспределять память двоичного дерева, которое, к слову, в 6-7 раз больше размера json-строки. Например:

select Json_Object(Jbin_Array_Add(Jbin_Array('a','b','c'), 'd') as "Jbin_foo") as "Result";

Этот запрос возвращает:

Здесь двоичное json-дерево, выделенное функцией Jbin_Array, дополняется функциями Jbin_Array_Add и Json_Object, и сериализуется только один раз, чтобы получить конечную строку результата. При использовании функций «Json» она бы сериализовалась и парсилась ещё дважды.

Обратите внимание, что результаты Jbin распознаются как такие, потому что они имеют алиасы, начинающиеся с «Jbin_». Вот почему в функции Json_Object алиас указан как «Jbin_foo».

Что произойдёт, если это не распознано как таковое? Эти функции объявлены как возвращающие строку, и для этого возвращаемая структура начинается с нуль-терминированной строки. Например:

select Jbin_Array('a','b','c');

Этот запрос отвечает:

Примечание: При тестировании дерево, возвращаемое функцией «Jbin», можно увидеть, используя функцию Json_Serialize, единственным параметром которой должен быть результат «Jbin». Например:

select Json_Serialize(Jbin_Array('a','b','c'));

Этот запрос возвращает:

Примечание: В этом простом примере это эквивалентно использованию функции Json_Array.

Использование файла в качестве первого аргумента json UDF

Мы видели, что многие json UDF могут иметь дополнительный аргумент, который ещё не описан. Это происходит в случае, когда аргумент json-элемента ссылается на файл. Тогда дополнительный целочисленный аргумент представляет собой красивый формат json-файла. Он важен только тогда, когда первый аргумент - это просто имя файла (чтобы функция UDF поняла, что этот аргумент - имя файла, он должен быть алиасом с именем, начинающимся с jfile_) или если функция изменяет файл, в этом случае он будет переписан в этом формате.

Json-элемент создается путем извлечения необходимой части из файла. Это может быть весь файл, но чаще всего только часть его. Есть два способа указать подэлемент файла, который должен использоваться:

1. Указание в аргументах Json_File или Jbin_File.
2. Указание в принимающей функции (не возможно для всех функций).

Это не имеет значения, когда используется Jbin_File, но это имеет значение для Json_File. Например:

select Jfile_Make('{"a":1, "b":[44, 55]}' json_, 'test.json');
select Json_Array_Add(Json_File('test.json', 'b'), 66);

Второй запрос возвращает:

Он просто возвращает – измененный – подмножество, возвращенное функцией Json_File, в то время как запрос:

select Json_Array_Add(Json_File('test.json'), 66, 'b');

возвращает то, что было получено из Json_File с внесенными изменениями в подмножество.

Обратите внимание, что в обоих случаях файл test.json не изменяется. Это потому, что функция Json_File возвращает строку, представляющую весь или часть текста файла, но не содержит информации об имени файла. Это нормально для проверки того, каким будет эффект внесения изменений в файл.

Однако, для изменения файла используйте функцию Jbin_File или непосредственно укажите имя файла. Jbin_File возвращает структуру, содержащую имя файла, указатель на дерево, разобранное из файла, и, возможно, указатель на подмножество, когда в качестве второго аргумента задан путь:

select Json_Array_Add(Jbin_File('test.json', 'b'), 66);

Этот запрос возвращает:

В этот раз файл изменяется. Это можно проверить с помощью:

select Json_File('test.json', 3);

create table tb (
n int key,
jfile_cols char(10) not null);
insert into tb values(1,'test.json');

update tb set jfile_cols = select Json_Array_Add(Jbin_File('test.json', 'b'), 66)
where n = 1;

select JsonGet_String(jfile_cols, '[1]:*') from tb;

Примечание: Было важно назвать второй столбец таблицы, начиная с «jfile_», чтобы функции json знали, что это имя файла, без необходимости указывать алиас в запросах.

Использование «Jbin» для управления тем, что делает выполнение запроса

Это особенно актуально при работе с json-файлами. Мы видели, что файл не изменялся при использовании функции Json_File в качестве аргумента для изменяющей функции, потому что изменяющая функция просто получала копию json-файла. Это не относится к функции Jbin_File, которая не сериализует двоичный документ и предоставляет прямой доступ к нему. Кроме того, как мы видели ранее, функции json, которые изменяют свой первый параметр-файл, изменяют файл и возвращают имя файла. Это делается путем непосредственной сериализации внутреннего двоичного документа в виде файла.

Однако аналог этих функций с «Jbin» не сериализует двоичный документ и, следовательно, не изменяет json-файл. Например, давайте сравним эти два запроса:

/* Первый запрос */

select Json_Object(Jbin_Object_Add(Jbin_File('bt2.json'), 4 as "d") as "Jbin_bt1")
  as "Result";

/* Второй запрос */

select Json_Object(Json_Object_Add(Jbin_File('bt2.json'), 4 as "d") as "Jfile_bt1")
  as "Result";

Оба запроса возвращают:

В первом запросе Jbin_Object_Add не сериализует документ (ни одна функция «Jbin» этого не делает), а Json_Object просто возвращает сериализованное измененное дерево. Следовательно, файл bt2.json не изменяется. Этот запрос подходит для копирования измененной версии json-файла без его изменения.

Однако, во втором запросе Json_Object_Add изменяет файл json и возвращает имя файла. Функция Json_Object получает это имя файла, считывает и парсит файл, создаёт объект из него и возвращает сериализованный результат. Это изменение может быть сделано намеренно, но может быть нежелательным побочным эффектом запроса.

Поэтому, использование функций с аргументом “Jbin”, помимо большей скорости и меньшего потребления памяти, также является более безопасным при работе с файлами json, которые не должны изменяться.

Использование JSON в качестве динамических столбцов

Язык JSON nosql обладает всеми необходимыми возможностями, чтобы использоваться в качестве альтернативы динамическим столбцам. Например, рассмотрим следующий пример динамических столбцов:

create table assets (
   item_name varchar(32) primary key, /* A common attribute for all items */
   dynamic_cols  blob  /* Dynamic columns will be stored here */
 );

INSERT INTO assets VALUES
   ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL'));

INSERT INTO assets VALUES
   ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500));

SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets;
+-----------------+-------+
| item_name       | color |
+-----------------+-------+
| MariaDB T-shirt | blue  |
| Thinkpad Laptop | black |
+-----------------+-------+

/* Удаление столбца: */

UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, "price")
  WHERE COLUMN_GET(dynamic_cols, 'color' as char)='black';

/* Добавление столбца: */

UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years')
   WHERE item_name='Thinkpad Laptop';

/* Также можно перечислить все столбцы или получить их вместе со значениями в формате JSON: */

SELECT item_name, column_list(dynamic_cols) FROM assets;
+-----------------+---------------------------+
| item_name       | column_list(dynamic_cols) |
+-----------------+---------------------------+
| MariaDB T-shirt | `size`,`color`            |
| Thinkpad Laptop | `color`,`warranty`        |
+-----------------+---------------------------+

SELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets;
+-----------------+----------------------------------------+
| item_name       | COLUMN_JSON(dynamic_cols)              |
+-----------------+----------------------------------------+
| MariaDB T-shirt | {"size":"XL","color":"blue"}           |
| Thinkpad Laptop | {"color":"black","warranty":"3 years"} |
+-----------------+----------------------------------------+

Тот же результат можно получить, используя столбцы json с помощью UDF функций json:

/* Эквивалент JSON */

create table jassets (
   item_name varchar(32) primary key, /* A common attribute for all items */
   json_cols varchar(512)  /* Jason columns will be stored here */
 );

INSERT INTO jassets VALUES
   ('MariaDB T-shirt', Json_Object('blue' color, 'XL' size));

INSERT INTO jassets VALUES
   ('Thinkpad Laptop', Json_Object('black' color, 500 price));

SELECT item_name, JsonGet_String(json_cols, 'color') AS color FROM jassets;
+-----------------+-------+
| item_name       | color |
+-----------------+-------+
| MariaDB T-shirt | blue  |
| Thinkpad Laptop | black |
+-----------------+-------+

/* Удаление столбца: */

UPDATE jassets SET json_cols=Json_Object_Delete(json_cols, 'price')
 WHERE JsonGet_String(json_cols, 'color')='black';

/* Добавление столбца */

UPDATE jassets SET json_cols=Json_Object_Add(json_cols, '3 years' warranty)
 WHERE item_name='Thinkpad Laptop';

/* Также можно перечислить все столбцы или получить их вместе со значениями в формате JSON: */

SELECT item_name, Json_Object_List(json_cols) FROM jassets;
+-----------------+-----------------------------+
| item_name       | Json_Object_List(json_cols) |
+-----------------+-----------------------------+
| MariaDB T-shirt | ["color","size"]            |
| Thinkpad Laptop | ["color","warranty"]        |
+-----------------+-----------------------------+

SELECT item_name, json_cols FROM jassets;
+-----------------+----------------------------------------+
| item_name       | json_cols                              |
+-----------------+----------------------------------------+
| MariaDB T-shirt | {"color":"blue","size":"XL"}           |
| Thinkpad Laptop | {"color":"black","warranty":"3 years"} |
+-----------------+----------------------------------------+

Однако, использование JSON предоставляет возможности, отсутствующие в динамических столбцах:

• Использование языка, используемого многими реализациями и разработчиками.
• Полная поддержка массивов, которой сейчас не хватает в динамических столбцах.
• Доступ к подчасти json с помощью JPATH, который может включать вычисления над массивами.
• Возможные ссылки на файлы json.

С большим опытом, дополнительные UDF могут быть легко написаны для поддержки новых потребностей.

Новый набор функций BSON

Все эти функции были переписаны, используя новый способ обработки JSON, и временно доступны, изменяя имя, начинающееся с J, на B. Затем новый стиль Json_Make_Array вызывается с помощью Bson_Make_Array. Некоторые, такие как Bson_Item_Delete, новые, а некоторые исправляют ошибки, обнаруженные в их аналогах Json.

Преобразование таблиц в JSON

Функции JSON UDF и прямая возможность Jpath “*” являются мощными инструментами для преобразования таблиц и файлов в формат JSON. Например, файл biblio3.json который мы использовали ранее, можно получить, преобразовав xsample.xml file. Это можно сделать так:

Из Connect 1.07.0002

create table xj1 (row varchar(500) jpath='*') engine=connect table_type=JSON file_name='biblio3.json' option_list='jmode=2';

До Connect 1.07.0002

create table xj1 (row varchar(500) field_format='*') 
 engine=connect table_type=JSON file_name='biblio3.json' option_list='jmode=2';

И затем:

insert into xj1
  select json_object_nonull(ISBN, language LANG, SUBJECT, 
    json_array_grp(json_object(authorfn FIRSTNAME, authorln LASTNAME)) json_AUTHOR, TITLE,
    json_object(translated PREFIX, json_object(tranfn FIRSTNAME, tranln LASTNAME) json_TRANSLATOR) 
    json_TRANSLATED, json_object(publisher NAME, location PLACE) json_PUBLISHER, date DATEPUB) 
from xsampall2 group by isbn;

Строки таблицы xj1 будут непосредственно получать объект Json, созданный оператором select, используемым в операторе insert, а файл таблицы будет создан, как показано (xj1 по умолчанию имеет pretty=2). Его режим Jmode=2, потому что вставляемые значения являются строками, даже если они обозначают объекты json.

Другой способ сделать это — создать таблицу, описывающую желаемый формат файла, до существования файла biblio3.json:

Из Connect 1.07.0002

create table jsampall3 (
ISBN char(15),
LANGUAGE char(2) jpath='LANG',
SUBJECT char(32),
AUTHORFN char(128) jpath='AUTHOR:[X]:FIRSTNAME',
AUTHORLN char(128) jpath='AUTHOR:[X]:LASTNAME',
TITLE char(32),
TRANSLATED char(32) jpath='TRANSLATOR:PREFIX',
TRANSLATORFN char(128) jpath='TRANSLATOR:FIRSTNAME',
TRANSLATORLN char(128) jpath='TRANSLATOR:LASTNAME',
PUBLISHER char(20) jpath='PUBLISHER:NAME',
LOCATION char(20) jpath='PUBLISHER:PLACE',
DATE int(4) jpath='DATEPUB')
engine=CONNECT table_type=JSON file_name='biblio3.json';

До Connect 1.07.0002

create table jsampall3 (
ISBN char(15),
LANGUAGE char(2) field_format='LANG',
SUBJECT char(32),
AUTHORFN char(128) field_format='AUTHOR:[X]:FIRSTNAME',
AUTHORLN char(128) field_format='AUTHOR:[X]:LASTNAME',
TITLE char(32),
TRANSLATED char(32) field_format='TRANSLATOR:PREFIX',
TRANSLATORFN char(128) field_format='TRANSLATOR:FIRSTNAME',
TRANSLATORLN char(128) field_format='TRANSLATOR:LASTNAME',
PUBLISHER char(20) field_format='PUBLISHER:NAME',
LOCATION char(20) field_format='PUBLISHER:PLACE',
DATE int(4) field_format='DATEPUB')
engine=CONNECT table_type=JSON file_name='biblio3.json';

и заполнить её:

insert into jsampall3 select * from xsampall;

Это более простой метод. Однако проблема в том, что этот метод не может обрабатывать несколько значений столбцов. Вот почему мы вставляли данные из xsampall, а не из xsampall2. Как добавить недостающих нескольких авторов в эту таблицу? Опять же, нам нужно создать вспомогательную таблицу, способную обрабатывать строки JSON. Из Connect 1.07.0002

create table xj2 (ISBN char(15), author varchar(150) jpath='AUTHOR:*') engine=connect table_type=JSON file_name='biblio3.json' option_list='jmode=1';

До Connect 1.07.0002

create table xj2 (ISBN char(15), author varchar(150) field_format='AUTHOR:*') 
  engine=connect table_type=JSON file_name='biblio3.json' option_list='jmode=1';

update xj2 set author =
(select json_array_grp(json_object(authorfn FIRSTNAME, authorln LASTNAME)) 
  from xsampall2 where isbn = xj2.isbn);

Вот и всё!

Преобразование файлов json

Мы видели, что файлы json могут форматироваться по-разному в зависимости от опции pretty. В частности, файлы больших данных должны быть отформатированы с pretty равным 0, когда используются в таблице json CONNECT. Наиболее простой и эффективный способ преобразовать файл из одного формата в другой — использовать функцию Jfile_Make. Действительно, эта функция создаёт файл заданного формата, используя синтаксис:

Jfile_Make(json_document, [file_name], [pretty]);

Имя файла необязательно, когда документ json получен из функции Jbin_File, потому что возвращаемая структура делает его доступным. Например, чтобы преобразовать файл json tb.json в pretty=0, это можно просто сделать следующим образом:

select Jfile_Make(Jbin_File('tb.json'), 0);

Учёт производительности

MySQL и PostgreSQL имеют тип данных JSON, который представляет собой не только текст, но и внутреннее кодирование данных JSON. Это позволяет сэкономить время на разборе при выполнении функций JSON. Конечно, разбор всё равно должен быть выполнен при создании данных и сериализации для вывода результата.

CONNECT напрямую работает со строками символов, имитируя значения JSON, с необходимостью их постоянного разбора, но с преимуществом лёгкой работы с внешними данными. Обычно это не слишком накладно, так как данные JSON часто имеют небольшой или разумный размер. Единственный случай, когда это может стать серьёзной проблемой, — это работа с большим файлом JSON.

Затем файл должен быть отформатирован или преобразован в pretty=0.

С версии Connect 1.7.002 это легко выполняется с помощью функции Jfile_Convert, например:

select jfile_convert('bibdoc.json','bibdoc0.json',350);

Такой файл json не должен использоваться напрямую функциями JSON UDF, потому что они анализируют весь файл, даже если используется только подмножество. Вместо этого он должен использоваться таблицей JSON, созданной на нём. Действительно, таблицы JSON не анализируют весь документ, а только элемент, соответствующий строке, с которой они работают. Кроме того, для таблицы можно использовать индексирование, как объяснялось ранее на этой странице.

В общем случае, максимальная гибкость, предоставляемая CONNECT, достигается при совместном использовании таблиц JSON и функций JSON UDF. Некоторые вещи лучше обрабатываются таблицами, другие — функциями UDF. Инструменты есть, но вам нужно найти лучший способ решения своих задач.

Файлы bjson

Начиная с Connect 1.7.002, файлы json с pretty=0 можно преобразовать в двоичный формат, который представляет собой предварительно проанализированное представление json. Это можно сделать с помощью функции Jfile_Bjson UDF, например:

select jfile_bjson('bigfile.json','binfile.json',3500);

Здесь третий аргумент, длина записи, должен быть в 6-10 раз больше, чем lrecl исходного файла json, так как проанализированное представление больше, чем исходное текстовое представление json.

Таблицы, использующие такие файлы Bjson, должны указывать ‘Pretty=-1’ в списке опций.

Вероятно, это похоже на BSON, используемый MongoDB и PostgreSQL, и позволяет обрабатывать запросы до 10 раз быстрее, чем работать с текстовыми файлами json. Индексирование также доступно для таблиц, использующих этот формат, что ещё больше улучшает производительность. Например, некоторые запросы к таблице json с половиной миллиона строк, которые ранее выполнялись более чем за 10 секунд, занимали всего 0,1 секунды после преобразования и индексирования.

Здесь снова это было переделано, чтобы использовать новый способ обработки Json. Файлы, созданные с помощью функции bfile_bjson, имеют размер всего в 2-4 раза больше размера исходных файлов. Это новое представление несовместимо со старым. Поэтому эти файлы должны использоваться только с таблицами BSON.

Указание кодирования таблицы JSON

Важная особенность JSON заключается в том, что строки должны быть в формате UNICODE. Фактически, все примеры, которые мы нашли в интернете, представлялись как ASCII. Это связано с тем, что UNICODE обычно кодируется в файлах JSON с использованием UTF8 или UTF16 или UTF32.

Для указания требуемого кодирования просто используйте опцию data_charset CONNECT или опцию DEFAULT CHARSET.

Извлечение данных JSON из MongoDB

Классифицируемая как программа базы данных NoSQL, MongoDB использует документы, похожие на JSON (BSON), сгруппированные в коллекции. Самый простой способ, и единственный доступный до Connect 1.6, доступа к данным MongoDB заключался в экспорте коллекции в файл JSON. Это создаёт файл с форматом pretty=0. С точки зрения SQL, коллекция — это таблица, а документы — это строки таблицы.

С версии CONNECT 1.6 теперь можно напрямую получить доступ к коллекциям MongoDB через драйвер MongoDB C. Это цель типа таблицы MONGO, описанной позже. Однако таблицы JSON также могут сделать это несколько иначе (при условии, что поддержка MONGO установлена, как описано для таблиц MONGO).

Это достигается путём указания URI подключения к MongoDB при создании таблицы. Например:

Из Connect 1.7.002

create or replace table jinvent (
_id char(24) not null, 
item char(12) not null,
instock varchar(300) not null jpath='instock.*')
engine=connect table_type=JSON tabname='inventory' lrecl=512
connection='mongodb://localhost:27017';

До Connect 1.7.002

create or replace table jinvent (
_id char(24) not null, 
item char(12) not null,
instock varchar(300) not null field_format='instock.*')
engine=connect table_type=JSON tabname='inventory' lrecl=512
connection='mongodb://localhost:27017';

В этом операторе опция file_name была заменена опцией connection. Это URI, позволяющий извлекать данные с локального или удалённого сервера MongoDB. Опция tabname — имя коллекции MongoDB, которая будет использоваться, а опция dbname могла использоваться для указания базы данных, содержащей коллекцию (по умолчанию — текущая база данных).

Способ работы заключается в том, что документы, извлеченные из MongoDB, сериализуются, и CONNECT использует их так, как если бы они были прочитаны из файла. Это подразумевает сериализацию MongoDB и разбор CONNECT, что не является оптимальным с точки зрения производительности. CONNECT старается минимизировать передачу данных, когда запрос содержит сокращённый список столбцов и/или условие where. Таким образом, доступны все возможности типа таблицы JSON, такие как вычисляемые массивы.

Однако для работы с большими коллекциями JSON использование типа таблицы MONGO, как правило, является стандартным способом.

Примечание: Таблицы JSON, использующие доступ к MongoDB, принимают специфические опции MONGO: colist, filter и pipeline. Они описаны в главе по таблицам MONGO.

Резюме опций и переменных, используемых с таблицами Json

Здесь перечислены опции и переменные, которые можно использовать при создании таблиц Json:

(*) Для JSON-таблиц, подключенных к MongoDB, также можно использовать специфичные для Mongo параметры.

Другие параметры должны быть указаны в списке параметров:

Параметры столбцов:

Переменные, используемые с JSON-таблицами:

• connect_default_depth
• connect_json_null
• connect_json_all_path
• connect_force_bson

Примечания

1. ↑ Значение n может быть нулево- или единично-базированным, в зависимости от параметра base таблицы. По умолчанию 0, что соответствует текущему использованию в мире JSON, но может быть установлено в 1 для таблиц, созданных в старых версиях.
2. ↑ См., например: json-functions, https://github.com/mysqludf/lib_mysqludf_json#readme и https://blogs.oracle.com/svetasmirnova/entry/json_udf_functions_version_04
3. ↑ Это не сработает, когда CONNECT скомпилирован как встраиваемый модуль.