not null constraint inside a jsonb array in postgres

Question

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

score 1 · Answer 1 · 2020-07-27T13:00:01.383

1

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) ) 
);

edited Jul 27 '20 at 13:00

answered Jul 27 '20 at 12:01

tried you way but the query insert into tablea(id) values ('[{"test1":"abc","test2":"a","test3":""}]'); seems to be failing. it is showing me new row for relation "tablea" violates check constraint "tablea_id_check – Abhishek Jul 27 '20 at 12:40
@Abhishek: ah, I didn't see that you are inserting arrays there. That makes the check much more complicated. Which Postgres version are you using? – Jul 27 '20 at 12:47
PostgreSQL 10.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit – Abhishek Jul 27 '20 at 12:52

not null constraint inside a jsonb array in postgres

1 Answers1