Checks if int value exists in array.

Signature

arrayContainsInt('colName', valueToFind)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivAirportIDs, 
       arrayContainsInt(DivAirportIDs, 14683) AS containsValue
from airlineStats 
WHERE arraylength(DivAirportIDs) >= 2
limit 5

Sum of at least two values

Signature

ADD(col1, col2, col3...)

Usage Examples

These examples are based on the Batch Quick Start.

select homeRuns, baseOnBalls, ADD(homeRuns, baseOnBalls) AS total
from baseballStats 
WHERE teamID = 'ML1' 
AND yearID = 1956 
AND playerName = 'Henry Louis'

Absolute of a value

Signature

ABS(col1)

Usage Examples

select ABS(-12.1) AS value
from ignoreMe

select ABS(12.1) AS value
from ignoreMe

Checks if string value exists in array.

Signature

arrayContainsString('colName', valueToFind)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivTailNums, 
       arrayContainsString(DivTailNums, 'N7713A') AS index
from airlineStats 
WHERE arraylength(DivTailNums) >= 2
limit 5

Concatenates two arrays of strings.

Signature

arrayConcatString('colName1', 'colName2')

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivTailNums, 
       arrayConcatString(DivTailNums, DivTailNums) AS concatIds
from airlineStats 
WHERE arraylength(DivTailNums) >= 2
limit 5

Finds the last index of the given value in the array starting at the given index.

Signature

arrayIndexOfString('colName', valueToFind)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivTailNums, 
       arrayIndexOfString(DivTailNums, 'N7713A') AS index
from airlineStats 
WHERE arraylength(DivTailNums) >= 2
limit 5

Finds the last index of the given value in the array starting at the given index.

Signature

arrayIndexOfInt('colName', valueToFind)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivAirportIDs, 
       arrayIndexOfInt(DivAirportIDs, 14683) AS index
from airlineStats 
WHERE arraylength(DivAirportIDs) >= 2
limit 5

Returns unique values in an array of strings.

Signature

arrayDistinctString('colName')

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivTailNums, 
       arrayDistinctString(DivTailNums) AS unique
from airlineStats 
WHERE arraylength(DivTailNums) >= 2
limit 5

Reverses array of ints.

Signature

arrayReverseInt('colName')

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivAirportIDs, 
       arrayReverseInt(DivAirportIDs) AS reversedIds
from airlineStats 
WHERE arraylength(DivAirportIDs) >= 2
limit 5

Removes value from array of ints.

Signature

arrayRemoveInt('colName', value)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivAirportIDs, 
       arrayRemoveInt(DivAirportIDs, 12892) AS value
from airlineStats 
WHERE arraylength(DivAirportIDs) >= 2
AND arrayContainsInt(DivAirportIDs, 12892) = 1
limit 5

Returns the length of a multi-value column

Signature

ARRAYLENGTH('colName')

Usage Examples

These examples are based on the Hybrid Quick Start.

select ARRAYLENGTH(RandomAirports) AS length, count(*) 
from airlineStats 
GROUP BY length
ORDER BY count(*) DESC
LIMIT 5

Removes value from array of strings.

Signature

arrayRemoveString('colName', value)

Usage Examples

These examples are based on the Hybrid Quick Start.

select RandomAirports, 
       arrayRemoveString(RandomAirports, 'SEA') AS value
from airlineStats 
WHERE arraylength(RandomAirports) BETWEEN 2 AND 4
limit 5

Returns unique values in an array of ints.

Signature

arrayDistinctInt('colName')

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivAirportIDs, 
       arrayDistinctInt(DivAirportIDs) AS unique
from airlineStats 
WHERE arraylength(DivAirportIDs) >= 2
limit 5

Returns the values in the array between the start and end positions.

Signature

arraySliceInt('colName', start, end)

Usage Examples

These examples are based on the Hybrid Quick Start.

select FlightNum, 
       arraySliceInt(DivAirportIDs, 0, 1) AS airports, 
	     DivAirportIDs
from airlineStats 
WHERE arraylength(DivAirportIDs) >= 2
limit 5

Create a union of two arrays of ints.

Signature

