Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

--Transforming JSON into PostgreSQL table

--I've got this JSON data, and what I want to do is organize 
--its keys and values into a table format:
create table my_table(my_json)as values
('[
    {
        "home": "team1",
        "away": "team2",
        "odds": {
            "periods": {
                "1": {
                    "scoring_types": {
                        "1": {
                            "bet_types": {
                                "1": {
                                    "outcomes": {
                                        "1": 1.85,
                                        "3": 3.85,
                                        "2": 6.50
                                    },
                                    "param": null
                                },
                                "2": {
                                    "outcomes": {
                                        "1": 1.9,
                                        "2": 1.9
                                    },
                                    "param": 8.5
                                }
                            }
                        }
                    }
                }
            }
        }
    }
]'::json);
--The resulting table should look like this:

with example_result(
  home   ,away,scoring_type_id,periods_id,bet_types,outcomes,param,value)as (values
 ('team1','team2',	1,	1,	1,	1,	null,	1.85)
,('team1','team2',	1,	1,	1,	2,	null,	6.5)
,('team1','team2',	1,	1,	1,	3,	null,	3.85)
,('team1','team2',	1,	1,	2,	1,	8.5,	1.9)
,('team1','team2',	1,	1,	2,	2,	8.5,	1.9))
select * from example_result;

--The keys representing column names: 
--home,  away , scoring_type_id , periods_id, bet_types , outcomes,  param, value 
--are consistent and always present. 
--However, keys that aren't column names can vary in number, 
--such as multiple types of periods and scoring_types. 
--The goal is to generate a row for each decimal value found under outcomes, 
--while maintaining the parent keys as displayed in the table.

--JSON version
select elem->>'home' as home,
       elem->>'away' as away,
       scoring_type_id,
       periods_id,
       bet_type_id as bet_types,
       outcomes,
       bet_type->>'param' as param,
       value 
from my_table
,json_array_elements(my_json) as a(elem)
,json_each(elem->'odds') as e(e_key,periods)
,json_each(periods)      as p1(periods_id,period)
,json_each(period)       as p2(p2_key,scoring_types)
,json_each(scoring_types)as s1(scoring_type_id,scoring_type)
,json_each(scoring_type) as s2(s2_key,bet_types)
,json_each(bet_types)    as b1(bet_type_id,bet_type)
,json_each(bet_type->'outcomes')     as b2(outcomes,value)

SELECT 1

home	away	scoring_type_id	periods_id	bet_types	outcomes	param	value
team1	team2	1	1	1	1	null	1.85
team1	team2	1	1	1	2	null	6.5
team1	team2	1	1	1	3	null	3.85
team1	team2	1	1	2	1	8.5	1.9
team1	team2	1	1	2	2	8.5	1.9

SELECT 5

home	away	scoring_type_id	periods_id	bet_types	outcomes	param	value
team1	team2	1	1	1	1	null	1.85
team1	team2	1	1	1	3	null	3.85
team1	team2	1	1	1	2	null	6.50
team1	team2	1	1	2	1	8.5	1.9
team1	team2	1	1	2	2	8.5	1.9

SELECT 5

jsonb_pretty	n	jsonb_pretty	e_key	jsonb_pretty	periods_id	jsonb_pretty	p2_key	jsonb_pretty	scoring_type_id	jsonb_pretty	s2_key	jsonb_pretty	bet_type_id	jsonb_pretty	param	outcomes	value
[ { "away": "team2", "home": "team1", "odds": { "periods": { "1": { "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } } } } } ]	1	{ "away": "team2", "home": "team1", "odds": { "periods": { "1": { "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } } } } }	periods	{ "1": { "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } } }	1	{ "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } }	scoring_types	{ "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } }	1	{ "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } }	bet_types	{ "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } }	1	{ "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }	null	1	1.85
[ { "away": "team2", "home": "team1", "odds": { "periods": { "1": { "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } } } } } ]	1	{ "away": "team2", "home": "team1", "odds": { "periods": { "1": { "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } } } } }	periods	{ "1": { "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } } }	1	{ "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } }	scoring_types	{ "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } }	1	{ "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } }	bet_types	{ "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } }	1	{ "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }	null	3	3.85
[ { "away": "team2", "home": "team1", "odds": { "periods": { "1": { "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } } } } } ]	1	{ "away": "team2", "home": "team1", "odds": { "periods": { "1": { "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } } } } }	periods	{ "1": { "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } } }	1	{ "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } }	scoring_types	{ "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } }	1	{ "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } }	bet_types	{ "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } }	1	{ "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }	null	2	6.50
[ { "away": "team2", "home": "team1", "odds": { "periods": { "1": { "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } } } } } ]	1	{ "away": "team2", "home": "team1", "odds": { "periods": { "1": { "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } } } } }	periods	{ "1": { "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } } }	1	{ "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } }	scoring_types	{ "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } }	1	{ "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } }	bet_types	{ "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } }	2	{ "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } }	8.5	1	1.9
[ { "away": "team2", "home": "team1", "odds": { "periods": { "1": { "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } } } } } ]	1	{ "away": "team2", "home": "team1", "odds": { "periods": { "1": { "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } } } } }	periods	{ "1": { "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } } }	1	{ "scoring_types": { "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } } }	scoring_types	{ "1": { "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } } }	1	{ "bet_types": { "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } } }	bet_types	{ "1": { "param": null, "outcomes": { "1": 1.85, "2": 6.50, "3": 3.85 } }, "2": { "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } } }	2	{ "param": 8.5, "outcomes": { "1": 1.9, "2": 1.9 } }	8.5	2	1.9

SELECT 5