You can translate the question and the replies:

JSON Data Source with optional Interpolation Variables

I want to make a JSON Data Source from this URL: https://data.cdc.gov/resource/52kb-ccu2.json This URL supports filtering by adding query strings to the end of the URL. These simple query string filters are optional. Here are 2 such examples? https://data.cdc.gov/resource/52kb-ccu2.json?repweekcode=1027 https://data.cdc.gov/resource/52kb-ccu2.json?testtype=PCR I understand how to make Interpolation Variables such as the first one: https://data.cdc.gov/resource/52kb-ccu2.json?repweekcode=@{weekcode} Now the user could query this JSON Data Source base view with something like: Select * from bv_somebaseview where weekcode = "7" When there are Interpolation Variables, it is my understanding that these MUST BE INCLUDED in the query otherwise the query will return an error. If the user just try to execute: Select * from bv_somebaseview, then the query would throw an exception that it was missing the requiired variable weekcode. Is there a way to define optional Interpolation Variables? Thanks, Dan

3 Answers

Hi, In Denodo 8.0 if you define Interpolation Variables on a JSON data source, the normal behaviour is that they always need to have a value in order to work. However, there is an option you can use if you want to make them optional. With the ExecuteIfIsNotNull function, you can define in the URI of your datasource the variables that you want to apply only when a value is specified. You can see the syntaxis of the function here; even though the doc is about JDBC, it also works for JSON datasources: https://community.denodo.com/docs/html/browse/latest/en/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#using-the-whereexpression-variable For example, in your case you can define a datasource with an optional parameter like repweekcode with this url: https://data.cdc.gov/resource/52kb-ccu2.json?^ExecuteIfIsNotNull("&repweekcode=",@repweekcode,"","") After that, when you create the base view, you need to set the Interpolation Variables as URI parameters. With this configuration, when you execute the query, if you pass the value of the parameter as an empty string, the execution will not add that parameter in the URI that is used when calling the webservice. Hope this helps!
Denodo Team
14-02-2023 06:00:09 -0500
code
Unfortunately, I cannot still get this to work. here is what I did: 1. Create a new JSON data source of type HTTP Client 2. Configured URL to be: https://data.cdc.gov/resource/52kb-ccu2.json?^ExecuteIfIsNotNull("&repweekcode=",@prepweekcode,"","")^ExecuteIfIsNotNull("&testtype=",@ptesttype,"","") 3. Specified prepweekcode and ptestcode as URI parameters when "Testing" connection. 4. Saved the datasource as ds_cdc 5. Clicked on "Create base view". Specified prepweekcode and ptestcode as URI parameters when "Testing" connection. 6. Saved new base view as bv_cdc If I try to query: Select * from bv_cdc, i get the following: "Finished with error: No search methods ready to be run. The following fields are obligatory in the view 'bv_nrevss': ptesttype, prepweekcode" Am I missing a key step? Thanks, Dan
user
14-02-2023 08:27:47 -0500
Hi, With the solution that we provided, you need to explicitly set the parameters in the query, even if you want to retrieve the data without filtering and the values of the parameters are empty strings. The query is going to fail if you don´t set those values to the parameters, with something like this: Select * from bv_cdc where repweekcode='' and testtype=''; If you don´t want to have this obligation of setting always the parameters, there is another alternative to achieve this behaviour, using an inputparameter on the call to your service. On the datasource configuration, add @input_param to the original URL: https://data.cdc.gov/resource/52kb-ccu2.json@input_param After this, create a baseview providing a valid filter to the parameter, such as '?&repweekcode=1027' Finally, create a selection view over this new view, adding a view parameter for each parameter you want to be able to filter by. Is important to set a default value to this view parameter that the field cannot have on the data origin, for repweekcode can be -1, as this field never has a negative value. You need to set the where condition on this selection view, to build the query depending on the values of the parameters. When you pass a value to the parameter, you need to add it to the URL call, otherwise you don´t. For example, if you want to be able to filter by repweekcode, you need to set something like this as your where condition: bv_cdc_new.input_param = CASE WHEN (repweekcode = '-1') THEN '' ELSE ('?repweekcode='||repweekcode) END Hope this helps!
Denodo Team
15-02-2023 04:24:53 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here