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

7 replies
244 views

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.

judekumar87Answer

Practitioner ⭐️⭐️⭐️

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.

C

Clement

Active ⭐️⭐️⭐️

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!

judekumar87Answer

Practitioner ⭐️⭐️⭐️

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.

A

Akshay HandeAuthor

Seasoned ⭐️⭐️

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?

C

Clement

Active ⭐️⭐️⭐️

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!