Good morning,
we have everywhere in code:
COALESCE(json_data, '{}'::json(b)) || json(b)_build_object(...)
or something like
IF json_data IS NULL THEN ....
I made some code refactoring with
CREATE OR REPLACE FUNCTION public.jsonb_concat_null(a jsonb, b jsonb)
RETURNS jsonb AS
$$
SELECT
CASE
WHEN a IS NOT NULL AND b IS NOT NULL
THEN a || b
WHEN a IS NULL AND b IS NULL
THEN NULL
WHEN a IS NULL
THEN b
WHEN b IS NULL
THEN a
ELSE
NULL
END;
$$ LANGUAGE SQL IMMUTABLE;
CREATE OPERATOR public.||| (
PROCEDURE = public.jsonb_concat_null,
LEFTARG = JSONB,
RIGHTARG = JSONB,
COMMUTATOR = OPERATOR(public.|||)
);
So now is possible join json data with NULL:
json_data || NULL -> NULL
json_data ||| NULL -> json_data
without coalesce or other NOT NULL check in code. Maybe can be usefull have this operator in pg? Code can be written better than my and in C. Anybody have same issue with concat json and NULL?
David T.
--
-------------------------------------
Ing. David TUROŇ
LinuxBox.cz, s.r.o.
28. rijna 168, 709 01 Ostrava
tel.: +420 591 166 224
fax: +420 596 621 273
mobil: +420 732 589 152
www.linuxbox.cz
mobil servis: +420 737 238 656
email servis: servis@xxxxxxxxxxx
-------------------------------------
