Postgresql json field array item validation

Question

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?

If you have such a defined structure, then why don't you normalize your data model? — user330315
– user330315, Commented May 29, 2017 at 16:24
@a_horse_with_no_name This is just an example. The document in my use case is not as strictly structured. Normalization is possible, but it requires 2-3 intermediate tables for each of the entity it relates with(my current database is using this form, but I think querying the current form requires to many joins, so json might be a better fit). — shawon191
– shawon191, Commented May 29, 2017 at 16:38

klin · Accepted Answer · 2017-05-29 17:20:15Z

1

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

edited May 29, 2017 at 17:20

answered May 29, 2017 at 17:11

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shawon191 Over a year ago

While not very strict, the json document I want to keep requires 2 intermediate tables for 3 of its related entities, and the number is very much likely to increase in the future. The document will usually be inserted and accessed as a whole and in most cases it won't be updated after insertion. So I figured storing it as a json document is better for both development and performance. Please correct me if I'm wrong.

klin Over a year ago

With these assumptions your approach seems to be rational.

Collectives™ on Stack Overflow

Postgresql json field array item validation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related