sqljsonpostgresql

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.


Solution

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