arrayUnionInt('colName1', 'colName2')

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivWheelsOffs, 
       DivWheelsOns,
       arrayUnionInt(DivWheelsOffs, DivWheelsOns) AS unionIds
from airlineStats 
WHERE arraylength(DivWheelsOffs) >= 2
limit 5

Reverses array of strings.

Signature

arrayReverseString('colName')

Usage Examples

These examples are based on the Hybrid Quick Start.

select FlightNum, 
       arrayReverseString(RandomAirports) AS reversedAirports, 
       RandomAirports
from airlineStats 
WHERE arraylength(RandomAirports) BETWEEN 2 AND 4
limit 5

Sorts array of ints.

Signature

arraySortInt('colName')

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivAirportIDs, 
       arraySortInt(DivAirportIDs) AS sortedIds
from airlineStats 
WHERE arraylength(DivAirportIDs) >= 2
limit 5

Rounded up to the nearest integer.

Signature

CEIL(col1)

Usage Examples

select CEIL(12.1) AS value
from ignoreMe

select CEIL(-12.1) AS value
from ignoreMe

Returns the values in the array between the start and end positions.

Signature

arraySliceString('colName', start, end)

Usage Examples

These examples are based on the Hybrid Quick Start.

select FlightNum, 
       arraySliceString(RandomAirports, 0, 2) AS airports, 
       RandomAirports
from airlineStats 
WHERE arraylength(RandomAirports) BETWEEN 2 AND 4
limit 5

Concatenates two arrays of ints.

Signature

arrayConcatInt('colName1', 'colName2')

Usage Examples

These examples are based on the .

Get the avg of values in a group

Signature

AVGMV(colName)

Usage Examples

These examples are based on the Hybrid Quick Start.

select AVGMV(DivLongestGTimes) AS value
from airlineStats 
where arraylength(DivLongestGTimes) > 1

Create a union of two arrays of strings.

Signature

arrayUnionString('colName1', 'colName2')

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivTailNums, 
       DivAirports,
       arrayUnionString(DivTailNums, DivAirports) AS unionIds
from airlineStats 
WHERE arraylength(DivTailNums) >= 2
limit 5

the Unicode codepoint of the first character of the string

Signature

CODEPOINT(col)

Usage Examples

SELECT CODEPOINT('Apache Pinot') AS value
FROM ignoreMe

the character corresponding to the Unicode codepoint

Signature

CHR(codepoint)

Usage Examples

SELECT CHR(65) AS value
FROM ignoreMe

Returns the day of the year from the given epoch millis in UTC or specified timezone. The value ranges from 1 to 366.

Signature

dayOfYear(tsInMillis)

dayOfYear(tsInMillis, timeZoneId)

doy(tsInMillis)

doy(tsInMillis, timeZoneId)

Usage Examples

Concatenate two input strings using the seperator

Signature

CONCAT(col1, col2, seperator)

Usage Examples

SELECT concat('Apache', 'Pinot', ' ') AS value
FROM ignoreMe

SELECT concat('real-time', 'analytics', '__') AS value
FROM ignoreMe

Returns the count of distinct row values in a group

Signature

DISTINCTCOUNT(colName)

Usage Examples

These examples are based on the Batch Quick Start.

select DISTINCTCOUNT(league) AS value
from baseballStats 

select DISTINCTCOUNT(teamID) AS value
from baseballStats 

Get the count of rows in a group

Signature

COUNTMV(colName)

Usage Examples

These examples are based on the .

The following query returns the documents that have a DivTailNums with more than one value:

You can count the number of items in these rows by running the following query:

Get the count of rows in a group

Signature

COUNT(colName)

Usage Examples

These examples are based on the Batch Quick Start.

select count(*) AS value
from baseballStats 

Returns the day of the month from the given epoch millis in UTC or specified timezone. The value ranges from 1 to 31.

Signature

day(tsInMillis)

day(tsInMillis, timeZoneId)

dayOfMonth(tsInMillis)

dayOfMonth(tsInMillis, timeZoneId)

Usage Examples

select day(1639351800000) AS day
FROM ignoreMe

select day(1639351800000, 'CET') AS day
FROM ignoreMe

select dayOfMonth(1639351800000) AS day
FROM ignoreMe

select dayOfMonth(1639351800000, 'CET') AS day
FROM ignoreMe

