Solved

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

8 months ago
24 August 2023
7 replies
127 views

Akshay Hande
Active
1 reply

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.

icon

Best answer by judekumar87 25 August 2023, 09:03

View original

7 replies

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
11 replies
8 months ago
25 August 2023
Answer

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.

Akshay Hande
Author
Active
1 reply
8 months ago
25 August 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?

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!