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.
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.