Get rid of all empty strings values in jsonb | Postgres

Question

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.

maybe too simplistic to just search for "" and replace with null? --> WITH j (val) AS ( VALUES ('{"one": null, "two": 0, "three": "" }'::jsonb)) SELECT replace(val::text,'""','null')::jsonb FROM j — Jim Jones
– Jim Jones, Commented Mar 11, 2021 at 18:10
Nice and clean, but unfortunately "" is a valid JSON key and null is not :) — Stefanov.sm
– Stefanov.sm, Commented Mar 11, 2021 at 18:25
@Stefanov.sm you're right.. I was just considering values, not keys... thanks for pointing out! — Jim Jones
– Jim Jones, Commented Mar 11, 2021 at 18:35

Jim Jones · Accepted Answer · 2021-03-11 18:38:48Z

2

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.

edited Mar 11, 2021 at 18:38

answered Mar 11, 2021 at 18:32

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stefanov.sm Over a year ago

To avoid certain cases (empty string keys, values like "this\"") better use regexp_replace(val::text, '[^\\]("")[^:]?', 'null') instead of replace(val::text,'""','null'). The regexp can be further improved.

Stefanov.sm · Accepted Answer · 2021-03-11 18:35:15Z

1

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;

answered Mar 11, 2021 at 18:35

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Comments

RJ.Hwang · Accepted Answer · 2024-09-15 06:42:24Z

0

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

answered Sep 15, 2024 at 6:42

RJ.Hwang

1,9431 gold badge19 silver badges26 bronze badges

Collectives™ on Stack Overflow

Get rid of all empty strings values in jsonb | Postgres

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related