LogoLogo
release-1.3.0
release-1.3.0
  • Introduction
  • Basics
    • Concepts
      • Pinot storage model
      • Architecture
      • Components
        • Cluster
          • Tenant
          • Server
          • Controller
          • Broker
          • Minion
        • Table
          • Segment
            • Deep Store
            • Segment threshold
            • Segment retention
          • Schema
          • Time boundary
        • Pinot Data Explorer
    • Getting Started
      • Running Pinot locally
      • Running Pinot in Docker
      • Quick Start Examples
      • Running in Kubernetes
      • Running on public clouds
        • Running on Azure
        • Running on GCP
        • Running on AWS
      • Create and update a table configuration
      • Batch import example
      • Stream ingestion example
      • HDFS as Deep Storage
      • Troubleshooting Pinot
      • Frequently Asked Questions (FAQs)
        • General
        • Pinot On Kubernetes FAQ
        • Ingestion FAQ
        • Query FAQ
        • Operations FAQ
    • Import Data
      • From Query Console
      • Batch Ingestion
        • Spark
        • Flink
        • Hadoop
        • Backfill Data
        • Dimension table
      • Stream ingestion
        • Ingest streaming data from Apache Kafka
        • Ingest streaming data from Amazon Kinesis
        • Ingest streaming data from Apache Pulsar
        • Configure indexes
      • Stream ingestion with Upsert
      • Segment compaction on upserts
      • Stream ingestion with Dedup
      • Stream ingestion with CLP
      • File Systems
        • Amazon S3
        • Azure Data Lake Storage
        • HDFS
        • Google Cloud Storage
      • Input formats
        • Complex Type (Array, Map) Handling
        • Complex Type Examples
        • Ingest records with dynamic schemas
      • Reload a table segment
      • Upload a table segment
    • Indexing
      • Bloom filter
      • Dictionary index
      • Forward index
      • FST index
      • Geospatial
      • Inverted index
      • JSON index
      • Native text index
      • Range index
      • Star-tree index
      • Text search support
      • Timestamp index
      • Vector index
    • Release notes
      • 1.3.0
      • 1.2.0
      • 1.1.0
      • 1.0.0
      • 0.12.1
      • 0.12.0
      • 0.11.0
      • 0.10.0
      • 0.9.3
      • 0.9.2
      • 0.9.1
      • 0.9.0
      • 0.8.0
      • 0.7.1
      • 0.6.0
      • 0.5.0
      • 0.4.0
      • 0.3.0
      • 0.2.0
      • 0.1.0
    • Recipes
      • Connect to Streamlit
      • Connect to Dash
      • Visualize data with Redash
      • GitHub Events Stream
  • For Users
    • Query
      • Querying Pinot
      • Query Syntax
        • Aggregation Functions
        • Array Functions
        • Cardinality Estimation
        • Explain Plan (Single-Stage)
        • Filtering with IdSet
        • Funnel Analysis
        • GapFill Function For Time-Series Dataset
        • Grouping Algorithm
        • Hash Functions
        • JOINs
        • Lookup UDF Join
        • Querying JSON data
        • Transformation Functions
        • URL Functions
        • Window Functions
      • Query Options
      • Query Quotas
      • Query using Cursors
      • Multi-stage query
        • Understanding Stages
        • Stats
        • Optimizing joins
        • Join strategies
          • Random + broadcast join strategy
          • Query time partition join strategy
          • Colocated join strategy
          • Lookup join strategy
        • Hints
        • Operator Types
          • Aggregate
          • Filter
          • Join
          • Intersect
          • Leaf
          • Literal
          • Mailbox receive
          • Mailbox send
          • Minus
          • Sort or limit
          • Transform
          • Union
          • Window
        • Stage-Level Spooling
      • User-Defined Functions (UDFs)
      • Explain plan
    • APIs
      • Broker Query API
        • Query Response Format
      • Controller Admin API
      • Controller API Reference
    • External Clients
      • JDBC
      • Java
      • Python
      • Golang
    • Tutorials
      • Use OSS as Deep Storage for Pinot
      • Ingest Parquet Files from S3 Using Spark
      • Creating Pinot Segments
      • Use S3 as Deep Storage for Pinot
      • Use S3 and Pinot in Docker
      • Batch Data Ingestion In Practice
      • Schema Evolution
  • For Developers
    • Basics
      • Extending Pinot
        • Writing Custom Aggregation Function
        • Segment Fetchers
      • Contribution Guidelines
      • Code Setup
      • Code Modules and Organization
      • Dependency Management
      • Update documentation
    • Advanced
      • Data Ingestion Overview
      • Ingestion Aggregations
      • Ingestion Transformations
      • Null value support
      • Use the multi-stage query engine (v2)
      • Advanced Pinot Setup
    • Plugins
      • Write Custom Plugins
        • Input Format Plugin
        • Filesystem Plugin
        • Batch Segment Fetcher Plugin
        • Stream Ingestion Plugin
    • Design Documents
      • Segment Writer API
  • For Operators
    • Deployment and Monitoring
      • Set up cluster
      • Server Startup Status Checkers
      • Set up table
      • Set up ingestion
      • Decoupling Controller from the Data Path
      • Segment Assignment
      • Instance Assignment
      • Rebalance
        • Rebalance Servers
        • Rebalance Brokers
        • Rebalance Tenant
      • Separating data storage by age
        • Using multiple tenants
        • Using multiple directories
      • Pinot managed Offline flows
      • Minion merge rollup task
      • Consistent Push and Rollback
      • Access Control
      • Monitoring
      • Tuning
        • Tuning Default MMAP Advice
        • Real-time
        • Routing
        • Query Routing using Adaptive Server Selection
        • Query Scheduling
      • Upgrading Pinot with confidence
      • Managing Logs
      • OOM Protection Using Automatic Query Killing
      • Pause ingestion based on resource utilization
    • Command-Line Interface (CLI)
    • Configuration Recommendation Engine
    • Tutorials
      • Authentication
        • Basic auth access control
        • ZkBasicAuthAccessControl
      • Configuring TLS/SSL
      • Build Docker Images
      • Running Pinot in Production
      • Kubernetes Deployment
      • Amazon EKS (Kafka)
      • Amazon MSK (Kafka)
      • Monitor Pinot using Prometheus and Grafana
      • Performance Optimization Configurations
      • Segment Operations Throttling
  • Configuration Reference
    • Cluster
    • Controller
    • Broker
    • Server
    • Table
    • Ingestion
    • Schema
    • Ingestion Job Spec
    • Monitoring Metrics
    • Functions
      • ABS
      • ADD
      • ago
      • EXPR_MIN / EXPR_MAX
      • ARRAY_AGG
      • arrayConcatDouble
      • arrayConcatFloat
      • arrayConcatInt
      • arrayConcatLong
      • arrayConcatString
      • arrayContainsInt
      • arrayContainsString
      • arrayDistinctInt
      • arrayDistinctString
      • arrayIndexOfInt
      • arrayIndexOfString
      • ARRAYLENGTH
      • arrayRemoveInt
      • arrayRemoveString
      • arrayReverseInt
      • arrayReverseString
      • arraySliceInt
      • arraySliceString
      • arraySortInt
      • arraySortString
      • arrayUnionInt
      • arrayUnionString
      • AVGMV
      • Base64
      • caseWhen
      • ceil
      • CHR
      • codepoint
      • concat
      • count
      • COUNTMV
      • COVAR_POP
      • COVAR_SAMP
      • day
      • dayOfWeek
      • dayOfYear
      • DISTINCT
      • DISTINCTAVG
      • DISTINCTAVGMV
      • DISTINCTCOUNT
      • DISTINCTCOUNTBITMAP
      • DISTINCTCOUNTBITMAPMV
      • DISTINCTCOUNTHLL
      • DISTINCTCOUNTSMARTHLL
      • DISTINCTCOUNTHLLPLUS
      • DISTINCTCOUNTHLLMV
      • DISTINCTCOUNTMV
      • DISTINCTCOUNTRAWHLL
      • DISTINCTCOUNTRAWHLLMV
      • DISTINCTCOUNTRAWTHETASKETCH
      • DISTINCTCOUNTTHETASKETCH
      • DISTINCTCOUNTULL
      • DISTINCTSUM
      • DISTINCTSUMMV
      • DIV
      • DATETIMECONVERT
      • DATETRUNC
      • exp
      • FIRSTWITHTIME
      • FLOOR
      • FrequentLongsSketch
      • FrequentStringsSketch
      • FromDateTime
      • FromEpoch
      • FromEpochBucket
      • FUNNELCOUNT
      • FunnelCompleteCount
      • FunnelMaxStep
      • FunnelMatchStep
      • Histogram
      • hour
      • isSubnetOf
      • JSONFORMAT
      • JSONPATH
      • JSONPATHARRAY
      • JSONPATHARRAYDEFAULTEMPTY
      • JSONPATHDOUBLE
      • JSONPATHLONG
      • JSONPATHSTRING
      • jsonextractkey
      • jsonextractscalar
      • LAG
      • LASTWITHTIME
      • LEAD
      • length
      • ln
      • lower
      • lpad
      • ltrim
      • max
      • MAXMV
      • MD5
      • millisecond
      • min
      • minmaxrange
      • MINMAXRANGEMV
      • MINMV
      • minute
      • MOD
      • mode
      • month
      • mult
      • now
      • percentile
      • percentileest
      • percentileestmv
      • percentilemv
      • percentiletdigest
      • percentiletdigestmv
      • percentilekll
      • percentilerawkll
      • percentilekllmv
      • percentilerawkllmv
      • quarter
      • regexpExtract
      • regexpReplace
      • remove
      • replace
      • reverse
      • round
      • roundDecimal
      • ROW_NUMBER
      • rpad
      • rtrim
      • second
      • SEGMENTPARTITIONEDDISTINCTCOUNT
      • sha
      • sha256
      • sha512
      • sqrt
      • startswith
      • ST_AsBinary
      • ST_AsText
      • ST_Contains
      • ST_Distance
      • ST_GeogFromText
      • ST_GeogFromWKB
      • ST_GeometryType
      • ST_GeomFromText
      • ST_GeomFromWKB
      • STPOINT
      • ST_Polygon
      • strpos
      • ST_Union
      • SUB
      • substr
      • sum
      • summv
      • TIMECONVERT
      • timezoneHour
      • timezoneMinute
      • ToDateTime
      • ToEpoch
      • ToEpochBucket
      • ToEpochRounded
      • TOJSONMAPSTR
      • toGeometry
      • toSphericalGeography
      • trim
      • upper
      • Url
      • UTF8
      • VALUEIN
      • week
      • year
      • Extract
      • yearOfWeek
      • FIRST_VALUE
      • LAST_VALUE
      • ST_GeomFromGeoJSON
      • ST_GeogFromGeoJSON
      • ST_AsGeoJSON
    • Plugin Reference
      • Stream Ingestion Connectors
      • VAR_POP
      • VAR_SAMP
      • STDDEV_POP
      • STDDEV_SAMP
    • Dynamic Environment
  • Reference
    • Single-stage query engine (v1)
    • Multi-stage query engine (v2)
    • Troubleshooting
      • Troubleshoot issues with the multi-stage query engine (v2)
      • Troubleshoot issues with ZooKeeper znodes
  • RESOURCES
    • Community
    • Team
    • Blogs
    • Presentations
    • Videos
  • Integrations
    • Tableau
    • Trino
    • ThirdEye
    • Superset
    • Presto
    • Spark-Pinot Connector
  • Contributing
    • Contribute Pinot documentation
    • Style guide
