Working with JSON hierarchical data

Hi Denodo Team, I am struggling with virtualizing data which is in JSON format (and can only be pulled in JSON format from the API). There are numerous arrays within Registers within arrays. My requirement is that I do not create numerous flattened views but rather one neat view of the data. So far I have two paths: 1. Flattening the first array in a flattened view (which opens numerous registers , some which contain arrays) and flattening the remaining array with group_concat((field).registername.desiredarraycolumn) and group by. The problem is that this group concat function only returns the last row of the array and not all the rows within the desired column within the array. I may be mistaken but this could be due to a bug in the API call. 2. Changing the format into an xml format through denodo. So far I thought using the "from variable" data source in an xml statement would work and somehow linking the data. I have read the Using the From Variable Data route documentation and am none the wiser. I am very new to this field and apologise in advance if my question does not make any sense. I hope you are able to supply me with some direction. I am using version 6.
user
12-04-2019 05:00:46 -0400

1 Answer

Hello , In such cases I would use [flattening of the views](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/creating_derived_views/creating_flatten_views) operation using the VDP AdminTool UI. Although the structure of your view has multiple arrays and registers, you can flatten multiple registers in just one view. When you “flatten” the fields of a register and not an array, do not create a Flatten view. Instead, create a Selection view as below: Right click on the base view -> select “New” -> Selection -> Edit tab of the view -> Output -> Right click on the Register column that needs to be flattened -> Project subfields of the “column” (You can also select the “Project subfields of the “column” recursively” option to flatten registers-within-registers in a single step) To flatten arrays, for each “flattening” operation, it is possible to select just one array. If there is more than one array, this operation will be repeated for each one. So, here you will have stacked views to flatten multiple arrays and will also allow some control over the multiplicity between them. Even if the user has constructed multiple flatten views referencing each other, there will be one final view that can be exposed to the users. Using the fields obtained after the above operations, you can implement the [Group_Concat](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/aggregation_functions#group-concat) function which outputs a string result with the concatenated non-NULL values from a group as per the requirement. Hope this helps!
Denodo Team
15-04-2019 17:47:30 -0400
You must sign in to add an answer. If you do not have an account, you can register here