You can translate the question and the replies:

How to flatten JSON Source on web returning different number of registers based on parameters

Apologies if my query has been answered elsewhere, but I haven't been able to find a relevant article / direction so far. I have an API source that is drawn into VDP as a JSON format. The API accepts dates as input parameters. Based on the dates entered, the number of JSON registers returned are different. For example, start_date='2020-01-01' and end-date='2020-02-28' returns 125 registers. That is for the above date range, my base view has the following structure: date_from text date_to text + id_0 hours int + id_1 hours int + ... ... + id_125 I'd need flatten each of those registers, but do no want to repeat these manually as I do not know how many registers (and sometimes in thousands) I will have before the dates are input. Each of the registers have the same field for example: hours and I should be able to project the "hours" from all the registers into a single view. Is this possible with Denodo? Any direction is appreciated.
user
10-05-2020 22:18:56 -0400

2 Answers

Hi, I see from your sample JSON file the column names (hours,int) are the same in all of the registers(id_0,id_1..id_n). Flattening a register will add new columns to the derived view (selection view). When flattening multiple registers with the same column names will lead to duplicate columns in the view definition. In this kind of scenario I try to make sure the data source returns registers as rows instead of columns. If you are able to make that change then you can follow the steps as mentioned in the community Q&A [How to Flatten Array & Register](https://community.denodo.com/answers/question/details?questionId=9060g000000TRA5AAO&title=How+to+handle+the+Register+and+Array+when+using+XML+datasource%3F+Please+explain+me+if+my+xml+source+has+more+hierarchicals) to flatten the registers. If you need any further help on multiple registers, and if you are an user with valid support access then you can raise a support case in the Denodo Support Site so they can help you out with multiple registers. Hope this helps!
Denodo Team
12-05-2020 08:51:36 -0400
Hi, Many thanks for responding & the inputs provided. On your suggestion to set up the data source to return the reigsters as rows - the data source is not in my control, so I'm wondering if this can be a setting that is on the data source's end. I'll log a support case to see what options I have to achieve this. Thanks again.
user
12-05-2020 16:05:41 -0400
You must sign in to add an answer. If you do not have an account, you can register here