Metabase API, troubles using api/card with parameters

iAmNikitaValty · November 23, 2017, 3:55pm

Hello,
I’m trying to retrieve from Metabase only the cards of a specific database. I’m using the REST API and i found that i can use the f parameter to specify database as filter.
The problem i have is that, this parameter requires an id and i don’t know how to pass it.
I’ve googled the pb but found nothing interesting
I’ve tried passing it in the parameter array as such but, it doesn’t work.
$ch = curl_init();
$url = MY_METABASE_BASE_URL .‘api/card’;
$parameters = [‘f’=>‘database’, ‘id’=>‘2’ ];
curl_setopt($ch, CURLOPT_URL, $url . “?” . http_build_query($parameters));
…
curl_setopt(
$ch,
CURLOPT_POSTFIELDS,
json_encode($parameters)
);
…

I have this error message by the way:
{“errors”:{“id”:“id is required parameter when filter mode is ‘database’”}}

Please can somebody help me find out how to pass this dammit parameter ? I’m getting mad for searching.

alantygel · December 11, 2017, 6:54pm

Hi, I’m having the same problem.

$parameters = array('parameters' => array ('municipio' => '3122306') );

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "http://.../api/card/" . $card_id . "/query/" . $export_format);
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type:application/json','Accept: application/json', 'X-Metabase-Session: ' . $_COOKIE['x_metabase_session']));				
curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($parameters));
curl_setopt($ch, CURLOPT_POST, 1);
$res = curl_exec($ch);
curl_close($ch);
$res = json_decode($res);

The answer is:

'Municipio' is a required param

Which is the correct way of sending parameters to questions via API?

jornh · December 11, 2017, 7:35pm

See https://github.com/metabase/metabase/issues/6462 … info should have been fixed in latest version

alantygel · December 12, 2017, 12:05pm

Hi Jornh,

Thanks for the answer. Unfortunately, in my case the problem is not really wrong name of parameters. My question uses template tags, in this case its named municipio.

This is my question:

SELECT `Censo2010_Basico`.`Nome_do_municipio` AS `Nome_do_municipio`
FROM `Censo2010_Basico`
WHERE `Censo2010_Basico`.`Cod_municipio` = {{municipio}}

I’m already at the newest version 0.27.1

I suspect I’m not passing the parameters in the right way (see first post). Here you can see the console response:

