Перейти к основному содержанию
`Ctrl`+`K`

# Работа с данными, хранящимися в jsonb контейнере

## Атрибуты, хранящиеся в jsonb контейнере

В нашей системе есть два типа атрибутов:

- Базовые, определяемые в odm-файле.

- Json-хранимые - определяемые как через интерфейс приложения(в том числе универсальные характеристики), так и через исходный код.

В самом простом случае при создании json-атрибута в интерфейсе приложения он будет отмечен как объектная характеристика, хранящаяся в json.

Редактировать такие атрибуты можно на вкладке объектных характеристик соответствующего класса. Обработка этих атрибутов уже реализована средствами фреймворка.

Внимание

В базе для хранения данных используется контейнер типа jsonb.

## Получение и установка атрибутов, хранимых в json

Получение и обработка данных происходит методами api текущего класса для объектных характеристик.

Примечание

Все json-хранимые объектные характеристики и универсальные характеристики хранятся в контейнере с именем `jObjAttrs_dz`. И все перечисленные далее методы чтения и записи данных для атрибутов-характеристик работают именно с этим контейнером.

Для атрибута, хранимого в json и настроенного как объектная характеристика в `JObjectAttrApi.scala`, реализован набор методов для записи и чтения данных из контейнера:

### Запись по id

    ru.bitec.app.btk.class_.service.objectAttr.JObjectAttrApi#setObjAttrValue(rop: ApiRop, idpAttr: NLong, pValue: Any)

- Метод установки значения json атрибута по его id.

- Вызвать этот метод можно от апи вашего класса.

- `rop` - rop-а, чей атрибут вы хотите изменить.

- `idpAttr` - id атрибута.

- `pValue` - значение, которое хотите установить атрибуту.

Метод проверяет, что переданное значение совпадает с типом данных, настроенным на атрибуте.

### Запись по имени

    ru.bitec.app.btk.class_.service.objectAttr.JObjectAttrApi#setObjAttrValue(rop: ApiRop, spAttrName: NString, pValue: Any)

- Метод установки значения json атрибута по его системному имени.

- Вызвать этот метод можно от апи вашего класса.

- `rop` - rop-а, чей атрибут вы хотите изменить.

- `spAttrName` - системное имя атрибута.

- `pValue` - значение, которое хотите установить атрибуту.

Метод проверяет наличие настроенного атрибута в классе `Btk_Attribute`, если атрибут не найден, то кидает ошибку.

После успешного прохождения проверки вызывается метод def setObjAttrValue(rop: ApiRop, idpAttr: NLong, pValue: Any).

Внимание

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

### Чтение по id

    ru.bitec.app.btk.class_.service.objectAttr.JObjectAttrApi#getObjAttrValue(rop: ApiRop, idpAttr: NLong)

- Метод получения значения json атрибута по его id.

- Так же, как и предыдущие, проверяет наличие атрибута в `Btk_Attribute`.

- Вызвать этот метод можно от апи вашего класса.

- `rop` - rop-а, чей атрибут вы хотите получить.

- `idpAttr` - id атрибута.

### Чтение по системному имени

    ru.bitec.app.btk.class_.service.objectAttr.JObjectAttrApi#getObjAttrValue(rop: ApiRop, spAttrName: NString)

- Метод получения значения json атрибута по его системному имени.

- Так же, как и предыдущие, проверяет наличие атрибута в `Btk_Attribute`

- Вызвать этот метод можно от апи вашего класса.

- `rop` - rop-а, чей атрибут вы хотите получить.

- `idpAttr` - id атрибута.

Метод проверяет наличие настроенного атрибута в классе `Btk_Attribute`, если атрибут не найден, то кидает ошибку.

После успешного прохождения проверки вызывается метод def getObjAttrValue(rop: ApiRop, idpAttr: NLong).

## Универсальные характеристики(UC)

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

Универсальные характеристики хранятся в справочнике `Btk_UniversalCharacteristic`.

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

Получение и обработка данных происходит методами api текущего класса для универсальных характеристик.

### Запись UC по id

    ru.bitec.app.btk.class_.service.objectAttr.JObjectAttrApi#setUniCharValue(rop: ApiRop, idpUniChar: NLong, pValue: Any)

