Skip to main content

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. 

 

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, ...)? 

 

Thanks!


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. 
 

Example:

Table 

Transformation


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?


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!


Hi Akshay,

Sorry for the late reply.

 

 

I hope the above helps.


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


thanks. feel free to close this ticket


Reply