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

Содержание

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

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

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

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

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

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

1

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

Внимание

В базе для хранения данных используется контейнер типа 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"|

Формат хранения дат в JSON#

Даты в JSON-контейнерах необходимо хранить в фиксированном формате dd.mm.yyyy. Если для значения требуется время, используется формат dd.mm.yyyy hh24:mi:ss.

В прикладном коде, в том числе в сеттерах объектных характеристик, правила сохранения даты в JSON жестко завязаны на формат dd.mm.yyyy hh24:mi:ss. Поэтому при работе с датами нельзя рассчитывать на неявное преобразование формата дат средствами БД.

Ошибка может возникнуть, если дата извлекается из JSON или формируется для JSON через неявное приведение типа. На результат такого преобразования влияют настройки БД, например параметр DateStyle.

Например, если в БД установлен параметр DateStyle = 'ISO, MDY', то дата при неявном преобразовании может интерпретироваться в формате mm.dd.yyyy, где сначала идет месяц, а затем день.

Если в JSON хранится значение:

{
  "dRegDateSEDPha": "12.05.2026"
}

то для прикладного кода это дата в формате dd.mm.yyyy, то есть 12 мая 2026 года.

При явном преобразовании формат даты задается напрямую:

select to_date(t.jobjattrs_dz ->> 'dRegDateSEDPha', 'dd.mm.yyyy')
from cnt_Contract t

Такой вариант корректен, потому что значение 12.05.2026 будет разобрано как 12 мая 2026 года.

Неправильный вариант — использовать неявное преобразование:

select cast(t.jobjattrs_dz ->> 'dRegDateSEDPha' as date)
from cnt_Contract t

В этом случае БД сама определяет, как разобрать строку с датой. При DateStyle = 'ISO, MDY' значение 12.05.2026 может быть интерпретировано как 5 декабря 2026 года, потому что формат MDY предполагает порядок месяц-день-год.

Если значение невозможно разобрать в формате, ожидаемом БД, запрос завершится ошибкой. Например, дата 25.05.2026 корректна для формата dd.mm.yyyy, но некорректна для MDY, потому что 25 не может быть номером месяца.

Аналогичная проблема возможна при формировании JSON в SQL-запросе с последующей обработкой результата в прикладном коде. Прикладной JSON-парсер ожидает дату в формате dd.mm.yyyy. Если дата была сформирована через неявное преобразование, при последующей обработке может возникнуть ошибка независимо от того, сохраняется результат в БД или только обрабатывается в прикладном коде.

Неправильный вариант формирования JSON:

select jsonb_build_object(
    'dRegDateSEDPha', t.dRegDateSEDPha
)
from cnt_Contract t

В этом случае формат даты в JSON зависит от того, как БД преобразует дату в строку.

Правильный вариант — явно сформировать строковое значение даты в нужном формате:

select jsonb_build_object(
    'dRegDateSEDPha', to_char(t.dRegDateSEDPha, 'dd.mm.yyyy')
)
from cnt_Contract t

Если нужно сохранить дату и время:

select jsonb_build_object(
    'dRegDateSEDPha', to_char(t.dRegDateSEDPha, 'dd.mm.yyyy hh24:mi:ss')
)
from cnt_Contract t

При работе с датами в JSON необходимо использовать явное преобразование:

  • при чтении даты из JSON — to_date(..., 'dd.mm.yyyy');

  • при чтении даты и времени из JSON — to_timestamp(..., 'dd.mm.yyyy hh24:mi:ss');

  • при формировании JSON в SQL-запросе — to_char(..., 'dd.mm.yyyy') или to_char(..., 'dd.mm.yyyy hh24:mi:ss').

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

Пример чтения даты из JSON

select to_date(t.jobjattrs_dz ->> 'dRegDateSEDPha', 'dd.mm.yyyy'),
       cast(t.jobjattrs_dz ->> 'dRegDateSEDPha' as date)
from cnt_Contract t

В этом примере:

  • to_date(t.jobjattrs_dz ->> 'dRegDateSEDPha', 'dd.mm.yyyy') — правильный вариант, так как формат даты задан явно;

  • cast(t.jobjattrs_dz ->> 'dRegDateSEDPha' as date) — неправильный вариант, так как результат зависит от неявного преобразования даты в БД.

Для подстановки даты из прикладного кода в SQL-запрос можно использовать метод ru.bitec.app.gtk.lang.NDate#toTimeStampStr. Метод формирует строку даты для SQL в формате dd.MM.yyyy HH:mm:ss.

Подробнее в разделе Работа с sql.

Работа с контейнером 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#

Назначение контейнера для интеграции НСИ#

Контейнер jIdExt_dz предназначен для хранения внешних идентификаторов и кодировок объектов нормативно-справочной информации (НСИ) при интеграции с внешними системами.

Поддерживаемые объекты НСИ:

  • позиции номенклатуры, спецификации, формулы;

  • технологические карты, рецепты;

  • ресурсы, отделы, складские подразделения;

  • иные справочные сущности.

Механизм работы:

  1. В каждом классе обычно присутствует поле код или системное имя, куда загружается базовая кодировка из внешней системы.

  2. Для хранения дополнительных внешних идентификаторов используется специальный JSON-атрибут jIdExt_dz типа jsonb.

  3. Ключами в контейнере выступают идентификаторы внешних систем из справочника btk_extsystem, значениями — соответствующие коды/идентификаторы объекта в этих системах.

Пример структуры данных:

{
  "2": "85f4cbd7-8308-4b52-b769-f5f3a8af4628",
  "52": "143180",
  "161": "e9e6eb40-4fe7-11dc-b011-001372387acf"
}

Технические особенности работы с данными#

Контейнер сильно схож с 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. Недостатки функциональных индексов в том, что на каждый ключ нужен отдельный индекс, что вызывает увеличение времени вставки и изменения записей таблицы, и увеличивает размер, занимаемый на диске.

Содержание