12-12 12:06:19 INFO api.card :: Question's average execution duration is 281 ms; using 'magic' TTL of 3 seconds 💾
12-12 12:06:19 WARN metabase.query-processor :: {:status :failed,
 :class java.lang.Exception,
 :error "'Municipio' is a required param.",
 :stacktrace
 ["query_processor.middleware.parameters.sql$fn__29065$default_value_for_tag__29070$fn__29074.invoke(sql.clj:180)"
  "query_processor.middleware.parameters.sql$fn__29065$default_value_for_tag__29070.invoke(sql.clj:173)"
  "query_processor.middleware.parameters.sql$fn__29179$value_for_tag__29184$fn__29185.invoke(sql.clj:231)"
  "query_processor.middleware.parameters.sql$fn__29179$value_for_tag__29184.invoke(sql.clj:225)"
  "query_processor.middleware.parameters.sql$fn__29205$query__GT_params_map__29210$fn__29216$iter__29219__29223$fn__29224.invoke(sql.clj:246)"
  "query_processor.middleware.parameters.sql$fn__29205$query__GT_params_map__29210$fn__29216.invoke(sql.clj:245)"
  "query_processor.middleware.parameters.sql$fn__29205$query__GT_params_map__29210.invoke(sql.clj:235)"
  "query_processor.middleware.parameters.sql$expand.invokeStatic(sql.clj:487)"
  "query_processor.middleware.parameters.sql$expand.invoke(sql.clj:482)"
  "query_processor.middleware.parameters$expand_parameters_STAR_.invokeStatic(parameters.clj:21)"
  "query_processor.middleware.parameters$expand_parameters_STAR_.invoke(parameters.clj:14)"
  "query_processor.middleware.parameters$expand_parameters.invokeStatic(parameters.clj:45)"
  "query_processor.middleware.parameters$expand_parameters.invoke(parameters.clj:42)"
  "query_processor.middleware.parameters$substitute_parameters_STAR_.invokeStatic(parameters.clj:51)"
  "query_processor.middleware.parameters$substitute_parameters_STAR_.invoke(parameters.clj:48)"
  "query_processor.middleware.driver_specific$process_query_in_context$fn__28405.invoke(driver_specific.clj:12)"
  "query_processor.middleware.resolve_driver$resolve_driver$fn__29832.invoke(resolve_driver.clj:14)"
  "query_processor.middleware.cache$run_query_and_save_results_if_successful_BANG_.invokeStatic(cache.clj:113)"
  "query_processor.middleware.cache$run_query_and_save_results_if_successful_BANG_.invoke(cache.clj:111)"
  "query_processor.middleware.cache$run_query_with_cache.invokeStatic(cache.clj:124)"
  "query_processor.middleware.cache$run_query_with_cache.invoke(cache.clj:121)"
  "query_processor.middleware.cache$maybe_return_cached_results$fn__27156.invoke(cache.clj:147)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__28327.invoke(catch_exceptions.clj:58)"
  "query_processor$process_query.invokeStatic(query_processor.clj:126)"
  "query_processor$process_query.invoke(query_processor.clj:122)"
  "query_processor$run_and_save_query_BANG_.invokeStatic(query_processor.clj:234)"
  "query_processor$run_and_save_query_BANG_.invoke(query_processor.clj:229)"
  "query_processor$fn__29866$process_query_and_save_execution_BANG___29871$fn__29872.invoke(query_processor.clj:272)"
  "query_processor$fn__29866$process_query_and_save_execution_BANG___29871.invoke(query_processor.clj:258)"
  "api.card$run_query_for_card.invokeStatic(card.clj:608)"
  "api.card$run_query_for_card.doInvoke(card.clj:594)"
  "api.card$fn__32822$fn__32823$fn__32824.invoke(card.clj:625)"
  "api.card$fn__32822$fn__32823.invoke(card.clj:623)"
  "api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:248)"
  "api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:243)"
  "api.card$fn__32822.invokeStatic(card.clj:617)"
  "api.card$fn__32822.invoke(card.clj:617)"
  "middleware$enforce_authentication$fn__31785.invoke(middleware.clj:122)"
  "api.routes$fn__44986.invokeStatic(routes.clj:61)"
  "api.routes$fn__44986.invoke(routes.clj:61)"
  "routes$fn__45697$fn__45698.doInvoke(routes.clj:101)"
  "routes$fn__45697.invokeStatic(routes.clj:97)"
  "routes$fn__45697.invoke(routes.clj:97)"
  "middleware$log_api_call$fn__31884$fn__31886.invoke(middleware.clj:330)"
  "middleware$log_api_call$fn__31884.invoke(middleware.clj:329)"
  "middleware$add_security_headers$fn__31834.invoke(middleware.clj:245)"
  "middleware$bind_current_user$fn__31789.invoke(middleware.clj:142)"
  "middleware$maybe_set_site_url$fn__31838.invoke(middleware.clj:268)"],
 :query
 {:type "native",
  :native
  {:query "SELECT `Censo2010_Basico`.`Nome_do_municipio` AS `Nome_do_municipio`\nFROM `Censo2010_Basico`\nWHERE `Censo2010_Basico`.`Cod_municipio` = {{municipio}}\nLIMIT 1",
   :template_tags {:municipio {:id "dd07d8f7-d6fd-8715-cfe3-2200f50e8634", :name "municipio", :display_name "Municipio", :type "text", :required true}}},
  :constraints nil,
  :parameters nil,
  :cache_ttl 3,
  :info
  {:executed-by 1,
   :context :json-download,
   :card-id 13,
   :dashboard-id nil,
   :query-hash [-38, 116, -34, 9, -44, 22, -120, -98, 26, -86, -57, -68, 27, -120, 41, -87, 48, -71, 126, -52, 69, -9, 32, 25, 41, 9, 86, 76, 16, -105, -41, -113],
   :query-type "native"}},
 :expanded-query nil}

