From: | Joel Hoffman <joel(dot)hoffman(at)gmail(dot)com> |
---|---|
To: | Maciej Szopinski <maciej(dot)sz(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Is there a way to address all elements of JSON array when creating a constraint in PosgreSQL? |
Date: | 2014-01-21 19:47:43 |
Message-ID: | CAEF8rJtwAC7om=MLt2a03XQTgVL1cYhLypBHv69-ZWrixztG6g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Jan 21, 2014 at 1:34 AM, Maciej Szopinski <maciej(dot)sz(at)gmail(dot)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)));
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 }]}).
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Tanzer | 2014-01-21 20:04:39 | pg_config problems on PG9.3/Centos? |
Previous Message | Adrian Klaver | 2014-01-21 18:39:13 | Re: pg_upgrade fails: "Mismatch of relation OID in database" - 9.2.4 to 9.3.2 |