如何在Supabase中将API返回的JSON数据转为表格格式？

我正在用Supabase数据库配合FlutterFlow开发博彩应用，打算通过赛事结果API把每场比赛的结果插入数据表。尝试直接用Supabase发起请求并将返回的JSON转成表格格式时遇到问题：能生成每行记录，但所有字段信息都挤在单个列里，没法提取成多列。

现有SQL代码 #

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' -- 替换为你的实际API密钥
            ),
            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;

当前执行结果 #

仅生成名为rodada的单列，每条记录是包含所有赛事信息的完整JSON字符串，例如第一条记录：

"{"fixture":{"id":1146728,"referee":"João","timezone":"UTC","date":"2024-01-20T21:00:00+00:00","timestamp":1705784400,"periods":{"first":1705784400,"second":1705788000},"venue":{"id":219,"name":"Estádio Santa Cruz","city":"Ribeirão Preto, São 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}}}"

预期结果 #

生成多列表格，包含拆分后的各个字段，示例结构：

解决方案 #

问题出在使用json_array_elements_text，它会把JSON数组的每个元素转成字符串，而非保留JSON对象。改用json_array_elements获取JSON对象，再用->>运算符提取嵌套字段：

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' -- 替换为你的实际API密钥
            ),
            http_header (
              'X-RapidAPI-Host',
              'api-football-v1.p.rapidapi.com'
            )
          ],
          null,
          null
        )::http_request
      )
  ),
  fixtures as (
    select json_array_elements(rodadas) as fixture_json
    from rodadas_json
  )
select
  -- 提取fixture层级的字段
  (fixture_json -> 'fixture' ->> 'id')::int as 赛事ID,
  fixture_json -> 'fixture' ->> 'referee' as 裁判,
  fixture_json -> 'fixture' ->> 'timezone' as 时区,
  fixture_json -> 'fixture' ->> 'date' as 比赛日期,
  -- 提取goals层级的字段
  (fixture_json -> 'goals' ->> 'home')::int as 主队进球,
  (fixture_json -> 'goals' ->> 'away')::int as 客队进球,
  -- 提取teams层级的字段
  fixture_json -> 'teams' -> 'home' ->> 'name' as 主队名称,
  fixture_json -> 'teams' -> 'away' ->> 'name' as 客队名称,
  -- 可根据需求继续添加其他字段
  fixture_json -> 'league' ->> 'name' as 联赛名称
from fixtures;

关键说明 #

• json_array_elements：将JSON数组拆分为独立的JSON对象行，而非字符串
• ->>：提取JSON字段的字符串值，若需要数值类型，用::int等进行类型转换
• 可根据API返回的JSON结构，继续扩展提取其他嵌套字段

内容的提问来源于stack exchange，提问作者Caio Cezar