Skip to main content
Question

How to load JSON data to columnar table and treat empty strings as null?

  • November 22, 2020
  • 0 replies
  • 2 views

mosheg
Forum|alt.badge.img+2
  • Participating Frequently

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)