Hello Is there a way to configure transformations on json fi

Question

Hello Is there a way to configure transformations on json fields to extract a certain property where this property may not always appear I ve configured the following ``` columnName audioLength transformFunction JSONPATH result $ audioLength ``` But the result field configured as ``` name result dataType STRING ``` Most of the time will have the following content ``` metadata isMatch Y ``` But sometimes it can be something like ``` AudioCreated 2021 05 06T23 40 28 6629486 AudioLength 00 04 02 1800000 BlobPath 068fd3f0 e5d6 499a bfb0 94491499aba6 9db5efb9 4a72 44ae a570 8647e1ac896a 33d3c59d b8e1 4818 be60 124e637fb02b wav isValid true feedback otherFeedback result 1 crowdMemberId 90c97d94 91c3 4587 8c91 26f6e971d52c tags null scriptToExecute null ```

Pedro Silva · Answer

Currently the way the transformation is set up it looks like `JSONPATH` always assumes the field exists if the incoming json string field in this case `result` is not null Logs from the server instance ```java lang IllegalStateException Caught exception while invoking method public static java lang Object org apache pinot common function scalar JsonFunctions jsonPath java lang Object java lang String with arguments metadata isMatch Y $ audioLength at org apache pinot common function FunctionInvoker invoke FunctionInvoker java 148 ~ pinot all 0 7 1 jar with dependencies jar 0 7 1 afa4b252ab1c424ddd6c859bb305b2aa342b66ed at org apache pinot core data function InbuiltFunctionEvaluator$FunctionExecutionNode execute InbuiltFunctionEvaluator java 137 ~ pinot all 0 7 1 jar with dependencies jar 0 7 1 afa4b252ab1c424ddd6c859bb305b2aa342b66ed at org apache pinot core data function InbuiltFunctionEvaluator evaluate InbuiltFunctionEvaluator java 104 ~ pinot all 0 7 1 jar with dependencies jar 0 7 1 afa4b252ab1c424ddd6c859bb305b2aa342b66ed at org apache pinot core data recordtransformer ExpressionTransformer transform ExpressionTransformer java 95 ~ pinot all 0 7 1 jar with dependencies jar 0 7 1 afa4b252ab1c424ddd6c859bb305b2aa342b66ed at org apache pinot core data recordtransformer CompositeTransformer transform CompositeTransformer java 82 ~ pinot all 0 7 1 jar with dependencies jar 0 7 1 afa4b252ab1c424ddd6c859bb305b2aa342b66ed at org apache pinot core data manager realtime LLRealtimeSegmentDataManager processStreamEvents LLRealtimeSegmentDataManager java 491 pinot all 0 7 1 jar with dependencies jar 0 7 1 afa4b252ab1c424ddd6c859bb305b2aa342b66ed at org apache pinot core data manager realtime LLRealtimeSegmentDataManager consumeLoop LLRealtimeSegmentDataManager java 402 pinot all 0 7 1 jar with dependencies jar 0 7 1 afa4b252ab1c424ddd6c859bb305b2aa342b66ed at org apache pinot core data manager realtime LLRealtimeSegmentDataManager$PartitionConsumer run LLRealtimeSegmentDataManager java 538 pinot all 0 7 1 jar with dependencies jar 0 7 1 afa4b252ab1c424ddd6c859bb305b2aa342b66ed at java lang Thread run Thread java 748 1 8 0 282 Caused by java lang reflect InvocationTargetException at sun reflect GeneratedMethodAccessor210 invoke Unknown Source ~ at sun reflect DelegatingMethodAccessorImpl invoke DelegatingMethodAccessorImpl java 43 ~ 1 8 0 282 at java lang reflect Method invoke Method java 498 ~ 1 8 0 282 at org apache pinot common function FunctionInvoker invoke FunctionInvoker java 145 ~ pinot all 0 7 1 jar with dependencies jar 0 7 1 afa4b252ab1c424ddd6c859bb305b2aa342b66ed 8 more Caused by com jayway jsonpath PathNotFoundException No results for path $ audioLength at com jayway jsonpath internal path EvaluationContextImpl getValue EvaluationContextImpl java 133 ~ pinot all 0 7 1 jar with dependencies jar 0 7 1 afa4b252ab1c424ddd6c859bb305b2aa^C```

Jackie · Answer

Hi Pedro For this use case you can use `JsonPathString` with 3 arguments where the third argument is the default value when the field does not exist