Powered by GitBook
On this page
  • Array Reversal
  • arrayReverseInt
  • arrayReverseString
  • Array Sorting
  • arraySortInt
  • arraySortString
  • Array Index Operations
  • arrayIndexOfInt
  • arrayIndexOfString
  • arrayIndexesOfInt
  • arrayIndexesOfLong
  • arrayIndexesOfFloat
  • arrayIndexesOfDouble
  • arrayIndexesOfString
  • Array Intersection
  • intersectIndices
  • Array Contains
  • arrayContainsInt
  • arrayContainsString
  • Array Slicing
  • arraySliceInt
  • arraySliceString
  • Array Distinct
  • arrayDistinctInt
  • arrayDistinctString
  • Array Remove
  • arrayRemoveInt
  • arrayRemoveString
  • Array Union
  • arrayUnionInt
  • arrayUnionString
  • Array Concatenation
  • arrayConcatInt
  • arrayConcatLong
  • arrayConcatFloat
  • arrayConcatDouble
  • arrayConcatString
  • Array Element Access
  • arrayElementAtInt
  • arrayElementAtLong
  • arrayElementAtFloat
  • arrayElementAtDouble
  • arrayElementAtString
  • Array Summation
  • arraySumInt
  • arraySumLong
  • Array Construction
  • arrayValueConstructor
  • Array Generation
  • generateIntArray
  • generateLongArray
  • generateFloatArray
  • generateDoubleArray
  • String Conversion
  • arrayToString (2-argument)
  • arrayToString (3-argument)

