Not able to run the snowflake custom queries with flatten json columns

rahulrajan — Wed, 06 Nov 2024 08:19:58 GMT

Hi In our project, we are loading data from snowflake tables into Reltio. We are receiving complex json data in some of the columns.
We have flatten the json column and able to get the proper result in snowflake but when we use the same query in RIH(Reltio Integration Hub), we are getting below error.I guess in query we are using ":" to flatten json data which is causing issue.

Error :
SQL: Bind variable "Name" is either missing or does not have a value in input field

Sample Query Used:
select id,
b.value:Name as Name from table a,
lateral flatten(input => try_parse_json(a.jsonArray)) b

Can you please let us if anyone has come across the issue and any suggestion is appreciated
Note: When we use the same query to generate the output schema in RIH, output schema got generated successfully.

Re: Not able to run the snowflake custom queries with flatten json columns

rahulrajan — Thu, 07 Nov 2024 17:09:05 GMT

This is resolved. Need to use b.value['Name'] in custom sql instead of b.value:Name

topic Not able to run the snowflake custom queries with flatten json columns in Workato Pros Discussion Board

Not able to run the snowflake custom queries with flatten json columns

Re: Not able to run the snowflake custom queries with flatten json columns