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

[Maciej Szopinski]

Hello,

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

An example to illustrate:

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

[Joel Hoffman]

Post by Maciej Szopinski
Hello,
Does PostgreSQL provide any notation/method for putting a constraint on
each element of a JSON array?
[...]
I know that this can be done by extracting products to a separate 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.

If you write a short function to help, it's possible. You would need a
subquery to make this assertion, but you can't add one directly as a check
constraint:

create table orders (data JSON);

alter table orders add check (1 <= ALL((select
array_agg((a->>'product_id')::integer) from
json_array_elements(data->'products') as a)));
ERROR: cannot use subquery in check constraint

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 ;
CREATE FUNCTION

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

insert into orders (data) values ('{"products": [{ "product_id":1 }, {
"product_id":2 }]}');
INSERT 0 1

insert into orders (data) values ('{"products": [{ "product_id":0 }, {
"product_id":2 }]}');
ERROR: new row for relation "orders" violates check constraint
"orders_data_check"
DETAIL: Failing row contains ({"products": [{ "product_id":0 }, {
"product_id":2 }]}).