Postgres 16

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

--mock
create function http_post(a text,b text,c text) returns table(CONTENT text) as $f$
select '{"data":{"sportMarkets":[
         {"id": "9f4a2", "tags": ["9006"], "total": null, "betType": "10001", "awayOdds": "203707333464004703", "awayTeam": "Ottawa Senators", "drawOdds": "0", "homeOdds": "796292666535995296", "homeTeam": "Los Angeles Kings", "isPaused": false},
         {"id": "9f4a3", "tags": ["9007","9005"], "total": null, "betType": "10002", "awayOdds": "'||(random()*203707333464004703)::bigint||'", "awayTeam": "Nebraska Governors", "drawOdds": "0", "homeOdds": "'||(random()*796292666535995296)::bigint||'", "homeTeam": "Los Santos Bloods", "isPaused": true},
         {"id": "9f4a2", "tags": ["9006"], "total": 5, "betType": "10003", "awayOdds": "'||(random()*203707333464004703)::bigint||'", "awayTeam": "Utah Saints", "drawOdds": "0", "homeOdds": "'||(random()*796292666535995296)::bigint||'", "homeTeam": "Burger King Sodas", "isPaused": false}
        ]}}'::text as CONTENT
$f$ language sql;

CREATE FUNCTION


--https://stackoverflow.com/q/78125638/5298879
/*
I am using the http extension in Postgresql, making a request, and getting a list of jsonb [{keys, values}] in a single column 'details' as a response.

It seems like there should be an easy way to create a new table from the keys and values.

Here is the request:
*/
--jsonb_to_recordset()
CREATE TABLE my_table AS
WITH sport_markets_api AS (
    SELECT ((CONTENT::jsonb ->> 'data')::jsonb ->> 'sportMarkets')::jsonb AS details 
    FROM http_post(
        'https://api.thegraph.com/subgraphs/name/',
        '{"query": "{sportMarkets(first:2,skip:0,orderBy:timestamp,orderDirection:desc){id,timestamp,address,gameId,maturityDate,tags,isOpen,isResolved,isCanceled,finalResult,homeTeam,awayTeam }}"}'::text,
        'application/json'))
SELECT r.*
FROM sport_markets_api,
     jsonb_to_recordset(details) r(id text, 
                                   tags text[], 
                                   total int,
                                   "betType" text,
                                   "awayOdds" bigint,
                                   "awayTeam" text,
                                   "drawOdds" bigint,
                                   "homeOdds" bigint,
                                   "homeTeam" text,
                                   "isPaused" boolean);

SELECT * FROM my_table;

SELECT 3




id
tags
total
betType
awayOdds
awayTeam
drawOdds
homeOdds
homeTeam
isPaused


9f4a2
{9006}
null
10001
203707333464004703
Ottawa Senators
0
796292666535995296
Los Angeles Kings
f

9f4a3
{9007,9005}
null
10002
53245307405670272
Nebraska Governors
0
456747974542032576
Los Santos Bloods
t

9f4a2
{9006}
5
10003
74020381708234016
Utah Saints
0
642260855437070976
Burger King Sodas
f



SELECT 3


--jsonb_populate_recordset()
CREATE TYPE my_type AS (           id text, 
                                   tags text[], 
                                   total int,
                                   "betType" text,
                                   "awayOdds" bigint,
                                   "awayTeam" text,
                                   "drawOdds" bigint,
                                   "homeOdds" bigint,
                                   "homeTeam" text,
                                   "isPaused" boolean);

CREATE TABLE my_table2 AS
WITH sport_markets_api AS (
    SELECT ((CONTENT::jsonb ->> 'data')::jsonb ->> 'sportMarkets')::jsonb AS details 
    FROM http_post(
        'https://api.thegraph.com/subgraphs/name/',
        '{"query": "{sportMarkets(first:2,skip:0,orderBy:timestamp,orderDirection:desc){id,timestamp,address,gameId,maturityDate,tags,isOpen,isResolved,isCanceled,finalResult,homeTeam,awayTeam }}"}'::text,
        'application/json'))
SELECT r.*
FROM sport_markets_api,
     jsonb_populate_recordset(null::my_type,details) as r;

SELECT * FROM my_table2;

CREATE TYPE



SELECT 3




id
tags
total
betType
awayOdds
awayTeam
drawOdds
homeOdds
homeTeam
isPaused


9f4a2
{9006}
null
10001
203707333464004703
Ottawa Senators
0
796292666535995296
Los Angeles Kings
f

9f4a3
{9007,9005}
null
10002
70621319368610224
Nebraska Governors
0
152409086209130112
Los Santos Bloods
t

9f4a2
{9006}
5
10003
18017616075956596
Utah Saints
0
242123014077450720
Burger King Sodas
f



SELECT 3


CREATE EXTENSION tablefunc;
DO $f$
BEGIN
CREATE TEMP TABLE sport_markets_api ON COMMIT DROP AS
SELECT ((CONTENT::jsonb ->> 'data')::jsonb ->> 'sportMarkets')::jsonb AS details 
FROM http_post( 'https://api.thegraph.com/subgraphs/name/'
               ,'{"query": "{sportMarkets(first:2,skip:0,orderBy:timestamp,orderDirection:desc){id,timestamp,address,gameId,maturityDate,tags,isOpen,isResolved,isCanceled,finalResult,homeTeam,awayTeam }}"}'::text
               ,'application/json');
