Работа с данными, хранящимися в 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 в двух вариантах:

Индексация существования ключа на верхнем уровне объекта. Поддерживает операторы ?, ?& и ?| Пример индекса:
```
CREATE INDEX idxSomeName ON SomeTable USING GIN (jData)
```
Индексация существования пары путь/значение. Поддерживает оператор @>
```
CREATE INDEX idxSomeName ON SomeTable USING GIN (jData jsonb_path_ops)
```

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

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

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

База данных:
- 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
Таблица содержит индексируемое поле с типом jsonB. Тип индекса gin (jsonb_path_ops).
Количество строк в таблице: 80 млн.
Каждая строка содержит jsonb-объект, в котором 200 ключей:
- 50 ключей, которые имеют 10 возможных значений (имена начинаются на attr_10_vals_).
- 50 ключей, которые имеют 2 возможных значения (0 или 1) (имена начинаются на attr_bool_vals_).
- 50 ключей, которые имеют 100 возможных значений (имена начинаются на attr_100_vals_).
- 50 ключей, которые имеют уникальные значения (имена начинаются на attr_1_vals_).
Данные формировались со сдвигом на каждой формируемой строке, для получения разреженных данных.
- Всего возможных ключей: 1600.
- 400 ключей, которые имеют 10 возможных значений (имена начинаются на attr_10_vals_).
- 400 ключей, которые имеют 2 возможных значения (0 или 1) (имена начинаются на attr_bool_vals_).
- 400 ключей, которые имеют 100 возможных значений (имена начинаются на attr_100_vals_).
- 400 ключей, которые имеют уникальные значения (имена начинаются на attr_1_vals_).

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);

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 $$;  

Размер таблицы:
- данные 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, возможно создать функциональный индекс.

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

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

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

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

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

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

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

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

Создаем индекс с классом операторов 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)   
```
Время создания: 7 мин 8 сек. размер: 98 МБ.

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

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

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

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

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

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

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

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

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

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

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

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

В 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 зависит от локали базы и не может использоваться при индексации.

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

Создаем функцию конвертации строки в дату. Т.к. формат у нас жестко фиксирован, то результат функции всегда будет одинаковым, и мы можем пометить ее как 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'))   

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

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

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

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

Выводы#

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

Пример запроса:
```
select *
  from tst_testobjattrs t
 where t.jobj @> '{"attr_10_vals_102": "103/103", "attr_10_vals_94": "95/95"}'
```
Для поиска по началу строки (like с процентом справа) требуется создать дополнительный функциональный индекс с классом операторов text_pattern_ops.

Пример индекса:
```
 create index tst_testobjattrs_idx_text on tst_testobjattrs ((jobj ->> 'attr_10_vals_3') text_pattern_ops)
```
Для поиска по вхождению строки (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)   
```
Для поиска по интервалу числовых значений требуется создать доп. функциональный индекс.

Пример индекса:
```
 create index tst_testobjattrs_idx_number on tst_testobjattrs (((jobj -> 'nNumber') :: numeric))
```

Для поиска по интервалу дат требуется создать доп. функциональный индекс и использовать 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'))   

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

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

Содержание

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

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

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

Запись по id#

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

Чтение по id#

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

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

Запись UC по id#

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

Чтение UC по id#

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

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

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

Примечания#

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

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

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

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

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

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

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

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

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

Запросы like#

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

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

Выводы#