I’ve done quite a lot of work with REST API data sources using PowerBI. I’ve also done similar work in Metabase by loading the data into SQL Server, then creating views for Metabase.
As attractive as the notion of a REST API data source is, it only works for non-trivial datasources if there is some form of transformation layer. PowerBI has this as part of it’s data load, Metabase doesn’t. If you want to use REST API with Metabase (a good idea), the only practical way is to use some form of ETL tool.
I use CURL to extract the json data, then load into MS SQL tables using stored procedures. It’s a bit of a pain to initially setup, but can then easily be done on an automated schedule.