Tengo el siguiente dominio:
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" );Probé varias variantes de ALL(), jsonb? 'key' y array_agg(jsonb_array_elements(VALUE)) Pero simplemente no puedo encontrar una manera de realizar esta prueba.
Use la función almacenada para eso:
-- 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 toot=# 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) );