- Метод установки значения UC json атрибута по его id

- Вызвать этот метод можно от апи вашего класса

- `rop` - rop-а, чей атрибут вы хотите изменить

- `idpUniChar` - id UC атрибута

- `pValue` - значение, которое хотите установить атрибуту. Возможна множественная установка значений

Метод проверяет, что переданное значение совпадает с типом данных, настроенным на атрибуте.

### Запись по имени

    ru.bitec.app.btk.class_.service.objectAttr.JObjectAttrApi#setUniCharValue(rop: ApiRop,  spUniCharName: NString, pValue: Any)

Аналогичен предыдущему. Предварительно ищет id универсальной характеристики по системному имени. После вызывает метод вызывается метод def setUniCharValue(rop: ApiRop, idpUniChar: NLong, pValue: Any). Возможна множественная установка значений.

### Чтение UC по id

    ru.bitec.app.btk.class_.service.objectAttr.JObjectAttrApi#getUniCharValue(rop: ApiRop, idpUniChar: NLong)

- Метод получения значения json атрибута по его id

- Так же как и предыдущие проверяет наличие атрибута в Btk_Attribute

- Вызвать этот метод можно от апи вашего класса

- `rop` - rop-а, чей атрибут вы хотите получить

- `idpUniChar` - id UC атрибута

### Чтение UC по системному имени

    ru.bitec.app.btk.class_.service.objectAttr.JObjectAttrApi#getUniCharValue(rop: ApiRop, spUniCharName: NString)

Проверяет наличие универсальной характеристики по системному имени. После вызывает метод def getUniCharValue(rop: ApiRop, idpUniChar: NLong).

## Чтение и запись иных данных в json контейнер

Если вам нужно хранить иные структуры данных, не подходящие под прошлые пункты, тогда:

- Для получения доступа к данным в контейнере используйте метод подключения json документа `ru.bitec.app.gtk.eclipse.json.JEmbeddedDoc#parseProperty` с последующей конвертацией к JObject и метод `ru.bitec.app.gtk.eclipse.json.JEObject#set`

- Для добавления или перезаписи значения по ключу:

      JEmbeddedDoc.parseProperty(rop,"Имя json контейнера").asJObject.set(key: NString, value: NString)

  Внимание

  Если попытаться напрямую установть значение в атрибут, можно затереть данные из контейнера, которые там присутствовали ранее!

## Работа с jsonb контейнерами

### Примечания

- Все строковые значения необходимо заворачивать в двойные кавычки `"text"`.

- Если вы пытаетесь поставить в нестроковое поле пустое значение, то используйте значение `null`.

- Оператор `->` возвращает элемент типа `jsonb`. Оператор `->>` возвращает элемент типа `text`. Для промежуточных манипуляций с объектами контейнера используйте `->`, а для получения конечного значения `->>`.

- При добавлении значений при помощи `||` или `jsonb_set()` к уже существующему набору (например `jtypesizeattrs`) пар ключ-значение необходимо оборачивать его в `coalesce(jtypesizeattrs, '{}'::jsonb)`, так как если `jtypesizeattrs` до этого был null, то прибавляя к нему любое значение, мы получим null.

- При добавлении значений при помощи `||` или `jsonb_set()` к уже существующему массиву (например `jSign_dz`) необходимо оборачивать его в `coalesce(jSign_dz, '[]'::jsonb)`, так как если `jSign_dz` до этого был null, то, прибавляя к нему любое значение, мы получим null.

- Формирование строк путей до полей пишется в фигурных скобках через запятую. В последовательность пути вписываются имена полей или индексы массивов. Например: `{0,sFIO}`. Имена полей можно писать в двойных кавычках, можно и без них.

- В нашей системе используются контейнеры типа `jsonb`, а не `json`.

- В приведённых ниже примерах в пунктах с изменением данных, в случаях если написан просто `select`, естественно нужно использовать синтаксис `update` вместо него.

- Ниже описаны примеры использования довольно узкого пула методов. Больше методов и их описание вы сможете найти по ссылке: https://postgrespro.ru/docs/postgrespro/9.5/functions-json

