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:
how can i find message window in QuartusThere are no messages window below the code blocks I'm using Quartus eda tool for fpga jobs with verilog HDL. For compiling, i need message window to … Read More
What rector rules do you use?I have been playing around with Rector to ensure a more consistent codebase and there are a lot of rules to choose from. Additionally there's even a L… Read More
Unite JavaScript Power with Laravel & Inertiasubmitted by /u/christophrumpel [link] [comments]… Read More
Wrap tag in a new tag and add alt attribute to theHow do I replace with text: with: What is right regex to handle ?… Read More
Scramble 0.11.0 – Laravel API documentation generator update: Laravel Data support, ability to enforce schema types, inference improvementssubmitted by /u/RomaLytvynenko [link] [comments]… Read More
0 comments:
Post a Comment
Thanks