PostgeSQL JSONB Column with various type of data - Mailing list pgsql-performance

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.

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,