Was this helpful?

Export as PDF
  1. For Users
  2. Query
  3. Query Syntax

Array Functions


Array Reversal

arrayReverseInt

Description: Reverses the order of elements in an integer array. Syntax: arrayReverseInt(int_array) Example:

SELECT arrayReverseInt(ARRAY[1, 2, 3, 4, 5]);
-- Result: [5, 4, 3, 2, 1]

arrayReverseString

Description: Reverses the order of elements in a string array. Syntax: arrayReverseString(string_array) Example:

SELECT arrayReverseString(ARRAY['apple', 'banana', 'cherry']);
-- Result: ['cherry', 'banana', 'apple']

Array Sorting

arraySortInt

Description: Sorts an integer array in ascending order. Syntax: arraySortInt(int_array) Example:

SELECT arraySortInt(ARRAY[4, 1, 3, 5, 2]);
-- Result: [1, 2, 3, 4, 5]

arraySortString

Description: Sorts a string array lexicographically. Syntax: arraySortString(string_array) Example:

SELECT arraySortString(ARRAY['banana', 'apple', 'cherry']);
-- Result: ['apple', 'banana', 'cherry']

Array Index Operations

arrayIndexOfInt

Description: Returns the first 0-based index of an integer value. Syntax: arrayIndexOfInt(int_array, value) Example:

