Pages

07 March, 2024

How to Create a New Table from JSONB_ARRAY_ELEMENTS and JSONB_OBJECT_KEYS in Postgresql?

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
jsonb_array_elements(details) ->> jsonb_object_keys(jsonb_array_elements(details))
FROM sport_markets_api




and was expecting a table with columns based on the keys.

No comments:

Post a Comment

Thanks