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.
How to Create a New Table from JSONB_ARRAY_ELEMENTS and JSONB_OBJECT_KEYS in Postgresql?
Programing Coderfunda
March 07, 2024
No comments
Related Posts:
CodeIgniter - OverviewCodeIgniter - OverviewCodeIgniter is an application development framework, which can be used to develop websites, using PHP. It is an Open Source fram… Read More
Laravel - RoutingIn Laravel, all requests are mapped with the help of routes. Basic routing routes the request to the associated controllers. This chapter discusses ro… Read More
Laravel - NamespacesNamespaces are used in various programming languages to create a separate group of variable, functions and classes. A program may contain various func… Read More
CodeIgniter TutorialCodeIgniter TutorialCodeIgniter is a powerful PHP framework with a very small footprint, built for developers who need a simple and elegant toolkit to… Read More
Laravel - MiddlewareMiddleware acts as a bridge between a request and a response. It is a type of filtering mechanism. This chapter explains you the middleware mechanism … Read More
0 comments:
Post a Comment
Thanks