A
A
Anton Misyagin2021-11-25 09:40:02
PostgreSQL
Anton Misyagin, 2021-11-25 09:40:02

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

And it even works, but it is not possible to add a check for extra keys here, as well as for empty strings and for the data type in the keys (there must be a string). Those. I want to exclude such options:
{"en": 6, "ru": "значение"}
{"en": "", "ru": "значение",}
{"en": "value", "ru": "значение", fr: "de valuje"}
Help modify CHECK

Answer the question

In order to leave comments, you need to log in

2 answer(s)
A
Akina, 2021-11-25
@sunnmas

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

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=afc3...
If the field is not JSONB, but JSON, then additionally convert: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2bd6...

M
Melkij, 2021-11-25
@melkij

check for extra keys, also for empty strings and for the data type in the keys (must be a string).

Take a step back and ask the question "why am I trying to fasten JSON here instead of simpler, more compact and faster plain fields"
jsonb_typeof for the data type in JSON, to check "only such keys" I even find it difficult to find something suitable for check constraint.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question