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

From: Maciej Szopinski <maciej(dot)sz(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Is there a way to address all elements of JSON array when creating a constraint in PosgreSQL?
Date: 2014-01-21 09:34:43
Message-ID: CACYP-QPiPfgGz6U_Q87p4fwkiFP9JOx6h9T0e4zsXZi8QD2wTA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stefan Warten 2014-01-21 13:42:09 pg_upgrade fails: "Mismatch of relation OID in database" - 9.2.4 to 9.3.2
Previous Message Albe Laurenz 2014-01-21 08:46:24 Re: to_date() and invalid dates