Sorts array of strings.

Signature

arraySortString('colName')

Usage Examples

These examples are based on the .

Returns the day of the week from the given epoch millis in UTC timezone. The value ranges from 1(Monday) to 7(Sunday).

Signature

dayOfWeek(tsInMillis)

dayOfWeek(tsInMillis, timeZoneId)

dow(tsInMillis)

dow(tsInMillis, timeZoneId)

Usage Examples

select dayOfWeek(1639351800000) AS dayOfWeek
FROM ignoreMe

select dayOfWeek(1639351800000, 'CET') AS dayOfWeek
FROM ignoreMe

select dow(1639351800000) AS dayOfWeek
FROM ignoreMe

select dow(1639351800000, 'CET') AS dayOfWeek
FROM ignoreMe

Returns the distinct row values in a group

Signature

DISTINCT(colName)

Usage Examples

These examples are based on the Batch Quick Start.

select DISTINCT league AS value
from baseballStats 

select DISTINCT(league) AS value
from baseballStats 

Returns an approximate distinct count using HyperLogLog in a group

Signature

DISTINCTCOUNTHLLMV(colName)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DISTINCTCOUNTHLLMV(DivLongestGTimes) AS value
from airlineStats 
where arraylength(DivLongestGTimes) > 1

Returns the count of distinct row values in a group. This function is accurate for INT column, but approximate for other cases where hash codes are used in distinct counting and there may be hash collisions. For accurate distinct counting on all column types, see DISTINCTCOUNT.

Signature

DISTINCTCOUNTBITMAP(colName)

Usage Examples

These examples are based on the Batch Quick Start.

select DISTINCTCOUNTBITMAP(league) AS value
from baseballStats 

select DISTINCTCOUNTBITMAP(teamID) AS value
from baseballStats 

The Theta Sketch framework enables set operations over a stream of data, and can also be used for cardinality estimation. Pinot leverages the Sketch Class and its extensions from the library org.apache.datasketches:datasketches-java:1.2.0-incubating to perform distinct counting as well as evaluating set operations.

Signature

DISTINCTCOUNTRAWTHETASKETCH(<thetaSketchColumn>, <thetaSketchParams>, predicate1, predicate2..., postAggregationExpressionToEvaluate) -> HexEncoded

• thetaSketchColumn (required): Name of the column to aggregate on.

• thetaSketchParams (required): Parameters for constructing the intermediate theta-sketches.

  • Currently, the only supported parameter is nominalEntries (defaults to 4096).

• predicates (optional)_: _ These are individual predicates of form lhs <op> rhs which are applied on rows selected by the where clause. During intermediate sketch aggregation, sketches from the thetaSketchColumn that satisfies these predicates are unionized individually. For example, all filtered rows that match country=USA are unionized into a single sketch. Complex predicates that are created by combining (AND/OR) of individual predicates is supported.

• postAggregationExpressionToEvaluate (required): The set operation to perform on the individual intermediate sketches for each of the predicates. Currently supported operations are SET_DIFF, SET_UNION, SET_INTERSECT , where DIFF requires two arguments and the UNION/INTERSECT allow more than two arguments.

Usage Examples

These examples are based on the Batch Quick Start.

select distinctCountRawThetaSketch(teamID) AS value
from baseballStats 

select distinctCountRawThetaSketch(teamID, 'nominalEntries=10') AS value
from baseballStats

We can also provide predicates and a post aggregation expression to compute more complicated cardinalities:

select distinctCountRawThetaSketch(
  yearID, 
  'nominalEntries=4096', 
  'teamID = ''SFN'' AND numberOfGames=28 AND homeRuns=1',
  'teamID = ''CHN'' AND numberOfGames=28 AND homeRuns=1',
  'SET_INTERSECT($1, $2)'
) AS value
from baseballStats 

Returns the count of distinct row values in a group

Signature

DISTINCTCOUNTMV(colName)

Usage Examples

These examples are based on the .

The following query returns the documents that have a DivTailNums with more than one value:

You can count the distinct number of items in these rows by running the following query:

Returns the count of distinct row values in a group. This function is accurate for INT or dictionary encoded column, but approximate for other cases where hash codes are used in distinct counting and there may be hash collision.

Signature

