We are recently getting stood up with Denodo for our organization and I am trying to find native functions that will work for JSON parsing. This is a bit different than the "flatten" or other abstractions that you can do from VDP, but rather manipulating strings of JSON which are already present within SQL server or (In my case) HIVE.
We have several ETL processes that bring in full JSON dict files and store each JSON file as a record in our system. When looking at it from something like HIVE, its just a STRING type field, but within the field its all JSON. Hive and Presto and Postgres all have native JSON functions such as "Get_JSON_Object", for which you just pass the field in question, the key and the separator. The function then returns the value of that Key and transforms they Key into a column. An example would look like this:
"time" as eventts,
CAST("time" AS date) as eventdate
So from the above, we can keep all the JSON in one single field and then parse out the events we need, on the fly.
Question: Can you do such a thing with Denodo (looking for the function name, if possible) OR do we need to prep the data into a view within HIVE, using this above code, before introspecting with Denodo?
Thanks very much!!