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

Question

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.

I think you'd need a subquery to do this at the moment. I'd put my validation in a PL/PgSQL procedure that looped over each array element and use that in my CHECK constraint. Consider raising this use case on the mailing list. — Craig Ringer
– Craig Ringer, Commented Jan 21, 2014 at 1:46

Community · Accepted Answer · 2017-05-23 11:45:06Z

3

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.

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Feb 16, 2014 at 10:45

Maciej Sz

12.1k9 gold badges43 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

waTeim · Accepted Answer · 2014-01-21 19:39:53Z

1

From one of the developers of JSON for Postgres

The path stuff does not support wildcards.

answered Jan 21, 2014 at 19:39

waTeim

9,2542 gold badges39 silver badges40 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related