Parse JSON string down to key and values in Postgres

Question

I'm trying to parse a JSON string into a table with it's value pairs.

Here is the JSON string:

{
"route": {
    "coding": [
        {
            "code": "56",
            "display": "ORAL",
            "userSelected": true
        }
    ]
}
}

My goal is to get it into a table like such:

| parent | key          | value                 | type    |
-------------------------------
| null   | route        | {coding: [...]}       | object  |
| route  | coding       | [{"code": "56", ...}] | array   |
| route  | coding       | {"code": "56", ...}   | object  |
| coding | code         | 56                    | integer |
| coding | display      | ORAL                  | text    |
| coding | userselected | true                  | boolean |

I'm struggling with making a recursive call. I am able to either parse the array or the object, I just cannot figure out how to call one or the other based upon the type.

This is my current code:

WITH RECURSIVE temp (parent, key, value, type) AS (
SELECT parent, key, value, type
FROM t1 
UNION ALL
SELECT parent, key, value, jsonb_typeof(value) AS type
FROM (
SELECT key AS parent, (jsonb_each(value)).*
FROM temp
WHERE temp.type = 'object') AS p1              
), temp2 (parent, key, value, type) AS (
SELECT parent, key, value, type
FROM t1 
UNION ALL
SELECT parent, key, jsonb_array_elements(value), 'object' AS type
FROM temp2
WHERE temp2.type = 'array'
)
SELECT parent, key, value, type FROM temp;

Any help is greatly appreciated. Thanks.

Answer 1

In this answer you can find a query which extracts paths from a jsonb object. It can be easily modified to get your expected output:

with recursive extract_all as
(
    select
        null as parent,
        key, 
        value,
        jsonb_typeof(value) as type
    from my_table
    cross join lateral jsonb_each(jdata)
union all
    select
        key,
        coalesce(obj_key, (arr_key- 1)::text),
        coalesce(obj_value, arr_value),
        jsonb_typeof(coalesce(obj_value, arr_value))
    from extract_all
    left join lateral 
        jsonb_each(case jsonb_typeof(value) when 'object' then value end) 
        as o(obj_key, obj_value) 
        on jsonb_typeof(value) = 'object'
    left join lateral 
        jsonb_array_elements(case jsonb_typeof(value) when 'array' then value end) 
        with ordinality as a(arr_value, arr_key)
        on jsonb_typeof(value) = 'array'
    where obj_key is not null or arr_key is not null
)
select *
from extract_all;

Result:

 parent |     key      |                                value                                |  type   
--------+--------------+---------------------------------------------------------------------+---------
        | route        | {"coding": [{"code": 56, "display": "ORAL", "userSelected": true}]} | object
 route  | coding       | [{"code": 56, "display": "ORAL", "userSelected": true}]             | array
 coding | 0            | {"code": 56, "display": "ORAL", "userSelected": true}               | object
 0      | code         | 56                                                                  | number
 0      | display      | "ORAL"                                                              | string
 0      | userSelected | true                                                                | boolean
(6 rows)    

Note that array elements have no keys, so we should use their indexes to identify them. Formally, coding is not a parent of nested array elements.

Working example in rextester.