On Tue, Jan 21, 2014 at 1:34 AM, Maciej Szopinski <maciej.sz@gmail.com> wrote:
Hello,
Does PostgreSQL provide any notation/method for putting a constraint on each element of a JSON array?
An example to illustrate:
[...]
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)));
