Dynamically map JSON to a SQL table, adding and naming columns based on JSON keys

Question

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:

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'))

I tried:

SELECT col.*
FROM sport_markets_api,
     jsonb_to_recordset(details) AS col(id TEXT,tags TEXT,total INT)

and this works...however I am looking for an easier way to create columns from jsonb_object_keys(). It can be rather tedious when calling different APIs with > 10 columns.

Example response:

{"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}
     ]
  }
}

Answer 1

If you want to avoid specifying field types and names in your records when calling jsonb_to_recordset(), before you pass that to create table as, then there's also jsonb_populate_recordset() in case you have that structure already defined somewhere, as table or a type: demo

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;

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	7248793422157497	Nebraska Governors	0	367914422982970560	Los Santos Bloods	t
9f4a2	{9006}	5	10003	80554882934156496	Utah Saints	0	419810059157643712	Burger King Sodas	f

In case you don't already have a table or a type to map those to, you can avoid mapping out each json individually, by hand: list pairs of field names and values with jsonb_each_text(), then pivot that with crosstab, kind of like here:

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(
        $crst$
        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)
        $crst$
      , '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;

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