Skip to main content
Answer

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

  • August 24, 2023
  • 7 replies
  • 177 views
A
Committed

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

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.

7 replies

C
Active
Forum|alt.badge.img+1
  • Clement
  • Active
  • August 24, 2023

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!

judekumar87
Practitioner
Forum|alt.badge.img+2
  • judekumar87
  • Practitioner
  • Answer
  • August 25, 2023

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
Committed
  • Akshay Hande
  • Author
  • Committed
  • August 25, 2023

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
Active
Forum|alt.badge.img+1
  • Clement
  • Active
  • August 25, 2023

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!

judekumar87
Practitioner
Forum|alt.badge.img+2
  • judekumar87
  • Practitioner
  • September 24, 2023

Hi Akshay,

Sorry for the late reply.

 

 

I hope the above helps.

C
Seasoned Practitioner
  • Carin Meems
  • Seasoned Practitioner
  • October 24, 2023

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

C
Active
Forum|alt.badge.img+1
  • Clement
  • Active
  • October 31, 2023

thanks. feel free to close this ticket

Terms & ConditionsCookie SettingsAccessibility statement