SELECT arrayIndexOfInt(ARRAY[10, 20, 3, 40], 3);
-- Result: 2

arrayIndexOfString

Description: Returns the first 0-based index of a string value. Syntax: arrayIndexOfString(string_array, value) Example:

SELECT arrayIndexOfString(ARRAY['apple', 'banana', 'cherry'], 'cherry');
-- Result: 2

arrayIndexesOfInt

Description: Returns all indices of an integer value. Syntax: arrayIndexesOfInt(int_array, value) Example:

SELECT arrayIndexesOfInt(ARRAY[5, 3, 5, 2, 5], 5);
-- Result: [0, 2, 4]

arrayIndexesOfLong

Description: Returns all indices of a long value. Syntax: arrayIndexesOfLong(long_array, value) Example:

SELECT arrayIndexesOfLong(ARRAY[5000000000, 3000000000, 5000000000], 5000000000);
-- Result: [0, 2]

arrayIndexesOfFloat

Description: Returns all indices of a float value. Syntax: arrayIndexesOfFloat(float_array, value) Example:

SELECT arrayIndexesOfFloat(ARRAY[1.5, 3.0, 1.5], 1.5);
-- Result: [0, 2]

arrayIndexesOfDouble

Description: Returns all indices of a double value. Syntax: arrayIndexesOfDouble(double_array, value) Example:

