Postgresql recursively parsing nested JSON

This is complimentary along side specific problem listed in this thread. I am sharing a code snippet to perform parsing on all the key/value attributes recursively until the leaf level. This directly is not improving on the problem but for people who land here looking for recursive traversing of json and breaking them into key value with leaf node or level flag.

The psql snippet parses all the attributes and their respective value along the way it returns a flag if_leaf_node. Its improvisation on top of what has already been posted in this stack thread. But for benefit of others like me who are landing here to parse not just a known array structure but also any key/value structure that might have nesting with complex json schema.

Note : This is yet to take care of the array type. But the rest it parses until the leaf. We can combine this and the above solution to get complete parsing of json.

If get the array working will post again alongside this key/value attribute solution. But array is tricky and depends on how someone has built array object therefore needs some testing and work.

Refer to the fiddle with working solution here, https://dbfiddle.uk/kBL9gRYD

It contains two parts. One is to validate if the given text is a true json. This function aids in terminating the recursion at the leaf node and also flag if we have reached the leaf attribute. Do remember in postgres(until 14.x) even a double quoted string is treated has a good json by postgres jsonb typecasting. Therefore, just simple calling a ::jsonb on text will not help in some instances. For example,

 select '"test this is json"'::jsonb -- returns as good json

Therefore, had to built some quick way to perform a strict check via the below function f_is_json.

CREATE OR REPLACE FUNCTION f_is_json(_txt text)
  RETURNS bool
  LANGUAGE plpgsql IMMUTABLE STRICT AS
$func$
BEGIN
   RETURN (select TRUE from (select jsonb_each(_txt::jsonb)) s LIMIT 1);
EXCEPTION
   WHEN SQLSTATE '22023' THEN   RETURN false; -- invalid_text_representation
   WHEN SQLSTATE '22P02' THEN   RETURN false;
END
$func$;

The core logic using the recursion is given below. You can refer to the fiddle with working example as well. This was built in crude way and limited testing done. Happy to hear any improvements on performance and any tuning.

WITH RECURSIVE jsondata AS (
    SELECT 
        isLeafElement,
        key,
        value as childvalue
    FROM (
     SELECT 
            CASE WHEN f_is_json(value::text) THEN 1
            ELSE 0
            END as isLeafElement,
            key, 
            value
                from jsonb_each('{  "entity": "12345",
                                    "msg":"i am the data to be search against the entity entered for 12345",
                                    "arr" : [{"key1":"val1"},{"key2":"val2"}],
                                    "nested" : {
                                                    "secondlevel1" : "value at second level 1",
                                                    "secondlevel2" : "value at second level 2",
                                                    "arr2" : [{"keyarr2":"val2"},{"keyarr2":"val2"}],
                                                    "2ndNesting":{"thirdlevel2" : "value at third level 2"}
                                                  }
                                }'::jsonb)
    ) s

    UNION

    SELECT
        CASE WHEN f_is_json(childvalue::text) THEN 1
        ELSE 0
        END as isLeafElement,
        c.key,
        c.value as childvalue
    FROM jsondata,
    jsonb_each(jsondata.childvalue) c
    WHERE f_is_json(childvalue::text)
    
)
SELECT 
    CASE WHEN f_is_json(childvalue::text) THEN 1
        ELSE 0
        END as isLeafElement,
    key,
    childvalue
from jsondata;