### Работа с контейнером jObjAttrs_dz

В контейнере `jObjAttrs_dz` данные хранятся в виде мапы, соответственно сразу можно использовать метод `->>` для получения конечных данных из этой мапы.

- Получение json-атрибута из контейнера

      select
          w.jObjAttrs_dz ->> 'idJsonAttr' as idJsonAttr,
          cast(w.jObjAttrs_dz ->> 'bJsonAttr' as numeric(1)) as bJsonAttr,
          cast(w.jObjAttrs_dz ->> 'nJsonAttr' as numeric(38, 18)) as nJsonAttr,
          w.jObjAttrs_dz ->> 'sJsonAttr' as sJsonAttr,
          cast(w.jObjAttrs_dz ->> 'dJsonAttr' as date) as dJsonAttr,
          cast(w.jObjAttrs_dz ->> 'dJsonAttr' as timestamp) as dJsonAttrDateTime
      from (
          select '{"idJsonAttr": 123456, "bJsonAttr": 0, "nJsonAttr": 0.01, "sJsonAttr": "Some test string", "dJsonAttr": "01.01.2101 10:10:10"}' :: jsonb as jObjAttrs_dz) w
      return: |idJsonAttr|bJsonAttr|           nJsonAttr|         sJsonAttr|   dJsonAttr|        dJsonAttrDateTime|
              |    123456|        0|0.010000000000000000|"Some test string"|"2101-01-01"|"2101-01-01 10:10:10.000"|

### Работа с контейнером jSign_dz в PostgreSQL

- Получение поля из массива

      select (w.jsign_dz -> 1) -> 'sFIO'  -- 1 - индекс массива. Отсчет ведётся с 0. sFIO - имя искомого поля
      from (select
              '[
                  {"sFIO": "Степанова Е.В.", "sPosition": "Нач. отдела", "idEmployee": null, "idDepartment": 102696, "sBasisDocument": null, "idBlankSignType": 95401},
                  {"sFIO": "Линчук Владимир Владимирович", "dDate": "04.12.2019", "sPosition": null, "idEmployee": 21035, "sBasisDocument": null, "idBlankSignType": 95402}
              ]' :: jsonb as jsign_dz) w

      return: "Линчук Владимир Владимирович"

- Обновление поля в массиве записей
  В этом случае используется метод `jsonb_set`. Аргументы: первоначальное значение, путь к изменяемому значению, устанавливаемое значение.

      update stk_warrantin w
      set jsign_dz = jsonb_set(coalesce(w.jSign_dz, '[]':: jsonb),'{1,sFIO}', '"Иванов Ива Иванович"') -- {1,sFIO} - путь к интересуемому полю 1 - индекс в массиве. sFIO - поле в массиве.
      where id = someId

      select jsonb_set(coalesce(w.jSign_dz, '[]':: jsonb),'{1,sFIO}', '"Иванов Ива Иванович"')
      from (select
              '[
                  {"sFIO": "Степанова Е.В.", "sPosition": "Нач. отдела", "idEmployee": null, "idDepartment": 102696, "sBasisDocument": null, "idBlankSignType": 95401},
                  {"sFIO": "Линчук Владимир Владимирович", "dDate": "04.12.2019", "sPosition": null, "idEmployee": 21035, "sBasisDocument": null, "idBlankSignType": 95402}
              ]' :: jsonb as jsign_dz) w

      return: [
                  {"sFIO": "Степанова Е.В.", "sPosition": "Нач. отдела", "idEmployee": null, "idDepartment": 102696, "sBasisDocument": null, "idBlankSignType": 95401},
                  {"sFIO": "Иванов Ива Иванович", "dDate": "04.12.2019", "sPosition": null, "idEmployee": 21035, "sBasisDocument": null, "idBlankSignType": 95402}
              ]

