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:[email protected]]
RISWANA
Technical Lead
TEMENOS India
Sterling Road, Chennai
d: + 91 9943613190
[cid:[email protected]]<https://www.linkedin.com/company/temenos/>[cid:[email protected]]<https://twitter.com/Temenos>[cid:[email protected]]<https://www.facebook.com/TemenosGroup>[cid:[email protected]]<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.
