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.

Answer

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.