Answer the question
In order to leave comments, you need to log in
How to set validator for json/jsonb field?
Good afternoon, the task is to validate the data in the jsonb field at the database level. Template for data:
{"en": "value", "ru": "значение"}
Here is what he wrote:
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"}
Answer the question
In order to leave comments, you need to log in
You convert to a string (jsonb -> text of json) and check with a regular expression.
select jsonb_value,
jsonb_value::text ~ '^{"en": "[^"]+", "ru": "[^"]+"}$'
from test
check for extra keys, also for empty strings and for the data type in the keys (must be a string).
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question