I've tried the following:
DROP TABLE my_json CASCADE;
CREATE TABLE my_json ( name varchar(50), age int, eyes varchar(50), weapon varchar(50));
COPY my_json
( name,
age,
eyes_1 FILLER varchar(50),
eyes AS CASE eyes_1 IS NULL
WHEN TRUE THEN NULL
ELSE eyes_1
END,
weapon )
FROM STDIN
PARSER fjsonparser(omit_empty_keys=true);
{
"name": "sierra with empty eyes",
"age": "63",
"eyes": "",
"weapon": "doggie"
}
{
"name": "janis",
"age": "10",
"eyes": "blue",
"weapon": "humor"
}
\.
SELECT name, age, ifnull(eyes,'NULL'), weapon FROM my_json ORDER BY 1;
-- name | age | ifnull | weapon
-- ------------------------+-----+--------+--------
-- janis | 10 | NULL | humor
-- sierra with empty eyes | 63 | NULL | doggie
-- (2 rows)
DROP TABLE my_json CASCADE;
CREATE TABLE my_json ( name varchar(50), age int, eyes varchar(50), weapon varchar(50));
COPY my_json
( name, age, eyes, weapon )
FROM STDIN
PARSER fjsonparser();
{
"name": "sierra with empty eyes",
"age": "63",
"eyes": "",
"weapon": "doggie"
}
{
"name": "janis",
"age": "10",
"eyes": "blue",
"weapon": "humor"
}
\.
SELECT name, age, ifnull(eyes,'NULL'), weapon FROM my_json ORDER BY 1;
-- name | age | ifnull | weapon
-- ------------------------+-----+--------+--------
-- janis | 10 | blue | humor
-- sierra with empty eyes | 63 | | doggie
-- (2 rows)