- Удаление записи из массива (пары ключ–значение из объекта)

      select w.jsign_dz - 1
      from (select
              '[
                  {"sFIO": "Степанова Е.В.", "sPosition": "Нач. отдела", "idEmployee": null, "idDepartment": 102696, "sBasisDocument": null, "idBlankSignType": 95401},
                  {"sFIO": "Линчук Владимир Владимирович", "dDate": "04.12.2019", "sPosition": null, "idEmployee": 21035, "sBasisDocument": null, "idBlankSignType": 95402}
              ]' :: jsonb as jsign_dz) w

      return: [
                  {"sFIO": "Степанова Е.В.", "sPosition": "Нач. отдела", "idEmployee": null, "idDepartment": 102696, "sBasisDocument": null, "idBlankSignType": 95401}
              ]

- Удаление поля из объекта (мапы)

      select (w.jsign_dz -> 0) - 'sFIO'
      from (select
              '[
                  {"sFIO": "Степанова Е.В.", "sPosition": "Нач. отдела", "idEmployee": null, "idDepartment": 102696, "sBasisDocument": null, "idBlankSignType": 95401},
                  {"sFIO": "Линчук Владимир Владимирович", "dDate": "04.12.2019", "sPosition": null, "idEmployee": 21035, "sBasisDocument": null, "idBlankSignType": 95402}
              ]' :: jsonb as jsign_dz) w

      return: {"sPosition": "Нач. отдела", "idEmployee": null, "idDepartment": 102696, "sBasisDocument": null, "idBlankSignType": 95401}

- Добавление записи в массив
  В этом случае работаем с объединением двух массивов. Необходимо задать строку с новым объектом массива и добавить её с помощью операции `||` к старому значению.

      select coalesce(w.jSign_dz, '[]':: jsonb)  ||
              '[
                  {"sFIO": "Петров Е.В.", "sPosition": "тест", "idEmployee": null, "idDepartment": 102696, "sBasisDocument": null, "idBlankSignType": 95401}
              ]' :: jsonb
      from (select
              '[
                  {"sFIO": "Степанова Е.В.", "sPosition": "Нач. отдела", "idEmployee": null, "idDepartment": 102696, "sBasisDocument": null, "idBlankSignType": 95401},
                  {"sFIO": "Линчук Владимир Владимирович", "dDate": "04.12.2019", "sPosition": null, "idEmployee": 21035, "sBasisDocument": null, "idBlankSignType": 95402}
              ]' :: jsonb as jsign_dz) w

      return:
          [
              {"sFIO": "Степанова Е.В.", "sPosition": "Нач. отдела", "idEmployee": null, "idDepartment": 102696, "sBasisDocument": null, "idBlankSignType": 95401},
              {"sFIO": "Линчук Владимир Владимирович", "dDate": "04.12.2019", "sPosition": null, "idEmployee": 21035, "sBasisDocument": null, "idBlankSignType": 95402},
              {"sFIO": "Петров Е.В.", "sPosition": "тест", "idEmployee": null, "idDepartment": 102696, "sBasisDocument": null, "idBlankSignType": 95401}
          ]

- Полная перезапись контейнера `jSign_dz`
  Можно сформировать строку при помощи обычных строковых методов и полностью перезаписать атрибут `jSign_dz`, используя приведение строки к типу `jsonb` и соблюдая синтаксис `jsonb`.

      update stk_warrantin w
      set jsign_dz = ('[{"sFIO": "' || 'Тест' || '", "sPosition": "тест", "idEmployee": null, "idDepartment": ' || '1' || ', "sBasisDocument": null, "idBlankSignType": 95401}]') :: jsonb
      where id = someId

- Разворачивание массива подписей в записи

      select value ->> 'sFIO' as sFIO
      from jsonb_array_elements(
          '[
              {"sFIO": "Степанова Е.В.", "sPosition": "Нач. отдела", "idEmployee": null, "idDepartment": 102696, "sBasisDocument": null, "idBlankSignType": 95401},
              {"sFIO": "Линчук Владимир Владимирович", "dDate": "04.12.2019", "sPosition": null, "idEmployee": 21035, "sBasisDocument": null, "idBlankSignType": 95402}
          ]' :: jsonb) v

      return:
          1: `Степанова Е.В.`
          2: `Линчук Владимир Владимирович`

### Работа с контейнером jtypesizeattrs класса bs_goods

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