12-12 12:06:20 WARN metabase.query-processor :: Query failure: 'Municipio' is a required param.
["query_processor$assert_query_status_successful.invokeStatic(query_processor.clj:203)"
 "query_processor$assert_query_status_successful.invoke(query_processor.clj:196)"
 "query_processor$run_and_save_query_BANG_.invokeStatic(query_processor.clj:235)"
 "query_processor$run_and_save_query_BANG_.invoke(query_processor.clj:229)"
 "query_processor$fn__29866$process_query_and_save_execution_BANG___29871$fn__29872.invoke(query_processor.clj:272)"
 "query_processor$fn__29866$process_query_and_save_execution_BANG___29871.invoke(query_processor.clj:258)"
 "api.card$run_query_for_card.invokeStatic(card.clj:608)"
 "api.card$run_query_for_card.doInvoke(card.clj:594)"
 "api.card$fn__32822$fn__32823$fn__32824.invoke(card.clj:625)"
 "api.card$fn__32822$fn__32823.invoke(card.clj:623)"
 "api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:248)"
 "api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:243)"
 "api.card$fn__32822.invokeStatic(card.clj:617)"
 "api.card$fn__32822.invoke(card.clj:617)"
 "middleware$enforce_authentication$fn__31785.invoke(middleware.clj:122)"
 "api.routes$fn__44986.invokeStatic(routes.clj:61)"
 "api.routes$fn__44986.invoke(routes.clj:61)"
 "routes$fn__45697$fn__45698.doInvoke(routes.clj:101)"
 "routes$fn__45697.invokeStatic(routes.clj:97)"
 "routes$fn__45697.invoke(routes.clj:97)"
 "middleware$log_api_call$fn__31884$fn__31886.invoke(middleware.clj:330)"
 "middleware$log_api_call$fn__31884.invoke(middleware.clj:329)"
 "middleware$add_security_headers$fn__31834.invoke(middleware.clj:245)"
 "middleware$bind_current_user$fn__31789.invoke(middleware.clj:142)"
 "middleware$maybe_set_site_url$fn__31838.invoke(middleware.clj:268)"]

12-12 12:06:20 ERROR metabase.middleware :: POST /api/card/13/query/json 500 (228 ms) (5 DB calls)
"'Municipio' is a required param."

best,
alan

jornh · December 13, 2017, 6:37pm

Ah sorry. To be honest I stopped reading to soon after:

I have the same question

So thanks for clarifying. I now understand that you are calling this API endpoint metabase/docs/api-documentation.md at master · metabase/metabase · GitHub and the context of the parameters to the same that is giving you a hard time.

I have no personal experience using that part of the API. So only a few observations that may or may not be useful.

The error response you get has Municipio with a uppercase M. Your POST has it in lowercase.
The API documentation states parameters should be a string. In your PHP you wrap it in a call to array().

Update: oh wait, here’s test code suggesting (if I understand it right) that parameters should be given as a URL parameter —> https://github.com/metabase/metabase/blob/release-0.27.2/test/metabase/api/card_test.clj#L745

alantygel · December 13, 2017, 7:26pm

Jornh,

Many thanks for your attention! You made see the point right above at the code, where the expected parameters format is shown.

Parameters should go at the URL , encoded in JSON, in the following format:

[{"type":"number", "target":["variable",["template-tag","municipio"]],"value":"4313409"}]

I just wanted to now if this is somewhere at the docs - otherwise I could try to add it.

Anyway, I’ll open a new question&answer since the it is quit different from this topic.

best, thanks!

ClementS · May 28, 2018, 6:33am

Hello,
I know it has been a while since this post but maybe someone know.
I need help to understand how to use the python API on a question with a filter. I have a question with a filter, a number, on which I want to have the result on python. I connect to my metabase account, and get the query result with : metabase.post("/card/300/query"). But when I want to apply the filter "Number", I am a little lost.
I tried to follow this :

& metabase.post("/card/300/query/json", json = payload) changing value and name but it did not work.
Does anybody knows how to help me ?
Thanks,
CS.

Welley · July 30, 2020, 1:17pm

Thanks to your explanation I got!

On Metabase interface I open my browser console and on the Network tab I got this parameters:
"parameters": [{"type":"category","target":["variable",["template-tag","myFirstVariable"]],"value":"1"}]

Then I put the url-enconded parameters at the url endpoint like this:
/api/card/1/query/json?parameters=%5B%7B%22type%22%3A%22category%22%2C%22target%22%3A%5B%22variable%22%2C%5B%22template-tag%22%2C%22myFirstVariable%22%5D%5D%2C%22value%22%3A%221%22%7D%5D

And it’s working now!

Thanks!

graham.david · December 8, 2021, 3:45pm

This is a life saver, would be great if this was in POST /api/card/:card-id/query/:export-format documentation, or maybe the examples page