Transform raw table column having JSON as Object type to Data Model

  • 24 August 2023
  • 7 replies

Hi, I want to transform JSON object to Data model column. JSON is populated from extractor to raw table, so its type is by default Object and its structure will be nested.

Is there any built-in/ Custom function using which I can parse this json  and convert the specific value into column.


I have tried to_josn () which will convert the json structure to string and then with the help of get_json_object() I can get expected value by passing json path to this function. But problem here is it will always return value as String. 



Best answer by judekumar87 25 August 2023, 09:03

View original

7 replies

Badge +1

I have same question! If the transform target is a JSONObject, what’s the built-in SQL function should I use, and what’s expected format in the RAW table column ({“key”:”value”}, or, {“key”, “value”}, or, {\”key\”:\”value\”}, or, ...)? 



Badge +2

Hi Akshay,

Kindly refer to our documentation for information on how to parse a JSON object. In my below example I have parsed one of the json value to a column double. 




Please let me know if this does not answer your question.

Thank you @judekumar87 for your response, but it seems like your JSON column type is String, but I am trying to parse object type directly

Just like tags and data column below


So is there a way, or we always need to convert the object into string first?

Badge +1

Hi @judekumar87 , can you cast json_1 to a JSONObject type column without knowing the struct of json_1? What if json_1 is an arbitrary json string that could vary with raw tables? Thanks!

Badge +2

Hi Akshay,

Sorry for the late reply.



I hope the above helps.

Userlevel 3

Hi @Akshay Hande and @Clement, would you need any further information?

Badge +1

thanks. feel free to close this ticket