DISTINCTCOUNTBITMAPMV(colName)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DISTINCTCOUNTBITMAPMV(DivLongestGTimes) AS value
from airlineStats 
where arraylength(DivLongestGTimes) > 1

select DISTINCTCOUNTBITMAPMV(DivTailNums) AS value
from airlineStats 
where arraylength(DivTailNums) > 1

Returns HLL response serialized as string. The serialized HLL can be converted back into an HLL and then aggregated with other HLLs. A common use case may be to merge HLL responses from different Pinot tables, or to allow aggregation after client-side batching.

Signature

DISTINCTCOUNTRAWHLLMV(colName, log2m)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DISTINCTCOUNTRAWHLLMV(DivAirports) AS value
from airlineStats 
where arraylength(DivAirports) > 1

select DISTINCTCOUNTRAWHLLMV(DivAirports, 1) AS value
from airlineStats 
where arraylength(DivAirports) > 1

Returns an approximate distinct count using HyperLogLog. It also takes an optional second argument to configure the log2m for the HyperLogLog. For accurate distinct counting, see DISTINCTCOUNT.

Signature

DISTINCTCOUNTHLL(colName, log2m)

Usage Examples

These examples are based on the Batch Quick Start.

select DISTINCTCOUNTHLL(teamID) AS value
from baseballStats 

select DISTINCTCOUNTHLL(teamID, 12) AS value
from baseballStats 

Rounded down to the nearest integer.

Signature

FLOOR(col1)

Usage Examples

select FLOOR(12.1) AS value
from ignoreMe

select FLOOR(-12.1) AS value
from ignoreMe

Quotient of two values

Signature

DIV(col1, col2)

Usage Examples

These examples are based on the Batch Quick Start.

select homeRuns, numberOfGames, DIV(homeRuns, numberOfGames) AS total
from baseballStats 
WHERE teamID = 'ML1' 
AND yearID = 1956 
AND playerName = 'Henry Louis'

The Theta Sketch framework enables set operations over a stream of data, and can also be used for cardinality estimation. Pinot leverages the Sketch Class and its extensions from the library org.apache.datasketches:datasketches-java:1.2.0-incubating to perform distinct counting as well as evaluating set operations.

Signature

DistinctCountThetaSketch(<thetaSketchColumn>, <thetaSketchParams>, predicate1, predicate2..., postAggregationExpressionToEvaluate) -> Long

• thetaSketchColumn (required): Name of the column to aggregate on.

• thetaSketchParams (required): Parameters for constructing the intermediate theta-sketches.

  • Currently, the only supported parameter is nominalEntries (defaults to 4096).

• predicates (optional)_: _ These are individual predicates of form lhs <op> rhs which are applied on rows selected by the where clause. During intermediate sketch aggregation, sketches from the thetaSketchColumn that satisfies these predicates are unionized individually. For example, all filtered rows that match country=USA are unionized into a single sketch. Complex predicates that are created by combining (AND/OR) of individual predicates is supported.

• postAggregationExpressionToEvaluate (required): The set operation to perform on the individual intermediate sketches for each of the predicates. Currently supported operations are SET_DIFF, SET_UNION, SET_INTERSECT , where DIFF requires two arguments and the UNION/INTERSECT allow more than two arguments.

Usage Examples

These examples are based on the Batch Quick Start.

select distinctCountThetaSketch(teamID) AS value
from baseballStats 

select distinctCountThetaSketch(teamID, 'nominalEntries=10') AS value
from baseballStats

We can also provide predicates and a post aggregation expression to compute more complicated cardinalities. For example, we could can find the intersection of the following queries:

select yearID
from baseballStats
where teamID = 'SFN' AND numberOfGames = 28 AND homeRuns = 1

select yearID
from baseballStats
where teamID = 'CHN' AND numberOfGames = 28 AND homeRuns = 1

(the yearId 1986 is the only one in common)

By running the following query:

select distinctCountThetaSketch(
  yearID, 
  'nominalEntries=4096', 
  'teamID = ''SFN'' AND numberOfGames=28 AND homeRuns=1',
  'teamID = ''CHN'' AND numberOfGames=28 AND homeRuns=1',
  'SET_INTERSECT($1, $2)'
) AS value
from baseballStats 

