I am developing a betting app in FlutterFlow using a Supabase database. My idea is to insert the results of each game into the table using a game results API. I am working to make these requests directly through Supabase, which would transform the incoming JSON request into a tabular format.
Problem:
I am unable to extract the JSON data from the request in tabular format. The code I’ve built below is creating rows for each record, but the information from the “Columns” fields is all within a single column for each record.
I am attempting with the following code:
with
rodadas_json as (
select
content::json -> 'response' as rodadas
from
http (
(
'GET',
'https://api-football-v1.p.rapidapi.com/v3/fixtures?league=475&season=2024',
array[
http_header (
'X-RapidAPI-Key',
'API_KEY'
),
http_header (
'X-RapidAPI-Host',
'api-football-v1.p.rapidapi.com'
)
],
null,
null
)::http_request
)
)
select
json_array_elements_text(rodadas_json.rodadas) as rodada
from
rodadas_json;
Result Obtained: Only one column called “Rodada” with multiple records, and each record contains all the game information. For example, Record 1.
Example result in line 1:
"{"fixture":{"id":1146728,"referee":"Jo\u00e3o","timezone":"UTC","date":"2024-01-20T21:00:00+00:00","timestamp":1705784400,"periods":{"first":1705784400,"second":1705788000},"venue":{"id":219,"name":"Est\u00e1dio Santa Cruz","city":"Ribeir\u00e3o Preto, S\u00e3o Paulo"},"status":{"long":"Match Finished","short":"FT","elapsed":90}},"league":{"id":475,"name":"Paulista - A1","country":"Brazil","logo":"https://media.api-sports.io/football/leagues/475.png","flag":"https://media.api-sports.io/flags/br.svg","season":2024,"round":"Regular Season - 1"},"teams":{"home":{"id":2618,"name":"Botafogo SP","logo":"https://media.api-sports.io/football/teams/2618.png","winner":false},"away":{"id":128,"name":"Santos","logo":"https://media.api-sports.io/football/teams/128.png","winner":true}},"goals":{"home":0,"away":1},"score":{"halftime":{"home":0,"away":0},"fulltime":{"home":0,"away":1},"extratime":{"home":null,"away":null},"penalty":{"home":null,"away":null}}}"
Expected:
Table:
Id | referee | timezone ....
1 | João | UTC
I solved this problem and discovered that to access nested fields of a JSON object in Supabase, you can use the ->
and ->>
operators.
The ->
operator is used to extract a field from a JSON object, returning a JSON result. For example, if you have a JSON object {"a": {"b": 1}}
and you want to access the "b" field, you can use jsonColumn->'a'->'b'
.
The ->>
operator is also used to extract a field, but it returns the result as text. So, if you use jsonColumn->>'a'->>'b'
, the result would be the string '1'.
The Correct code is:
with
rodadas_json as (
select
content::json -> 'response' as rodadas
from
http (
(
'GET',
'https://api-football-v1.p.rapidapi.com/v3/fixtures?league=475&season=2024',
array[
http_header (
'X-RapidAPI-Key',
'API_Key'
),
http_header (
'X-RapidAPI-Host',
'api-football-v1.p.rapidapi.com'
)
],
null,
null
)::http_request
)
)
select
js -> 'fixture' ->> 'id' AS fixture_id,
js -> 'fixture' ->> 'referee' AS referee_name,
js -> 'fixture' ->> 'timezone' AS fixture_timezone,
js -> 'fixture' ->> 'date' AS fixture_date,
js -> 'fixture' ->> 'timestamp' AS fixture_timestamp,
js -> 'fixture' -> 'periods' ->> 'first' AS first_period_timestamp,
js -> 'fixture' -> 'periods' ->> 'second' AS second_period_timestamp,
js -> 'fixture'-> 'venue' ->> 'name' AS venue_name,
js -> 'fixture'-> 'venue' ->> 'city' AS venue_city,
js -> 'fixture'-> 'status' ->> 'long' AS fixture_status,
js -> 'league' ->> 'name' AS league_name,
js -> 'league' ->> 'country' AS league_country,
js -> 'league' ->> 'logo' AS league_logo,
js -> 'league' ->> 'flag' AS league_flag,
js -> 'league' ->> 'season' AS league_season,
js -> 'league' ->> 'round' AS league_round,
js -> 'teams' -> 'home' ->> 'name' AS home_team_name,
js -> 'teams' -> 'home' ->> 'logo' AS home_team_logo,
js -> 'teams' -> 'home' ->> 'winner' AS home_team_winner,
js -> 'teams' -> 'away' ->> 'name' AS away_team_name,
js -> 'teams' -> 'away' ->> 'logo' AS away_team_logo,
js -> 'teams' -> 'away' ->> 'winner' AS away_team_winner,
js -> 'goals' ->> 'home' AS home_goals,
js -> 'goals' ->> 'away' AS away_goals,
js -> 'score' -> 'halftime' ->> 'home' AS halftime_home_goals,
js -> 'score' -> 'halftime' ->> 'away' AS halftime_away_goals,
js -> 'score' -> 'fulltime' ->> 'home' AS fulltime_home_goals,
js -> 'score' -> 'fulltime' ->> 'away' AS fulltime_away_goals,
js -> 'score' -> 'extratime' ->> 'home' AS extratime_home_goals,
js -> 'score' -> 'extratime' ->> 'away' AS extratime_away_goals,
js -> 'score' -> 'penalty' ->> 'home' AS penalty_home_goals,
js -> 'score' -> 'penalty' ->> 'away' AS penalty_away_goals
from
(
select json_array_elements(rodadas_json.rodadas) as js
from rodadas_json
) q1