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.