jsonpostgresqlsupabasesupabase-database

How to Structure Json data Returned by API in Supabase?


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


Solution

  • 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