not null constraint inside a jsonb array in postgres

user_5667911 0
Viewed 686

How to have not null constraint inside a jsonb column in Postgres.

I have created a Postgres table with just one column called id like this below

create table tablea (
  id jsonb, 
  check 
  ((id->>'test1', id->>'test2') != (null, null))
);

The caller will insert data into the table in the below json format:-

[
    {
        "test1":"",
        "test2":"",
        "test3":""
    },
    {
        "test1":"",
        "test2":"",
        "test3":""
    }
]

My goal is to when a caller insert data in the id column i want the key test1 and test2 be not null. How can in achieve that. My table creation logic is explained above. I am trying to insert data like

insert into tablea(id) values 
 ('[{"test1":null,"test2":"a","test3":""}]');

Ideally this insert statement should throw me error but it is inserting data in the table. Can anyone help me out

jsonb check-constraints postgresql arrays
1 Answers

You will need to create a function that iterates through your array and validates every array element.

Something like this:

create or replace function validate_json(p_input jsonb)
  returns boolean
as
$$
  select not exists (select *
                      from jsonb_array_elements(p_input) as t(element)
                      where nullif(element ->> 'test1', '') is null
                         or nullif(element ->> 'test2', '') is null);
$$
language sql 
stable;

Then you can use it to define a check constraint:

You can't compare null with = or <>. You need to use IS NOT NULL for that.

It also seems you want to treat an empty string the same way as null.

create table tablea 
(
  id jsonb,
  constraint check_json check ( validate_json(id) ) 
);
user_3303150
Related