CODEWITHSUNDEEP
sqljsonpostgresql
Aleksei Khatkevich
Aleksei Khatkevich

Reputation: 2197

Get rid of all empty strings values in jsonb | Postgres

I have rather abstract question on PostgreSQL jsonb data.

For example I have a table called… table_one, where I have a column:

my_jsonb_column   jsonb;

In 100% cases in contains flat json structure like

example:

{"one": null, "two": 0, "three": "" }

might be different length or null but always flat.

My goal is like that.

Whenever I select this column I need to somehow convert empty string values “” into null for each member of json where it’s value is “”

example :

{"one": null, "two": 0, "three": "" }  - > {"one": null, "two": 0, "three": null }

Preferable without PLPGsQL

Postgres version - 11

Thank you.

Upvotes: 2

Views: 3313

Answers (3)

RJ.Hwang
RJ.Hwang

Reputation: 1913

select replace('{"one": null, "two": 0, "three": "" }'::jsonb::text, ': ""',': null');

Upvotes: 0

Jim Jones
Jim Jones

Reputation: 19613

If you want to avoid writing a function, try searching for the empty string "" and using replace to change it to null

WITH j (val) AS (
  VALUES ('{"one": null, "two": 0, "three": "" }'::jsonb)
) SELECT replace(val::text,'""','null')::jsonb FROM j;

                replace                 
----------------------------------------
 {"one": null, "two": 0, "three": null}

Demo: db<>fiddle

If you want the empty strings to be replaced even if you don't say so in your SELECT, you might want to write a TRIGGER on INSERT or UPDATE to keep the jsonb column clean for future selects. Depending on your use case it might be also interesting to take a look at GENERATATED COLUMNS.

Note: as pointed out by @Stefanov.sm, a simple replace wouldn't work if there are keys with empty strings.

Upvotes: 2

Stefanov.sm
Stefanov.sm

Reputation: 13049

As a plpgsql function:

create function jsonb_es_to_null(arg_j jsonb) returns jsonb immutable strict as
$body$
 declare retval jsonb := '{}'::jsonb;
 r record;
 begin
    for r in select * from jsonb_each(arg_j) loop
        retval := retval || jsonb_build_object(r.key, case when r.value <> '""' then r.value end);
    end loop;
    return retval;
 end;
$body$ language plpgsql;

Upvotes: 1

Related Questions