Question

# Flexible data modeling - transformation to JsonObject type

• 7 replies
• 129 views

• Active Member
• 3 replies

Hi,

I have created one FDM model having JsonObject field. In the transformation query I referred that field , example query:

SELECT Series_Title as externalId,
Series_Title as name,
Overview as description,
int(Gross) as gross,
float(IMDB_Rating) as imdbRating,
int(Runtime) as runTime,
int(Released_Year) as releasedYear,
'{"key1":"value1","key2":{"key3":12,"key4":"hello","key5":{"key6":10}}}' as data
FROM movies.movies

FDM model is:

type Movie {

name: String!

description: String

watchedIt: Boolean

imdbRating: Float

releasedYear: Int

runTime: Int

gross: Int

actors: [Actor]

data: JSONObject

}

The transformation is failing with this error:

Unknown property type Json.

### 7 replies

Hi,

You are not missing anything :)

Currently Transformation does not support writing to Json type yet. We have this feature in the backlog and will work on it.

Thanks @Vu Hai Nguyen  for the quick response. I have follow up question on using TimeSeries Type in FDM data model. Is it the native CDF timeseries resource type. Can we link FDM field to existing timeseries resource type to refer the existing timeseries data. If it is true , How can I use that in transformation, to link the timeseries data.

Hi,

We made the fix to support Json type can you try it again?
I don’t think we have well support to link timeseries data with FDM yet. But there’s work on it to grow with FDM as well  :)

Userlevel 1
+1

I'm just following up from my end. Were you able to check whether the Json type worked for you?

Best regards,
Dilini

Hello @Dilini Fernando ,
I was trying the json thing and stuck at transformation phase. If you check in the following screenshot look at “data” field.
I have used to_json in the query thinking it will convert it to json. But as you can see in the Result section data is still String and not converting to JSON.

Can you help ?

Hi @Neerajkumar Bhatewara ,

You can ingest data for JSON property type using the function to_json or even raw json string, both syntax below should work:

to_json(named_struct("string_val", "toto", "int_val", 1)) as prop_json'{    "int_val": 2,    "string_val": "tata",    "struct_val":    {        "name": "jetfire",        "age": 25.0    }}' as prop_json
Please ignore the warning type string, since we can’t infer the result type that it is JSON or raw json string. If you run the transformation it should ingest data successfully. Let me know if you encounter any issue :)

Hi @Vu Hai Nguyen
I tried using approach 2 ie raw json string and transformed but getting error while querying. Will try with approach one having to_json
'{ "int_val": 2, "string_val": "tata", "struct_val": { "name": "jetfire", "age": 25.0 }}' as data

{
"errors": [
{
"message": "Something went wrong, we were not able to run your query.",
"code": 400,
"type": "VALIDATION",
"errors": [
{
"message": "Can't serialize value (/listMovie/edges[0]/node/data) : Expected a type that could be converted to JSONObject, but was java.lang.String",
"path": [
"listMovie",
"edges",
0,
"node",
"data"
],
"extensions": {
"classification": "DataFetchingException"
}
}
]
}
]
}

Regards,

Neeraj Bhatewara