Solved

Flexible data modeling - transformation to JsonObject type

  • 21 November 2022
  • 10 replies
  • 306 views

Userlevel 1

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.

 

May be I am missing something, Can you please help?

icon

Best answer by Dilini Fernando 31 March 2023, 09:15

View original

10 replies

Userlevel 1
Badge

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 :)
Userlevel 1

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

Userlevel 1
Badge

Hi,

The issue is fixed now. Can you try to re-ingest data with transformation and querying it again?

Userlevel 6
Badge

HI @Neerajkumar Bhatewara just checking if the above issue is fixed? 

Userlevel 1
Badge

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.

 

Userlevel 1

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.

Userlevel 1
Badge

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 4
Badge +2

Hi @Neerajkumar Bhatewara,

I hope the above reported issue is fixed. As of now, I will solve this thread. 

If you have any questions please free to post here. 

Br,
Dilini

Userlevel 4
Badge +2

Hi @Niranjan Madhukar Karvekar ,

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

Best regards,
Dilini

Userlevel 1

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 ?
 

 

Reply