PostgeSQL JSONB Column with various type of data

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.png@01D46E21.E2244170

RISWANA
Technical Lead 

 

TEMENOS India

Sterling Road, Chennai
d: + 91 9943613190

 

cid:image002.png@01D45B44.8C6E0030cid:image003.png@01D45B3E.3EA72B70cid:image004.png@01D45B3E.3EA72B70cid:image005.png@01D45B3E.3EA72B70  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.

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux