jsonextractscalar

Evaluates the 'jsonPath' on jsonField, returns the result as the type 'resultsType', use optional defaultValuefor null or parsing error.
Signature
JSONEXTRACTSCALAR(jsonField, 'jsonPath', 'resultsType', [defaultValue])
Arguments
Description
jsonField
An Identifier/Expression contains JSON documents.
'jsonPath'
Follows JsonPath Syntax to read values from JSON documents.
'results_type'
One of the Pinot supported data types:INT, LONG, FLOAT, DOUBLE, BOOLEAN, TIMESTAMP, STRING,
INT_ARRAY, LONG_ARRAY, FLOAT_ARRAY, DOUBLE_ARRAY, STRING_ARRAY.
'jsonPath'and`` ``'results_type'are literals. Pinot uses single quotes to distinguish them from identifiers.
Usage Examples
The examples in this section are based on the Batch JSON Quick Start. In particular we'll be querying the row WHERE id = 7044874109:
1
select repo
2
from githubEvents 
3
WHERE id = 7044874109
Copied!
repo
{"id":115911530,"name":"LimeVista/Tapes","url":"https://api.github.com/repos/LimeVista/Tapes"}
The following examples show how to use the JSONEXTRACTSCALAR function:
1
select id, jsonextractscalar(repo, '$.name', 'STRING') AS name
2
from githubEvents 
3
WHERE id = 7044874109
Copied!
id
name
7044874109
LimeVista/Tapes
1
select id, jsonextractscalar(repo, '$.foo', 'STRING') AS name
2
from githubEvents 
3
WHERE id = 7044874109
Copied!
1
[
2
  {
3
    "message": "QueryExecutionError:\njava.lang.RuntimeException: Illegal Json Path: [$.foo], when reading [{\"id\":115911530,\"name\":\"LimeVista/Tapes\",\"url\":\"https://api.github.com/repos/LimeVista/Tapes\"}]\n\tat org.apache.pinot.core.operator.transform.function.JsonExtractScalarTransformFunction.transformToStringValuesSV(JsonExtractScalarTransformFunction.java:254)\n\tat org.apache.pinot.core.operator.docvalsets.TransformBlockValSet.getStringValuesSV(TransformBlockValSet.java:90)\n\tat org.apache.pinot.core.common.RowBasedBlockValueFetcher.createFetcher(RowBasedBlockValueFetcher.java:64)\n\tat org.apache.pinot.core.common.RowBasedBlockValueFetcher.<init>(RowBasedBlockValueFetcher.java:32)",
4
    "errorCode": 200
5
  }
6
]
Copied!
1
select id, jsonextractscalar(repo, '$.foo', 'STRING', 'dummyValue') AS name
2
from githubEvents 
3
WHERE id = 7044874109
Copied!
id
name
7044874109
dummyValue

jsonextractkey

length

Last modified 6mo ago

Copy link

Contents

Signature

Usage Examples