You can translate the question and the replies:

How to flatten an unknown hierarchy

I have a JSON datasource which returns a hierarchy along the lines of the JSON below. The key thing here is that from one web service call to the next the folder structure can differ i.e. each folder is an array and can contain different numbers of sub arrays, so standard flattening doesn't seem to lend itself to work very well here. Is there a way that I can dynamically have the view roll everything in a hierarchy up? **The data I have** ``` { "toplevel": { "attr1": true, "outline": { "folder": [ { "name": "Folder1", "folder": [ { "name": "Folder2", "description": "blah", "item": [ { "@type": "Dimension", "id": "DOf3" }, { "@type": "Dimension", "id": "DOf4" } ] }, { "name": "Folder3", "description": "blahblah", "item": [ { "@type": "Dimension", "id": "DO1c26" } ] } ] } ] } } } ``` **What I'd like to end up with. ** Note, foldername should be a concatenation of all the parent folders. ``` toplevel attr1 foldername folderdescription itemid itemtype ``` Is this possible?
user
17-01-2018 11:31:08 -0500

1 Answer

Hi, There are a couple of ways to go about this, depending on just how variable and complex your JSON can be. If by any chance this JSON happens to be coming from Hive, I would create a Hive data source instead of a JSON data source and use the *Create from query* option to leverage Hive JSON parsing tools as outlined in [Accessing hierarchial JSON data in Hive from Denodo](https://community.denodo.com/kb/view/document/Accessing%20hierarchical%20JSON%20data%20in%20Hive%20from%20Denodo?category=Data+Sources). Assuming you're not using Hive, though, there are two other approaches that I'd consider. Firstly, if there's a reasonably finite number of possible branches in your JSON, you can accomplish this by flattening the same Base View multiple times, and then creating a Union of the resulting Derived Views. On those flattened views, you can do the concatenating of the *outline_folder_folder_folder_name* style fields in order to get your desired parent path, and project the other fields as needed to get your desired output. These views should all have the same structure, even though one will have a *folderdescription* projected from *outline_folder_folder_description* and another projected from *outline_folder_folder_folder_description* (and so on), so that the Union will result in a properly flattened Dervied View. If possible, I'd use a sample JSON that includes at least one value in all *possible* nodes; for example, in your sample above, if "Folder1" *can* have a description, make sure it (or a sibling) does when you generate your Base View. If the JSON isn't predictable enough to manage in this manner, you can build your own parser using one of the many JSON frameworks available for Java. You can implement this in Denodo as either a [Custom Wrapper](https://community.denodo.com/docs/html/browse/6.0/vdp/developer/developing_extensions/developing_custom_wrappers/developing_custom_wrappers) which you can then use in lieu of the standard JSON Wrapper, or as a [Custom Function](https://community.denodo.com/docs/html/browse/6.0/vdp/developer/developing_extensions/developing_custom_functions/developing_custom_functions) that you can execute against a JSON value retrieved as a single text string. I think the wrapper would likely be more efficient, and allow more flexibility in working with the resulting Base View. One side note; given your sample data and desired output, you should not expect 'toplevel' in your output, as it is a Register (of attr1 and outline) and has no value of its own to project as a column. I'm sure this is likely a matter of simplifying your example for this Community post, but wanted to bring it up just in case it wasn't. Hope this helps!
Denodo Team
22-01-2018 12:52:33 -0500
You must sign in to add an answer. If you do not have an account, you can register here