- Установка или обновление значения в поле

  - Конкретный пример:

        select coalesce(jtypesizeattrs, '{}':: jsonb) || '{"width":1}':: jsonb from bs_goods where id = 458499 -- обновление значения в поле width

  - Общий пример:

        select coalesce(g.jtypesizeattrs, '{}':: jsonb) || '{"width":1}':: jsonb
        from (select '{"width": null, "height": null, "length": 6000.0, "diameter": 244.5, "thickness": 12.5, "shelf_width": null, "shelf_width2": null, "shelf_thickness": null, "shelf_thickness2": null}' :: jsonb as jtypesizeattrs) g

  - Добавление нового поля:

        select coalesce(jtypesizeattrs, '{}':: jsonb) || '{"weight":1}':: jsonb from bs_goods where id = 458499 -- добавили значение weight

- Удаление значения из объекта `jsonb`
  Аналогично удалению записи из массива.

      select g.jtypesizeattrs - 'width' -- убрали поле width
      from (select '{"width": null, "height": null, "length": 6000.0, "diameter": 244.5, "thickness": 12.5, "shelf_width": null, "shelf_width2": null, "shelf_thickness": null, "shelf_thickness2": null}' :: jsonb as jtypesizeattrs) g

- Полная перезапись контейнера
  Соблюдая синтаксис `jsonb`, можно сформировать строку при помощи обычных строковых методов и полностью перезаписать атрибут `jtypesizeattrs`, используя приведение строки к типу `jsonb`.

      update bs_goods
      set jtypesizeattrs = ('{"width": ' || '1' || ', "height": null, "length": 6000.0, "diameter": 244.5, "thickness": 12.5, "shelf_width": null, "shelf_width2": null, "shelf_thickness": null, "shelf_thickness2": null}') :: jsonb
      where id = 458499

- Разворачивание набора пар ключ–значение в записи

      select * from jsonb_each_text((select jtypesizeattrs  from bs_goods where id = 458499))

- Общий пример

      select *
      from jsonb_each_text('{"width": null, "height": null, "length": 6000.0, "diameter": 244.5, "thickness": 12.5, "shelf_width": null, "shelf_width2": null, "shelf_thickness": null, "shelf_thickness2": null}' :: jsonb )

### Работа с контейнером jIdExt_dz

Контейнер сильно схож с `jtypesizeattrs`, и все примеры по `jtypesizeattrs` валидны и для `jIdExt_dz`. Единственное отличие — ключами являются ID внешних систем из их справочника. При доступе к полям нужно приводить идентификаторы внешних систем к строковым значениям.

- Получение ID внешней системы для системы `1C_OCO`

  - Конкретный пример:

        select jidext_dz ->> (select id from btk_extsystem s where s.scode = '1C_OCO') :: varchar  --
        from bs_goods g
        where g.id = 273568

  - Общий пример:

        select jidext_dz ->> (select id from btk_extsystem s where s.scode = '1C_OCO') :: varchar
        from (select '{"2": "85f4cbd7-8308-4b52-b769-f5f3a8af4628", "52": "143180", "161": "e9e6eb40-4fe7-11dc-b011-001372387acf"}':: jsonb as jidext_dz) s

- Установка или обновление значения в поле
  Используется тот же оператор `||`.

  > Заметим, что в следующем примере оператор `||` используется для двух разных типов: сначала для строк, а потом для `jsonb`, и работает он по-разному. Сначала конкатенируются строки для получения нового значения, а затем это значение добавляется в поле `jidext_dz`.

  - Конкретный пример:

        update bs_goods g
        set jidext_dz = coalesce(jidext_dz, '{}':: jsonb) || ('{"' || (select id from btk_extsystem s where s.scode = '1C_Acc') || '":"' || (select 'тестовыйВнешнийID' from dual) || '"}') :: jsonb
        where g.id = 273568

  - Общий пример:

        select coalesce(jidext_dz, '{}':: jsonb) || ('{"' || (select id from btk_extsystem s where s.scode = '1C_Acc') || '":"' || (select 'тестовыйВнешнийID' from dual) || '"}') :: jsonb
        from (select '{"2": "85f4cbd7-8308-4b52-b769-f5f3a8af4628", "52": "143180", "161": "e9e6eb40-4fe7-11dc-b011-001372387acf"}':: jsonb as jidext_dz) s

