Converting flat structure table to 3 level hierarchy json object

Hi Team, We have a requirement to convert denormalized table structure as a source and we want to provide 3 level hierarchy json object what would be the best approach using Denodo?
07-03-2017 15:26:11 -0500

5 Answers

Hi, In Dendo you can use the NEST and REGISTER operations that will be transformed into arrays and registers in JSON when publishing a view in this format. You can find examples of those functions in the Advanced VQL guide: Example Consider the view V: | intsample | textsample | registersample | | -------- | -------- | -------- | | 1| A|Register { hello , how're you }| | 1| B|Register { hello, good bye } | |2|C|Register { another string, last string }| Executing the following query you will get a hierarchy when publishing as JSON: ``` SELECT intsample, NEST(textsample, registersample) AS nestsample FROM V GROUP BY intsample; ``` | intsample | nestsample | | -------- | -------- | |1| Array [ A, Register { hello , how're you } B, Register { hello, good bye } ] | |2| Array [ C, Register { another string, last string } ] | I hope it helps!
Denodo Team
08-03-2017 16:51:27 -0500
Thanks for the response, NEST works for single hierarchy. Not sure there is any funciton which can do Nested hierarchies of the columns. Flat structure: Country Name, State Name, Street Name,Address1,address2,zip Nested hierarchy: Country->Name State ->Name Cities-> Name Street-> Name Address->Address1 Address2 Zip
13-03-2017 13:10:22 -0400
You can use an array field (result of a NEST function) as the input for another NEST operation in order to create nested hierarchies. From your example it is not clear to me what you are trying to accomplish. Feel free to extend your example with actual data to see how the 3 level hierarchy looks like.
Denodo Team
15-03-2017 13:50:54 -0400
Hi Thanks for the repsonse, I was not able to show the exact hierarchy in this rich textbox as it is always doing flattern: Description: Country is an object and it has name atribute and State another object State has again Name and cities as another object City has name and Street objects init Steet has name and address object Adress object has address1, address 2 and zip attributes. Order: Country->State->City->Street->Adress Country->Name State ->Name Cities-> Name Street-> Name Address->Address1 Address2 Zip
15-03-2017 15:22:56 -0400
As I mentioned in my previous answer you can have multiple hierarchies of NEST/REGISTER functions to add any hierarchy you need. The following answer may help too I hope this helps!
Denodo Team
28-03-2017 11:52:19 -0400
You must sign in to add an answer. If you do not have an account, you can register here