Postgres Check for Empty JSONB Fields

Question

I want to be able to ignore or prevent an INSERT from occurring if there is a JSON object with keys but no values from within a Postgres function.Here is a small, contrived example:

DROP TABLE IF EXISTS mytable;
create table mytable(
a text,
b text
);

CREATE OR REPLACE FUNCTION insert_to_table(
somedata JSONB
)
RETURNS VOID AS $$
BEGIN 
insert into mytable (a, b) select a,b from jsonb_populate_recordset(null::mytable, $1::jsonb);
END;
$$ LANGUAGE plpgsql;

select insert_to_table('[{"a":1,"b":2},{"a":3,"b":4}, {"a":null, "b": null}, {"a": null, "b": "some data"}]');

This will insert 4 records, with the first row being 1,2 and the next row being 3,4. The third row is "", "", and the forth is "", some data.

In this scenario, rows 1,2, and 4 are valid. I want to ignore 3 and prevent it from being inserted.

I do not want a blank row, and my data/table will be much larger than what is listed (roughly 20 fields in the table, and 20 key/value pairs in the JSON).

Most likely I will need to loop over the array and pick out JSON object where ALL the keys are null and not just 1 or 2.

How can I do that?

score 2 · Accepted Answer · 2017-11-17 16:40:57Z

2

In Postgres you can refer to a complete row using the name of the table (alias) in the query and compare that to NULL. A record is considered NULL if all columns are null. So you can do:

create or replace function insert_to_table(somedata jsonb)
returns void as $$
begin 
    insert into mytable (a, b) 
    select a, b 
    from jsonb_populate_recordset(null::mytable, somedata) as t
    where not (t is null);
end;
$$ language plpgsql;

Note that where t is not null is something different then where not (t is null). This works regardless of the number of columns or their data types.

To visualize the logic. The following:

select a,b,
       not (t is null) as "not (t is null)", 
       t is null as "t is null", 
       t is not null as "t is not null"
from jsonb_populate_recordset(null::mytable, 
       '[{"a":1,"b":2},
         {"a":3,"b":4}, 
         {"a":null, "b": null}, 
         {"a": null, "b": "some data"}]'::jsonb) as t(a,b)

returns:

a | b         | not (t is null) | t is null | t is not null
--+-----------+-----------------+-----------+--------------
1 | 2         | true            | false     | true         
3 | 4         | true            | false     | true         
  |           | false           | true      | false        
  | some data | true            | false     | false

Unrelated:

The cast $1::jsonb is useless as you have declared the parameter of that type already.

edited Nov 17, 2017 at 16:40

answered Nov 16, 2017 at 21:47

user330315

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

1 Comment

unseen_damage Over a year ago

Perfect! Thanks for the tips. One note - There is a missing t after the as in the first function example.

Collectives™ on Stack Overflow

Postgres Check for Empty JSONB Fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related