CODEWITHSUNDEEP
arraysjsonpostgresqlvalidation
shawon191
shawon191

Reputation: 1955

Postgresql json field array item validation

I want to validate a postgres json field such that every item in an array inside the json contains certain properties. For example, let's say I have a json field which contains an array of objects contacts, I want each object in contacts always have a name and phone property. Like the following-

{
    "contacts": [
        { "name": "a", "phone": "123" },
        { "name": "b", "phone": "456" }
    ]
}

Is there a builtin way to validate this so that this format is always maintained on insert/update?

Upvotes: 1

Views: 471

Answers (1)

klin
klin

Reputation: 121654

If the json document has a rigid structure, it is better to save this data in a regular table(s). I assume that the objects contain many keys including a few mandatory.

The following function checks whether each object in a json array (first argument) contains all array strings as top-level keys (second argument).

create or replace function jsonb_has_keys(jsonb, text[])
returns boolean language sql as $$
    select bool_and(value ?& $2)
    from jsonb_array_elements($1)
$$;

Use the function in a check constraint, e.g.:

create table test(
    data jsonb check (jsonb_has_keys(data->'contacts', array['name', 'phone']))
);

insert into test values
('{
    "contacts": [
        { "name": "a", "phone": "123" },
        { "name": "b", "tel": "456" }
    ]
}'::jsonb);

ERROR:  new row for relation "test" violates check constraint "test_data_check"
DETAIL:  Failing row contains ({"contacts": [{"name": "a", "phone": "123"}, {"tel": "456", "nam...).

Upvotes: 1

Related Questions