(Presto) SQL compatible date truncation, equivalent to the Presto function date_trunc.

Converts the value into a specified output granularity seconds since UTC epoch that is bucketed on a unit in a specified timezone.

Signature

DATETRUNC(unit, timeValue)

DATETRUNC(unit, timeValue, inputTimeUnitStr)

DATETRUNC(unit, timeValue, inputTimeUnitStr, timeZone)

DATETRUNC(unit, timeValue, inputTimeUnitStr, timeZone, outputTimeUnitStr)

unit supports the following values:

• millisecond

• second

• minute

• hour

• day

• week

• month

• quarter

• year

inputTimeUnitStr and outputTimeUnitStr support the following values:

• NANOSECONDS

• MICROSECONDS

• MILLISECONDS

• SECONDS

• MINUTES

• HOURS

• DAYS

Usage Examples

Truncates an epoch in milliseconds at WEEK (where a Week starts at Monday UTC midnight):

or

Truncates an epoch in milliseconds at WEEK (where a Week starts at Monday UTC midnight) in the UTC time zone, returning a result in epoch in seconds in UTC timezone:

Truncates an epoch in milliseconds at WEEK (where a Week starts at Monday UTC midnight) in the CET time zone, returning a result in epoch in seconds in UTC timezone:

Truncates an epoch in milliseconds at QUARTER in the Los Angeles time zone (where a Quarter begins on Jan 1st, April 1st, July 1st, October 1st in Los Angeles timezone), returning a result in hours since UTC epoch:

Euler’s number(e) raised to the power of col.

Signature

EXP(col1)

Usage Examples

Converts a formatted date-time string to milliseconds, based on the provided .

Signature

FromDateTime(dateTimeString, pattern)

Usage Examples

Returns the hour of the day from the given epoch millis in UTC or specified timezone. The value ranges from 0 to 23.

Signature

hour(tsInMillis)

hour(tsInMillis, timeZoneId)

Usage Examples

Convert epoch to epoch milliseconds. The following time units are supported:

• SECONDS

• MINUTES

• HOURS

• DAYS

Signature

FromEpoch<TIME_UNIT>(timeIn<Time_UNIT>)

Usage Examples

Converts the value from a column that contains an epoch timestamp into another time unit and buckets based on the given time granularity.

Signature

DATETIMECONVERT(columnName, inputFormat, outputFormat, outputGranularity)

inputFormat and outputFormat are defined using the following structure:

<time size>:<time unit>:<time format>:<pattern>

where:

• time size - size of the time unit eg: 1, 10

• time unit - DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, MICROSECONDS, NANOSECONDS

• time format

  • EPOCH

  • SIMPLE_DATE_FORMAT pattern - defined in case of SIMPLE_DATE_FORMAT e.g. yyyy-MM-dd. A specific timezone can be passed using tz(timezone). Timezone can be long or short string format timezone. e.g. Asia/Kolkata or PDT

granularity is specified in the format <time size>:<time unit>.

Usage Examples

These examples are based on the Batch JSON Quick Start.

created_at_timestamp from milliseconds since epoch to days since epoch, bucketed to 1 day granularity:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:DAYS:EPOCH', 
         '1:DAYS'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

created_at_timestamp bucketed to 15 minutes granularity:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:MILLISECONDS:EPOCH', 
         '15:MINUTES'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

created_at_timestamp to format yyyy-MM-dd, bucketed to 1 days granularity:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd', 
         '1:DAYS'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

created_at_timestamp to format yyyy-MM-dd HH:mm, in timezone Pacific/Kiritimati:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm tz(Pacific/Kiritimati)', 
         '1:MILLISECONDS'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

created_at_timestamp to format yyyy-MM-dd, in timezone Pacific/Kiritimati and bucketed to 1 day granularity:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm tz(Pacific/Kiritimati)', 
         '1:DAYS'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

Returns HLL response serialized as string. The serialized HLL can be converted back into an HLL and then aggregated with other HLLs. A common use case may be to merge HLL responses from different Pinot tables, or to allow aggregation after client-side batching.

Signature

DISTINCTCOUNTRAWHLL(colName, log2m)

Usage Examples

These examples are based on the Batch Quick Start.