- Полная перезапись контейнера
  Соблюдая синтаксис `jsonb`, можно сформировать строку при помощи обычных строковых методов и полностью перезаписать атрибут `jIdExt_dz`, используя приведение строки к типу `jsonb`.

  - Конкретный пример:

        update bs_goods g
        set jidext_dz = ('{"' || (select id from btk_extsystem s where s.scode = '1C_Acc') || '":"' || (select 'тестовыйВнешнийID' from dual) || '"}') :: jsonb
        where g.id = 273568

  - Общий пример:

        select ('{"' || (select id from btk_extsystem s where s.scode = '1C_Acc') || '":"' || (select 'тестовыйВнешнийID' from dual) || '"}') :: jsonb

## Индексный доступ в jsonb объектах

Postgresql позволяет индексировать поля с типом `jsonb` в двух вариантах:

1. Индексация существования ключа на верхнем уровне объекта. Поддерживает операторы `?`, `?&` и `?|` Пример индекса:

       CREATE INDEX idxSomeName ON SomeTable USING GIN (jData)

2. Индексация существования пары путь/значение. Поддерживает оператор `@>`

       CREATE INDEX idxSomeName ON SomeTable USING GIN (jData jsonb_path_ops)

Поле `jObjectAttrs_dz`, в котором хранятся значения `json-атрибутов` и `универсальных характеристик`, по умолчанию использует второй тип индекса.

### Исследование производительности запросов

#### Характеристики теста

