You can translate the question and the replies:

JSON datasource - HTTP POST - ARRAY request and response FLATTEN

HI, i've set a JSON source for the API https://www.openfigi.com/api, with a parameter in POST body. ``` CREATE DATASOURCE JSON ds_openfigi FOLDER = '/01-connectivity/01-data-sources/openfigi' ROUTE HTTP 'http.CommonsHttpClientConnection,120000' POST 'https://api.openfigi.com/v3/mapping' POSTBODY '@parameter_openfigi' MIME 'application/json' HEADERS ( 'X-OPENFIGI-APIKEY'='********************************', 'Content-Type'='application/json' ) ``` The source takes as input a JSON array, and returns a JSON array. My goal is to obtain a flat view with corresponding request and response data. This could be easily done by passing only one element of the array and join with the response, but the API has a limits on the amount of requests per second, so i have to send more jobs in a single request. I can succesfully query the base view with the string parameter: ``` SELECT * FROM bv_openfigi WHERE parameter_openfigi = '[ { "idType": "ID_ISIN", "idValue": "AAA" }, { "idType": "ID_WERTPAPIER", "idValue": "851399", "exchCode": "US" }, { "idType": "ID_BB_UNIQUE", "idValue": "EQ0010080100001000", "currency": "USD" }, { "idType": "ID_SEDOL", "idValue": "2005973", "micCode": "EDGX", "currency": "USD" }, { "idType":"PIPPO", "idValue":"TSLA 10 C100", "securityType2":"Option", "expiration":["2018-10-01", "2018-12-01"]}, { "idType":"BASE_TICKER", "idValue":"NFLX 9 P330", "marketSecDes":"Equity", "securityType2":"Option", "strike":[330,null], "expiration":["2018-07-01",null]}, { "idType":"BASE_TICKER", "idValue":"FG", "marketSecDes":"Mtge", "securityType2":"Pool", "maturity":["2019-09-01", "2020-06-01"]}, { "idType":"BASE_TICKER", "idValue":"IBM", "marketSecDes":"Corp", "securityType2":"Corp", "maturity":["2026-11-01",null]}, { "idType":"BASE_TICKER", "idValue":"2251Q", "securityType2":"Common Stock", "includeUnlistedEquities": true} ]' ``` and return a single record within and array with the same dimension of the request one (one response for each request job). I've also created a JSON datasource from variable, to convert the request string to an array, and join with the response. So now i have a view of one record, with two arrays of the same dimension to be flatten. There is no key to join response and request element, only the array position. If i flat one column at time, the result is a sort of "full join" of the two arrays. How cai it be done? Thank you
user
16-03-2022 12:09:54 -0400

1 Answer

Hi, Denodo Platform does not allow for the appending of columns. As such, it requires that the two views that you are trying to join, response and request elements have a common key to join on else as described, flattening one column at a time will result in a “Full Join” like operation between the two arrays. If you still need help and you are a valid support user, you can raise a support case at [Denodo Support Site](https://support.denodo.com/) and our Support Team will assist you. Hope this helps!
Denodo Team
21-03-2022 04:15:04 -0400
You must sign in to add an answer. If you do not have an account, you can register here