SELECT arrayIndexesOfDouble(ARRAY[123.456, 789.012, 123.456], 123.456);
-- Result: [0, 2]

arrayIndexesOfString

Description: Returns all indices of a string value. Syntax: arrayIndexesOfString(string_array, value) Example:

SELECT arrayIndexesOfString(ARRAY['a', 'b', 'a'], 'a');
-- Result: [0, 2]

Array Intersection

intersectIndices

Description: Returns common indices between two sorted integer arrays. Syntax: intersectIndices(array1, array2) Example:

SELECT intersectIndices(ARRAY[1, 3, 5], ARRAY[3, 5]);
-- Result: [3, 5]

Array Contains

arrayContainsInt

Description: Checks if an integer array contains a value. Syntax: arrayContainsInt(int_array, value) Example:

SELECT arrayContainsInt(ARRAY[3, 7, 9], 7);
-- Result: true

arrayContainsString

Description: Checks if a string array contains a value. Syntax: arrayContainsString(string_array, value) Example:

SELECT arrayContainsString(ARRAY['apple', 'banana'], 'apple');
-- Result: true

Array Slicing

arraySliceInt

Description: Extracts a subarray (start inclusive, end exclusive). Syntax: arraySliceInt(int_array, start, end) Example:

SELECT arraySliceInt(ARRAY[10, 20, 30, 40], 1, 3);
-- Result: [20, 30]

arraySliceString

Description: Extracts a string subarray. Syntax: arraySliceString(string_array, start, end) Example:

SELECT arraySliceString(ARRAY['a', 'b', 'c', 'd'], 0, 2);
-- Result: ['a', 'b']

Array Distinct

arrayDistinctInt

Description: Removes duplicate integers. Syntax: arrayDistinctInt(int_array) Example:

SELECT arrayDistinctInt(ARRAY[1, 2, 2, 3, 1]);
-- Result: [1, 2, 3]

arrayDistinctString

Description: Removes duplicate strings. Syntax: arrayDistinctString(string_array) Example:

SELECT arrayDistinctString(ARRAY['apple', 'banana', 'apple']);
-- Result: ['apple', 'banana']

Array Remove

arrayRemoveInt

Description: Removes the first occurrence of an integer. Syntax: arrayRemoveInt(int_array, value) Example:

SELECT arrayRemoveInt(ARRAY[2, 4, 2, 6], 2);
-- Result: [4, 2, 6]

arrayRemoveString

Description: Removes the first occurrence of a string. Syntax: arrayRemoveString(string_array, value) Example:

SELECT arrayRemoveString(ARRAY['apple', 'banana', 'cherry'], 'banana');
-- Result: ['apple', 'cherry']

Array Union

arrayUnionInt

Description: Combines two integer arrays with unique values. Syntax: arrayUnionInt(array1, array2) Example:

SELECT arrayUnionInt(ARRAY[1, 2], ARRAY[2, 3]);
-- Result: [1, 2, 3]

arrayUnionString

Description: Combines two string arrays with unique values. Syntax: arrayUnionString(array1, array2) Example:

SELECT arrayUnionString(ARRAY['a', 'b'], ARRAY['b', 'c']);
-- Result: ['a', 'b', 'c']

Array Concatenation

arrayConcatInt

Description: Concatenates two integer arrays. Syntax: arrayConcatInt(array1, array2) Example:

SELECT arrayConcatInt(ARRAY[1, 2], ARRAY[3, 4]);
-- Result: [1, 2, 3, 4]

arrayConcatLong

Description: Concatenates two long arrays. Syntax: arrayConcatLong(array1, array2) Example:

SELECT arrayConcatLong(ARRAY[1000000000, 2000000000], ARRAY[3000000000]);
-- Result: [1000000000, 2000000000, 3000000000]

arrayConcatFloat

Description: Concatenates two float arrays. Syntax: arrayConcatFloat(array1, array2) Example:

SELECT arrayConcatFloat(ARRAY[1.5, 2.0], ARRAY[3.5]);
-- Result: [1.5, 2.0, 3.5]

