Как задать валидатор для поля json/jsonb?

Question

[Антон Мисягин]

Добрый день, стоит задача валидировать данные в поле jsonb на уровне базы. Шаблон для данных:
{"en": "value", "ru": "значение"}
Вот что написал:

CREATE DOMAIN json_localized AS jsonb CHECK (VALUE->'ru' IS NOT NULL AND VALUE->'en' IS NOT NULL);

И это уже даже работает, но не получается добавить сюда проверку на лишние ключи, так же на пустые строки и на тип данных в ключах (должна быть строка). Т.е. такие варианты хочу исключить:
{"en": 6, "ru": "значение"}
{"en": "", "ru": "значение",}
{"en": "value", "ru": "значение", fr: "de valuje"}
Помогите модифицировать CHECK

Answer 1

[Akina]

Конвертируешь в строку (jsonb -> text of json) и проверяешь регуляркой.

select jsonb_value,
       jsonb_value::text ~ '^{"en": "[^"]+", "ru": "[^"]+"}$' 
from test

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=afc3...

Если поле не JSONB, а JSON, то дополнительно конвертируешь: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2bd6...

Comments

[Антон Мисягин]

мне кажется это не то что я хочу. Мне не нужно ничего конвертировать (да и нечего пока), я лишь хочу добавить ограничения на формат объекта, чтобы кидалась ошибка если инсертятся вот три последние примера

[Akina]

Антон Мисягин, o_O А заколотить выражение в CHECK constraint мысль не приходила? я же его даже специально сделал таким, чтобы оно boolean вернуло...

[Антон Мисягин]

Akina, знаний не хватает как этот констрайнт задать) Помоги плиз

[Akina]

Антон Мисягин, https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a3e2...

Обращаю внимание - такое ограничение допускает NULL. Если они не допускаются - ограничивай на уровне свойства поля NOT NULL.

[Антон Мисягин]

Да, это то что нужно!! спасибо
CREATE DOMAIN json_localized AS jsonb CHECK (VALUE::text ~ '^{"en": "[^"]+", "ru": "[^"]+"}$');

Answer 2

[Melkij]

проверку на лишние ключи, так же на пустые строки и на тип данных в ключах (должна быть строка).

Сделать шаг назад и задать вопрос "а зачем я здесь пытаюсь прикрутить JSON вместо более простых, компактных и быстрых plain полей"

jsonb_typeof для типа данных в JSON, для проверки "только такие ключи" сходу даже затрудняюсь что-то найти подходящее для check constraint.

Comments

[Антон Мисягин]

Никаких шагов назад. Надо и все))