Custom transform not getting applied in wrangler in Google Cloud Data Fusion

Question

I am trying to following custom transform in a wrangler in Google Cloud Data Fusion.

set-column column (parse-as-json :column 2 ) ? column =^ "[" : (parse-as-json :column 1 )

I want to parse column as JSON to a depth of 2 if it is an array, which means if it starts with a square bracket ([), otherwise to a depth of 1. I am not sure if the colon in parse-as-json directive is causing issue here.

If I change it to following, it works fine:-

set-column column 'a' ? column =^ "[" : 'b'

I have also tried escaping the colon in parse-as-json directive with a backslash,still didn't work. What am I doing wrong here? Please suggest.

Answer 1

We don't currently support nested directives (ie. set-column with parse-as-json).

You can try first doing a copy of the column, then parse one copy with depth 1, and the parse the clone with depth 2. Then finally you can use the set-column to pick the column that is correct.

For example, if let's say the original column is called 'body', and depth 2 will produce null when it doesn't start with "[", you can do something like this:

copy body body_clone
parse-as-json body 1
parse-as-json body_clone 2
set-column final_result !body_clone_field ? body_field : body_clone_field