arrayConcatDouble

Description: Concatenates two double arrays. Syntax: arrayConcatDouble(array1, array2) Example:

SELECT arrayConcatDouble(ARRAY[123.456], ARRAY[789.012]);
-- Result: [123.456, 789.012]

arrayConcatString

Description: Concatenates two string arrays. Syntax: arrayConcatString(array1, array2) Example:

SELECT arrayConcatString(ARRAY['a', 'b'], ARRAY['c']);
-- Result: ['a', 'b', 'c']

Array Element Access

arrayElementAtInt

Description: Returns the 1-indexed integer element. Syntax: arrayElementAtInt(array, index) Example:

SELECT arrayElementAtInt(ARRAY[10, 20, 30], 2);
-- Result: 20

arrayElementAtLong

Description: Returns the 1-indexed long element. Syntax: arrayElementAtLong(array, index) Example:

SELECT arrayElementAtLong(ARRAY[1000000000, 2000000000], 1);
-- Result: 1000000000

arrayElementAtFloat

Description: Returns the 1-indexed float element. Syntax: arrayElementAtFloat(array, index) Example:

SELECT arrayElementAtFloat(ARRAY[1.5, 2.0], 2);
-- Result: 2.0

arrayElementAtDouble

Description: Returns the 1-indexed double element. Syntax: arrayElementAtDouble(array, index) Example:

SELECT arrayElementAtDouble(ARRAY[123.456, 789.012], 1);
-- Result: 123.456

arrayElementAtString

Description: Returns the 1-indexed string element. Syntax: arrayElementAtString(array, index) Example:

SELECT arrayElementAtString(ARRAY['alpha', 'beta', 'gamma'], 1);
-- Result: 'alpha'

Array Summation

arraySumInt

Description: Sums all integers in an array. Syntax: arraySumInt(int_array) Example:

SELECT arraySumInt(ARRAY[1, 2, 3, 4]);
-- Result: 10

arraySumLong

Description: Sums all longs in an array. Syntax: arraySumLong(long_array) Example:

SELECT arraySumLong(ARRAY[1000000000, 2000000000]);
-- Result: 3000000000

Array Construction

arrayValueConstructor

Description: Constructs an array from elements. Syntax: array(element1, element2, ...) Example:

SELECT array(1, 2, 3) AS int_array, array('a', 'b') AS str_array;
-- Result: 
-- int_array = [1, 2, 3]
-- str_array = ['a', 'b']

Array Generation

generateIntArray

Description: Generates an integer sequence. Syntax: generateIntArray(start, end, increment) Example:

SELECT generateIntArray(1, 5, 2);
-- Result: [1, 3, 5]

generateLongArray

Description: Generates a long sequence. Syntax: generateLongArray(start, end, increment) Example:

SELECT generateLongArray(100, 300, 100);
-- Result: [100, 200, 300]

generateFloatArray

Description: Generates a float sequence. Syntax: generateFloatArray(start, end, increment) Example:

SELECT generateFloatArray(0.5, 2.0, 0.5);
-- Result: [0.5, 1.0, 1.5, 2.0]

generateDoubleArray

Description: Generates a double sequence. Syntax: generateDoubleArray(start, end, increment) Example:

SELECT generateDoubleArray(1.0, 2.5, 0.5);
-- Result: [1.0, 1.5, 2.0, 2.5]

String Conversion

arrayToString (2-argument)

Description: Joins elements with a delimiter. Syntax: arrayToString(string_array, delimiter) Example:

SELECT arrayToString(ARRAY['red', 'green', 'blue'], ',');
-- Result: 'red,green,blue'

arrayToString (3-argument)

Description: Joins elements with null replacement. Syntax: arrayToString(string_array, delimiter, nullString) Example:

SELECT arrayToString(ARRAY['foo', NULL, 'bar'], '|', 'NA');
-- Result: 'foo|NA|bar'

PreviousAggregation FunctionsNextCardinality Estimation

Was this helpful?