PostgeSQL JSONB Column with various type of data

Hi Team,

We are using Postgresql JSONB as storage type in our development.
In the below table , RECORD column has JSONB data and we create a view which will derive the column "TEST_MV_2" from column "RECORD" as below

CREATE OR REPLACE VIEW public."V_TEST_SELECT"
AS
SELECT a.recid, a.record AS "RECORD",
jsonb_path_query(a.xmlrecord, '$."2"'::jsonpath) AS "TEST_MV_2 "
FROM " TEST_SELECT " a;

So we might have array of data or an empty JSON object or an array of empty JSON object or a string in the column "TEST_MV_2".
Null is stored as empty JSON object due to our business logic.

RECID
RECORD (datatype: JSONB)
TEST_MV_2 (datatype: JSONB)
"SELTEST1"
"{"1": "SELTEST1", "2": [{"": "TESTVALUE"}, {}]}"
[{"": "TESTVALUE"}, {}]
"SELTEST2"
"{"1": "SELTEST2", "2": "TESTVALUE"}"
"TESTVALUE"
"SELTEST3"
"{"1": "SELTEST3", "2": [{"": "TESTVALUE"}, {"": "TESTVALUE1"}]}"
[{"": "TESTVALUE"}, {"": "TESTVALUE1"}]
"SELTEST4"
"{"1": "SELTEST4", "2": [{"": "TESTVALUE4MV1"}, {}]}"
[{"": "TESTVALUE4MV1"}, {}]
"SELTEST5"
"{"1": "SELTEST5", "2": [{}, {}]}"
[{},{}]
"SELTEST6"
"{"1": "SELTEST6", "2": {}}"
{}
"SELTEST7"
"{"1": "SELTEST7", "2": [{}, {"": "TESTVALUE"}]}"
[{}, {"": "TESTVALUE"}]

In such cases, to find the null values in the JSONB, I have written below SQL Function to handle different type of data

CREATE OR REPLACE FUNCTION jsonbNull(jsonb_column JSONB)
returns boolean as $$
declare
isPoint text := jsonb_typeof(jsonb_column) ;
begin
CASE isPoint
WHEN 'array' THEN
if true = ALL(select (jsonb_array_elements(jsonb_column)) = '{}') THEN
return true;
else
return false;
end if;
WHEN 'object' THEN
if jsonb_column = '{}' THEN
return true;
else
return false;
end if;
WHEN 'string' THEN
return false;
ELSE
return true;
END CASE;
end;
$$ LANGUAGE plpgsql IMMUTABLE;

Sample SQL statement used:
SELECT RECID,"TEST_MV_2" FROM "V_TEST_SELECT" WHERE true=jsonbNull("TEST_MV_2") ORDER BY RECID ;

I would like to know whether we can handle multiple types of JSONB data in a better/nicer way as this function could impact performance of the query.

Kindly provide your suggestions.

Thanks,
[cid:image001(dot)png(at)01D46E21(dot)E2244170]
RISWANA
Technical Lead

TEMENOS India
Sterling Road, Chennai
d: + 91 9943613190

[cid:image002(dot)png(at)01D45B44(dot)8C6E0030]<https://www.linkedin.com/company/temenos/>[cid:image003(dot)png(at)01D45B3E(dot)3EA72B70]<https://twitter.com/Temenos>[cid:image004(dot)png(at)01D45B3E(dot)3EA72B70]<https://www.facebook.com/TemenosGroup>[cid:image005(dot)png(at)01D45B3E(dot)3EA72B70]<https://www.youtube.com/user/TemenosMarketing> temenos.com<http://www.temenos.com/?utm_source=signature&utm_medium=email&utm_campaign=new-signature&utm_content=email>

The information in this e-mail and any attachments is confidential and may be legally privileged. It is intended solely for the addressee or addressees. Any use or disclosure of the contents of this e-mail/attachments by a not intended recipient is unauthorized and may be unlawful. If you have received this e-mail in error please notify the sender. Please note that any views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of TEMENOS. We recommend that you check this e-mail and any attachments against viruses. TEMENOS accepts no liability for any damage caused by any malicious code or virus transmitted by this e-mail.