EXECUTE FORMAT(
  $dyn_sql$
    CREATE TABLE map_json_dynamically AS
    SELECT *
    FROM crosstab(
        $c$SELECT element_num, field_name, val
        FROM sport_markets_api,
             jsonb_array_elements(details)
             with ordinality e(element_,element_num),
             jsonb_each_text(element_)x(field_name, val)
        $c$
      , 'SELECT jsonb_object_keys(example)field_name
         FROM (SELECT details[0] AS example FROM sport_markets_api LIMIT 1)_'
    ) AS ct (element_num int, %1$s) 
  $dyn_sql$
  , (SELECT string_agg(format('%I text',field_name),', ')
     FROM (SELECT jsonb_object_keys(example)field_name
           FROM (SELECT details[0] AS example FROM sport_markets_api LIMIT 1)_)_));
END $f$;

SELECT * FROM map_json_dynamically;

CREATE EXTENSION



DO




element_num
id
tags
total
betType
awayOdds
awayTeam
drawOdds
homeOdds
homeTeam
isPaused


1
9f4a2
["9006"]
null
10001
203707333464004703
Ottawa Senators
0
796292666535995296
Los Angeles Kings
false

2
9f4a3
["9007", "9005"]
null
10002
111916761610270288
Nebraska Governors
0
231034811655113152
Los Santos Bloods
true

3
9f4a2
["9006"]
5
10003
34666840126829848
Utah Saints
0
156478668264038336
Burger King Sodas
false



SELECT 3


--here's what crosstab sees:
CREATE TEMP TABLE sport_markets_api ON COMMIT DROP AS
SELECT ((CONTENT::jsonb ->> 'data')::jsonb ->> 'sportMarkets')::jsonb AS details 
FROM http_post( 'https://api.thegraph.com/subgraphs/name/'
               ,'{"query": "{sportMarkets(first:2,skip:0,orderBy:timestamp,orderDirection:desc){id,timestamp,address,gameId,maturityDate,tags,isOpen,isResolved,isCanceled,finalResult,homeTeam,awayTeam }}"}'::text
               ,'application/json');

SELECT element_num, field_name, val
FROM sport_markets_api,
     jsonb_array_elements(details)with ordinality e(element_,element_num),
     jsonb_each_text(element_) as _(field_name, val)

SELECT 1




element_num
field_name
val


1
id
9f4a2

1
tags
["9006"]

1
total
null

1
betType
10001

1
awayOdds
203707333464004703

1
awayTeam
Ottawa Senators

1
drawOdds
0

1
homeOdds
796292666535995296

1
homeTeam
Los Angeles Kings

1
isPaused
false

2
id
9f4a3

2
tags
["9007", "9005"]

2
total
null

2
betType
10002

2
awayOdds
44359007255010824

2
awayTeam
Nebraska Governors

2
drawOdds
0

2
homeOdds
27913902287158108

2
homeTeam
Los Santos Bloods

2
isPaused
true

3
id
9f4a2

3
tags
["9006"]

3
total
5

3
betType
10003

3
awayOdds
53952502569519088

3
awayTeam
Utah Saints

3
drawOdds
0

3
homeOdds
383359404914971904

3
homeTeam
Burger King Sodas

3
isPaused
false



SELECT 30

id	tags	total	betType	awayOdds	awayTeam	homeOdds	homeTeam	isPaused
9f4a2	{9006}	null	10001	203707333464004703	Ottawa Senators	796292666535995296	Los Angeles Kings	f
9f4a3	{9007,9005}	null	10002	53245307405670272	Nebraska Governors	456747974542032576	Los Santos Bloods	t
9f4a2	{9006}	5	10003	74020381708234016	Utah Saints	642260855437070976	Burger King Sodas	f

element_num	id	tags	total	betType	awayOdds	awayTeam	homeOdds	homeTeam	isPaused
1	9f4a2	["9006"]	null	10001	203707333464004703	Ottawa Senators	796292666535995296	Los Angeles Kings	false
2	9f4a3	["9007", "9005"]	null	10002	111916761610270288	Nebraska Governors	231034811655113152	Los Santos Bloods	true
3	9f4a2	["9006"]	5	10003	34666840126829848	Utah Saints	156478668264038336	Burger King Sodas	false

element_num	field_name	val
1	id	9f4a2
1	tags	["9006"]
1	total	null
1	betType	10001
1	awayOdds	203707333464004703
1	awayTeam	Ottawa Senators
1	drawOdds	0
1	homeOdds	796292666535995296
1	homeTeam	Los Angeles Kings
1	isPaused	false
2	id	9f4a3
2	tags	["9007", "9005"]
2	total	null
2	betType	10002
2	awayOdds	44359007255010824
2	awayTeam	Nebraska Governors
2	drawOdds	0
2	homeOdds	27913902287158108
2	homeTeam	Los Santos Bloods
2	isPaused	true
3	id	9f4a2
3	tags	["9006"]
3	total	5
3	betType	10003
3	awayOdds	53952502569519088
3	awayTeam	Utah Saints
3	drawOdds	0
3	homeOdds	383359404914971904
3	homeTeam	Burger King Sodas
3	isPaused	false