1. База данных:

   - PostgreSQL 16.5 (Debian 16.5-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

   - 64 Gb Ram, CPU: 3.5Ghz х 8, SSD

2. Таблица содержит индексируемое поле с типом `jsonB`. Тип индекса `gin (jsonb_path_ops)`.

3. Количество строк в таблице: 80 млн.

4. Каждая строка содержит jsonb-объект, в котором 200 ключей:

   - 50 ключей, которые имеют 10 возможных значений (имена начинаются на attr_10_vals_).

   - 50 ключей, которые имеют 2 возможных значения (0 или 1) (имена начинаются на attr_bool_vals_).

   - 50 ключей, которые имеют 100 возможных значений (имена начинаются на attr_100_vals_).

   - 50 ключей, которые имеют уникальные значения (имена начинаются на attr_1_vals_).

5. Данные формировались со сдвигом на каждой формируемой строке, для получения разреженных данных.

   - Всего возможных ключей: 1600.

   - 400 ключей, которые имеют 10 возможных значений (имена начинаются на attr_10_vals_).

   - 400 ключей, которые имеют 2 возможных значения (0 или 1) (имена начинаются на attr_bool_vals_).

   - 400 ключей, которые имеют 100 возможных значений (имена начинаются на attr_100_vals_).

   - 400 ключей, которые имеют уникальные значения (имена начинаются на attr_1_vals_).

6. `ddl-выражение` для создания таблицы:

       CREATE TABLE public.tst_testobjattrs (
        scaption text NULL,
        jobj jsonb NULL
       );
       CREATE INDEX idx_tst_testobjattrs_jidext_dz ON public.tst_testobjattrs USING gin (jobj jsonb_path_ops);

7. `pg/sql-выражение` для генерации тестовых данных:

       DO $$
        begin
        FOR t IN 1 .. 800 loop
            EXECUTE ('
            with attrs as materialized (
            select ''attr_10_vals_'' || cast(t as text) as sName
                ,(select array_agg(cast(v % 10 + t as  text) || ''/'' || cast(v % 10 + t as  text)) from generate_series(t*10, t*10 + 99) v ) as vals
                ,t as nNum
            from generate_series(1, 400) t
            union all
            select ''attr_bool_vals_'' || cast(t as text) as sName
                ,(select array_agg(cast((v + t) % 2 as  text)) from generate_series(t*10, t*10 + 99) v ) as vals
                ,t as nNum
            from generate_series(401, 800) t
            union all
            select ''attr_100_vals_'' || cast(t as text) as sName
                ,(select array_agg(cast(v as  text) || ''/'' || cast(v as  text)) from generate_series(t*10, t*10 + 99) v ) as vals
                ,t as nNum
            from generate_series(801, 1200) t
            union all
            select ''attr_1_vals_'' || cast(t as text) as sName
                ,(select array_agg(case when v > 0  then cast(t as  text) || ''/'' || cast(t as  text) end) from generate_series(1, 100) v ) as vals
                ,t as nNum
            from generate_series(1201, 1600) t
            )
            insert into tst_testobjattrs
            select ''capt'' || t::text,
                (
                select jsonb_object(array_agg(a.sName), array_agg(a.vals[t % 100]))
                    from generate_series(t % 400, t % 400 + 39) v
                    cross join generate_series(1, 1201, 400) attrStep
                    join attrs a on a.nNum = v + attrStep
            ) as jObj
            from generate_series(1, 100000) t;
        ');
            commit;
        end loop;
        END $$;

8. Размер таблицы:

   - данные 208 Гб

   - индекс 56 Гб

#### Запрос на точное совпадения значения

В этом запросе точно известны все значения атрибутов, по которым требуется найти строки.

Выполняемый запрос:

    select *
      from tst_testobjattrs t
     where t.jobj @> '{"attr_10_vals_102": "103/103"}'
       and t.jobj @> '{"attr_10_vals_94": "95/95"}'
       and t.jobj @> '{"attr_10_vals_73": "74/74"}'
       and t.jobj @> '{"attr_10_vals_63": "64/64"}'
       and t.jobj @> '{"attr_1_vals_1300": "1300/1300"}'
       and t.jobj @> '{"attr_100_vals_888": "8941/8941"}'
       and t.jobj @> '{"attr_bool_vals_467": "1"}'
       and t.jobj @> '{"attr_bool_vals_494": "1"}'

План выполнения запроса. Используется индекс:

#### Запросы на неточное совпадение значения

##### Запросы like

Существующий индекс

В этой главе описывается возможности индексного доступа, при использовании оператора `like`.

Индекс `gin (jsonb_path_ops)` не обеспечивает возможность индексного доступа при использовании оператора `like`.

Выполняемый запрос:

    select *
      from tst_testobjattrs t
     where (t.jobj ->> 'attr_10_vals_4') not like '1%'

План выполнения. Полный просмотр таблицы:

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

Выполняемый запрос:

    select *
      from tst_testobjattrs t
     where t.jobj @> '{"attr_10_vals_10": "10/10"}'
       and t.jobj ->> 'attr_10_vals_4' like '1%'

План выполнения. Используется индекс:

Дополнительный функциональный индекс

Для решения задачи поиска значения, используя оператор `like`, возможно создать функциональный индекс.

Поиск по началу строки.

1. Создаем индекс с классом операторов `text_pattern_ops`:

       create index tst_testobjattrs_idx_text on tst_testobjattrs ((jobj ->> 'attr_10_vals_3') text_pattern_ops)

2. Время создания: 5 мин 20 сек. размер: 528 МБ.

3. Выполняем запрос:

       select *
         from tst_testobjattrs t
        where (t.jobj ->> 'attr_10_vals_3') like '100%'

4. План запроса, используется индекс:

Поиск по началу строки.

1. Создаем индекс с классом операторов `gin_trgm_ops`. Потребуется подключенный модуль `pg_trgm` (поставляется по умолчанию с нашей системой). Модуль который разбивает слова на триграммы и выполняет поиск по ним. Отсюда следует, что искать строки следует от 3-х символов и больше.

       ccreate index tst_testobjattrs_idx_text_trg on tst_testobjattrs using gin ((jobj ->> 'attr_10_vals_2') gin_trgm_ops)

2. Время создания: 7 мин 8 сек. размер: 98 МБ.

3. Выполняем запрос:

       select *
        from tst_testobjattrs t
       where (t.jobj ->> 'attr_10_vals_2') like '%10/%'

4. План запроса, используется индекс:

##### Запрос диапазона по числам

Для решения задачи поиска по диапазону чисел (`<`, `<=`, `>`, `>=`) возможно создать функциональный индекс.

1. Создаем индекс:

       create index tst_testobjattrs_idx_number on tst_testobjattrs (((jobj -> 'nNumber') :: numeric))

2. Время создания: 4 мин 58 сек. размер: 1.7 ГБ.

3. Выполняем запрос:

       select *
         from tst_testobjattrs t
        where (jobj -> 'nNumber') :: numeric >= 10
          and (jobj -> 'nNumber') :: numeric <= 20

4. План запроса, используется индекс:

##### Запрос диапазона по датам

В `json` нет такого типа данных как даты, потому система хранит даты в виде строк в формате `DD.MM.YYYY HH24:MI:SS`. По этому для поиска по дате необходимо создать функциональный индекс, который бы переводил строку в дату.

Давайте попробуем создать индекс:

    create index tst_testobjattrs_idx_json_date2 on tst_testobjattrs (to_date(jobj ->> 'dDate', 'DD.MM.YYYY HH24:MI:SS'))

Получим ошибку:

    SQL Error [42P17]: ОШИБКА: функции в индексном выражении должны быть помечены как IMMUTABLE

Для создания функциональный индексов требуется использовать только функции, помеченные как `IMMUTABLE`. Т.е. при одних и тех же входящих параметрах результат функции не должен изменяться. Функция `to_date` зависит от локали базы и не может использоваться при индексации.

По этому алгоритм действий следующий:

1. Создаем функцию конвертации строки в дату. Т.к. формат у нас жестко фиксирован, то результат функции всегда будет одинаковым, и мы можем пометить ее как `IMMUTABLE`:

       create function strToDate_tst(stringDate text)
        RETURNS timestamp
        LANGUAGE sql
        AS $function$
          select to_date(stringDate, 'DD.MM.YYYY HH24:MI:SS')
        $function$
        IMMUTABLE;

2. Создаем индекс:

       create index tst_testobjattrs_idx_json_date on tst_testobjattrs (strToDate_tst(jobj ->> 'dDate'))

3. Время создания: 10 мин 7 сек. размер: 528 МБ.

4. Выполняем запрос:

       select *
         from tst_testobjattrs t
        where strToDate_tst((t.jobj ->> 'dDate')) between current_date - interval '100 DAY' and current_date

5. План запроса, используется индекс:

### Выводы

1. При запросе строк по точному совпадению значения необходимо использовать оператор `@>`.

   Пример запроса:

       select *
         from tst_testobjattrs t
        where t.jobj @> '{"attr_10_vals_102": "103/103", "attr_10_vals_94": "95/95"}'

2. Для поиска по началу строки (like с процентом справа) требуется создать дополнительный функциональный индекс с классом операторов `text_pattern_ops`.

   Пример индекса:

        create index tst_testobjattrs_idx_text on tst_testobjattrs ((jobj ->> 'attr_10_vals_3') text_pattern_ops)

3. Для поиска по вхождению строки (like с процентом справа и слева) требуется наличие модуля `pg_trgm` (поставляется по умолчанию с нашей системой) создать дополнительный функциональный `gin`-индекс с классом операторов `gin_trgm_ops`.

   Пример индекса:

        create index tst_testobjattrs_idx_text_trg on tst_testobjattrs using gin ((jobj ->> 'attr_10_vals_2') gin_trgm_ops)

4. Для поиска по интервалу числовых значений требуется создать доп. функциональный индекс.

   Пример индекса:

        create index tst_testobjattrs_idx_number on tst_testobjattrs (((jobj -> 'nNumber') :: numeric))

5. Для поиска по интервалу дат требуется создать доп. функциональный индекс и использовать `immutable`-функцию конвертации строки в дату.

   Пример функции:

        create function strToDate_tst(stringDate text)
        RETURNS timestamp
        LANGUAGE sql
        AS $function$
          select to_date(stringDate, 'DD.MM.YYYY HH24:MI:SS')
        $function$
        IMMUTABLE;

   Пример индекса:

        create index tst_testobjattrs_idx_json_date on tst_testobjattrs (strToDate_tst(jobj ->> 'dDate'))

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