Is there a way to address all elements of a JSON array when creating a constraint in PosgreSQL?

Question

Does PostgreSQL provide any notation / method for limiting the restriction on each element of the JSON array?

Example:

create table orders(data json);

insert into orders values ('
{
    "order_id": 45,
    "products": [
        {
            "product_id": 1,
            "name": "Book"
        },
        {
            "product_id": 2,
            "name": "Painting"
        }
    ]
}
');

I can easily add a field constraint order_id:

alter table orders add check ((data->>'order_id')::integer >= 1);

Now I need to do the same with product_id. I can set a restriction on individual elements of the array:

alter table orders add check ((data->'products'->0->>'product_id')::integer >= 1);
alter table orders add check ((data->'products'->1->>'product_id')::integer >= 1);
-- etc.

So it’s obvious that I'm looking for some kind of wildcard operator for that matches any element of the JSON array :

alter table orders add check ((data->'products'->*->>'product_id')::integer >= 1);
--                                               ^ like this

, , products orders. , JSON, .

Answer 1

, PostgreSQL, Craig Ringer, .

, , , JSON PostgreSQL:

create function data_product_ids(JSON) returns integer[] immutable  as $$
select array_agg((a->>'product_id')::integer) from
json_array_elements($1->'products') as a $$ language sql ;

CHECK statment:

alter table orders add check (1 <= ALL(data_product_ids(data)));

, se PostgreSQL. .

Answer 2

JSON Postgres

.