https://pinot.apache.org/ logo
p

Pedro Silva

05/07/2021, 3:20 PM
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:
Copy code
{
  "columnName": "audioLength",
  "transformFunction": "JSONPATH(result,'$.audioLength')"
}
But the result field, configured as:
Copy code
{
  "name": "result",
  "dataType": "STRING"
}
Most of the time will have the following content:
Copy code
{
  "metadata": {
    "isMatch": "Y"
  }
}
But sometimes it can be something like:
Copy code
{
  "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
}
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:
Copy code
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
j

Jackie

05/07/2021, 5:00 PM
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
❤️ 1