Is there a way to address all elements of JSON arr

问题:

Does PostgreSQL provide any notation/method for putting a constraint on each element of a JSON array?

An 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 constraint on the order_id field:

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

Now I need to do the same with product_id. I can put constraint on idividual array items:

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 obviously what I'm looking for is some kind of wildcard operator for matching any JSON array element:

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

I know that this can be done by extracting products to a separate products table with a foreign key to orders. But I want to know if this is possible within single JSON column, so I can keep that in mind when designing a database schema.

回答1:

So I asked this question on PostgreSQL mailing list, as suggested by Craig Ringer, and I've got the answer.

In short the solution is to write a procedure which materializes JSON array to PostgreSQL array:

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 ;

and use that procedure in CHECK statment:

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

For more details on how this works se the answer on PostgreSQL mailing list. Credits to Joel Hoffman.

回答2:

From one of the developers of JSON for Postgres

The path stuff does not support wildcards.