PostgeSQL JSONB Column with various type of data

[Riswana Rahman <riswana@xxxxxxxxxxx>]

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,

RISWANA Technical Lead    TEMENOS India Sterling Road, Chennai d: + 91 9943613190     temenos.com

 

 

 

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.