select DISTINCTCOUNTHLL(teamID) AS value
from baseballStats 

select DISTINCTCOUNTRAWHLL(teamID, 1) AS value
from baseballStats 

Extracts the object value from jsonField based on 'jsonPath', the result type is inferred based on JSON value. This function can only be used in an ingestion transformation function.

Signature

JSONPATH(jsonField, 'jsonPath')

Usage Examples

The usage examples are based on extracting fields from the following JSON document:

{
  "data": {
    "name": "Pete",
    "age": 24,
    "subjects": [
      {
        "name": "maths",
        "homework_grades": [80, 85, 90, 95, 100],
        "grade": "A",
        "score": 90
      },
      {
        "name": "english",
        "homework_grades": [60, 65, 70, 85, 90],
        "grade": "B",
        "score": 70
      }
    ]
  }
}

This function can be used in the table config to extract the name property into the name column and age property into the age column, as described below:

{
   "tableConfig":{
      "ingestionConfig":{
         "transformConfigs":[
            {
               "columnName":"name",
               "transformFunction":"JSONPATHSTRING(data, '$.name')"
            },
            {
               "columnName":"age",
               "transformFunction":"JSONPATHSTRING(data, '$.age')"
            }
         ]
      }
   }
}

Extracts an array from jsonField based on 'jsonPath', the result type is inferred based on JSON value. This function can only be used in an ingestion transformation function.

Signature

JSONPATHARRAY(jsonField, 'jsonPath')

Usage Examples

The usage examples are based on extracting fields from the following JSON document:

{
  "data": {
    "name": "Pete",
    "age": 24,
    "subjects": [
      {
        "name": "maths",
        "homework_grades": [80, 85, 90, 95, 100],
        "grade": "A",
        "score": 90
      },
      {
        "name": "english",
        "homework_grades": [60, 65, 70, 85, 90],
        "grade": "B",
        "score": 70
      }
    ]
  }
}

This function can be used in the table config to extract the name, score, and second value of homework_grades into their respective columns , as described below:

{
   "tableConfig":{
      "ingestionConfig":{
         "transformConfigs":[
            {
               "columnName":"names",
               "transformFunction":"JSONPATHARRAY(data, '$.subjects[*].name')"
            },
            {
               "columnName":"ages",
               "transformFunction":"JSONPATHARRAY(data, '$.subjects[*].score')"
            },
            {
               "columnName":"homeworkGrades",
               "transformFunction":"JSONPATHARRAY(data, '$.subjects[*].homework_grades[1]')"
            }
         ]
      }
   }
}

Extracts an array from jsonField based on 'jsonPath', the result type is inferred based on JSON value. Returns empty array for null or parsing error. This function can only be used in an ingestion transformation function.

Signature

JSONPATHARRAYDEFAULTEMPTY(jsonField, 'jsonPath')

Usage Examples

The usage examples are based on extracting fields from the following JSON document:

{
  "data": {
    "name": "Pete",
    "age": 24,
    "subjects": [
      {
        "name": "maths",
        "homework_grades": [80, 85, 90, 95, 100],
        "grade": "A",
        "score": 90
      },
      {
        "name": "english",
        "homework_grades": [60, 65, 70, 85, 90],
        "grade": "B",
        "score": 70
      }
    ]
  }
}

This function can be used in the table config to extract the name, score, and second value of homework_grades into their respective columns , as described below:

{
   "tableConfig":{
      "ingestionConfig":{
         "transformConfigs":[
            {
               "columnName":"names",
               "transformFunction":"JSONPATHARRAYDEFAULTEMPTY(data, '$.subjects[*].name')"
            },
            {
               "columnName":"ages",
               "transformFunction":"JSONPATHARRAYDEFAULTEMPTY(data, '$.subjects[*].score')"
            },
            {
               "columnName":"homeworkGrades",
               "transformFunction":"JSONPATHARRAYDEFAULTEMPTY(data, '$.subjects[*].homework_grades[1]')"
            }
         ]
      }
   }
}

This page contains reference documentation for functions in Apache Pinot.

Convert epoch to epoch milliseconds. e.g. 10 seconds since epoch or 5 minutes since Epoch. The following time units are supported:

• SECONDS

• MINUTES

• HOURS

• DAYS