I have the following domain:
CREATE DOMAIN foo AS JSONB
NOT NULL
CONSTRAINT is_valid CHECK (
jsonb_typeof(VALUE) = 'array'
-- AND is each element of array an obj
-- AND does each element of array have a key "id"
);
I've tried several variants of ALL(), jsonb ? 'key', and array_agg(jsonb_array_elements(VALUE)) But I just can't figure out a way to get this test done.
Use stored function for that:
-- drop table if exists t;
-- drop function if exists f(jsonb);
create function f(jsonb) returns bool language plpgsql immutable as $$
begin
if jsonb_typeof($1) <> 'array' then
return false;
else
return (
select bool_and(jsonb_typeof(j) = 'object' and j ? 'id')
from jsonb_array_elements($1) as a(j));
end if;
end $$;
-- test the function
select f('[{"id":1}]'::jsonb), f('{"id":1}'::jsonb), f('[{"id":1},"id"]'::jsonb);
create table t(x jsonb check (f(x)));
insert into t values('[{"id":1}]'); -- success
insert into t values('{"id":1}'); -- fail
insert into t values('[{"id":1},"id"]'); -- fail too
t=# create or replace function s89(_v jsonb) returns boolean as $$
declare
_r boolean;
_c int;
_t text;
begin
with b as (
with t as (
select _v "value"
)
select
jsonb_array_elements(value)->>'id' id
, jsonb_array_length(value) c
from t
)
select array_length(array_agg(id),1) = c,c,array_agg(id)::text into _r, _c,_t
from b
where id is not null
group by c;
raise info '%',_c||_t;
return _r;
end;
$$ language plpgsql
;
CREATE FUNCTION
t=# select s89('[{"id":3},{"id":4},4]'::jsonb);
INFO: 3{3,4}
s89
-----
f
(1 row)
t=# select s89('[{"id":3},{"idr":4}]'::jsonb);
INFO: 2{3}
s89
-----
f
(1 row)
t=# select s89('[{"id":3},{"id":4}]'::jsonb);
INFO: 2{3,4}
s89
-----
t
(1 row)
t=# CREATE DOMAIN foo AS JSONB
NOT NULL
CONSTRAINT is_valid CHECK (
jsonb_typeof(VALUE) = 'array' and s89(VALUE)
);