tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
Cloud Log Service
    Documentation
    Download PDF

    Array Functions

    Last updated: 2024-01-22 10:57:48
    Download PDF
      This document introduces the basic syntax and examples of array functions.
      Function
      Syntax
      Description
      [x]
      Returns an element from an array. Equivalent to the `element_at` function.
      array_agg(x)
      Returns all values in `x` as an array.
      array_distinct(x)
      Deduplicates an array and returns unique values from the array.
      array_except(x, y)
      Returns the difference between the `x` and `y` arrays.
      array_intersect(x, y)
      Returns the intersection between the `x` and `y` arrays.
      array_join(x, delimiter)
      Concatenates the elements in an array using the specified delimiter. If the array contains a null element, the null element is ignored.Note: For the `array_join` function, the maximum return result supported is 1 KB, and data exceeding 1 KB will be truncated.
      array_join(x, delimiter, null_replacement)
      Concatenates the elements in an array using `delimiter` and uses `null_replacement` to replace null values.Note: For the `array_join` function, the maximum return result supported is 1 KB, and data exceeding 1 KB will be truncated.
      array_max(x)
      Returns the maximum value of an array.
      array_max(x)
      Returns the minimum value of an array.
      array_position(x, element)
      Returns the subscript (starting from 1) of a specified element. If the specified element does not exist, return `0`.
      array_remove(x, element)
      Removes a specified element from an array.
      array_sort(x)
      Sorts elements in an array in ascending order. If there are null elements, the null elements will be placed at the end of the returned array.
      array_union(x, y)
      Returns the union of two arrays.
      cardinality(x)
      Calculates the number of elements in an array.
      concat(x, y...)
      Concatenates multiple arrays into one.
      contains(x, element)
      Determines whether an array contains a specified element and returns `true` if the array contains the element.
      element_at(x, y)
      Returns the yth element of an array.
      filter(x, lambda_expression)
      Filters elements in an array and returns only the elements that comply with the Lambda expression.
      flatten(x)
      Converts a two-dimensional array to a one-dimensional array.
      reduce(x, lambda_expression)
      Adds the elements in the array as defined by the Lambda expression and returns the result.
      reverse(x)
      Reverses the elements in an array.
      sequence(x, y)
      Returns an array of consecutive and increasing values within the specified starting value range. The increment interval is the default value 1.
      sequence(x, y, step)
      Returns an array of consecutive and increasing values within the specified starting value range. The increment interval is `step`.
      shuffle(x)
      Randomizes the elements in an array.
      slice(x, start, length)
      Returns a subset of an array.
      transform(x, lambda_expression)
      Applies a Lambda expression to each element of an array.
      zip(x, y)
      Combines multiple arrays into a two-dimensional array (elements with the same subscript in each original array form a new array).
      zip_with(x, y, lambda_expression)
      Merges two arrays into one as defined by a Lambda expression.

      Subscript Operator []

      The subscript operator ([]) is used to return the xth element in an array. It is equivalent to the element_at function.

      Syntax

      [x]

      Parameter description

      Parameter
      Description
      x
      Array subscript, starting from 1. The parameter value is of the bigint type.

      Returned value type

      Return the data type of the specified element.

      Sample

      Return the second element of the number field value.
      Field sample
      array:[12,23,26,48,26]
      Search and analysis statement
      * | SELECT cast(json_parse(array) as array(bigint)) [2]
      Search and analysis result
      23

      array_agg

      The array_agg function is used to return all values in x as an array.

      Syntax

      array_agg(x)

      Parameter description

      Parameter
      Description
      x
      The parameter value can be of any data type.

      Returned value type

      Array

      Sample

      Return the values of the status field as an array.
      Search and analysis statement
      * | SELECT array_agg(status) AS array
      Search and analysis result
      [200,200,200,404,200,200]

      array_distinct

      The array_distinct function is used to delete duplicate elements from an array.

      Syntax

      array_distinct(x)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.

      Returned value type

      Array

      Sample

      Delete duplicate elements from the array field.
      Field sample
      array:[12,23,26,48,26]
      Search and analysis statement
      * | SELECT array_distinct(cast(json_parse(array) as array(bigint)))
      Search and analysis result
      [12,23,26,48]

      array_except

      The array_except function is used to calculate the difference between two arrays.

      Syntax

      array_except(x, y)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.
      y
      The parameter value is of the array type.

      Returned value type

      Array

      Sample

      Calculate the difference between arrays [1,2,3,4,5] and [1,3,5,7].
      Search and analysis statement
      * | SELECT array_except(array[1,2,3,4,5],array[1,3,5,7])
      Search and analysis result
      [2,4]

      array_intersect

      The array_intersect function is used to calculate the intersection between two arrays.

      Syntax

      array_intersect(x, y)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.
      y
      The parameter value is of the array type.

      Returned value type

      Array

      Sample

      Calculate the difference between arrays [1,2,3,4,5] and [1,3,5,7].
      Search and analysis statement
      * | SELECT array_intersect(array[1,2,3,4,5],array[1,3,5,7])
      Search and analysis result
      [1,3,5]

      array_join

      The array_join function is used to concatenate the elements in an array into a string using the specified delimiter.

      Syntax

      Concatenate the elements in an array into a string using the specified delimiter. If the array contains null elements, the null elements are ignored.
      array_join(x, delimiter)
      Concatenate the elements in an array into a string using the specified delimiter. If the array contains null elements, the null elements are replaced with null_replacement.
      array_join(x, delimiter, null_replacement)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.
      delimiter
      Connector, which can be a string.
      null_replacement
      String used to replace a null element.

      Returned value type

      Varchar

      Sample

      Use spaces to concatenate the elements in the [null,'China','sh'] array as a string, and replace the null element with region.
      Search and analysis statement
      * | SELECT array_join(array[null,'China','sh'],'/','region')
      Search and analysis result
      region/China/sh

      array_max

      The array_max function is used to get the maximum value of an array.

      Syntax

      array_max(x)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.

      Returned value type

      Same as the element data type of the parameter value.

      Sample

      Get the maximum value 48 from the [12,23,26,48,26] array.
      Field sample
      array:[12,23,26,48,26]
      Search and analysis statement
      * | SELECT array_max(try_cast(json_parse(array) as array(bigint))) AS max_number
      Search and analysis result
      48

      array_min

      The array_min function is used to get the minimum value of an array.

      Syntax

      array_min(x)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.

      Returned value type

      Same as the element data type of the parameter value.

      Sample

      Get the minimum value 12 from the [12,23,26,48,26] array.
      Field sample
      array:[12,23,26,48,26]
      Search and analysis statement
      * | SELECT array_min(try_cast(json_parse(array) as array(bigint))) AS min_number
      Search and analysis result
      12

      array_position

      The array_position function is used to get the subscript (starting from 1) of a specified element. If the specified element does not exist, return 0.

      Syntax

      array_position(x, element)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.
      element
      Element in an array.

      Returned value type

      Bigint

      Sample

      Return the subscript of 46 in the [23,46,35] array.
      Search and analysis statement
      * | SELECT array_position(array[23,46,35],46)
      Search and analysis result
      2

      array_remove

      The array_remove function is used to delete a specified element from an array.

      Syntax

      array_remove(x, element)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.
      element
      Element in an array.

      Returned value type

      Array

      Sample

      Delete 23 from the [23,46,35] array.
      Search and analysis statement
      * | SELECT array_remove(array[23,46,35],23)
      Search and analysis result
      [46,35]

      array_sort

      The array_sort function is used to sort elements in an array in ascending order.

      Syntax

      array_sort(x)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.

      Returned value type

      Array

      Sample

      Sort elements in the ['b', 'd', null, 'c', 'a'] array in ascending order.
      Search and analysis statement
      * | SELECT array_sort(array['b','d',null,'c','a'])
      Search and analysis result
      ["a","b","c","d",null]

      array_union

      The array_union function is used to calculate the union of two arrays.

      Syntax

      array_union(x, y)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.
      y
      The parameter value is of the array type.

      Returned value type

      Array

      Sample

      Calculate the union of the arrays [1,2,3,4,5] and [1,3,5,7].
      Search and analysis statement
      * | SELECT array_union(array[1,2,3,4,5],array[1,3,5,7])
      Search and analysis result
      [1,2,3,4,5,7]

      cardinality

      The cardinality function is used to calculate the number of elements in an array.

      Syntax

      cardinality(x)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.

      Returned value type

      Bigint

      Sample

      Calculate the number of elements in the number field value.
      Field sample
      array:[12,23,26,48,26]
      Search and analysis statement
      * | SELECT cardinality(cast(json_parse(array) as array(bigint)))
      Search and analysis result
      5

      concat

      The concat function is used to concatenate multiple arrays into one.

      Syntax

      concat(x, y...)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.
      y
      The parameter value is of the array type.

      Returned value type

      Array

      Sample

      Concatenate the arrays ['red','blue'] and ['yellow','green'] into one array.
      Search and analysis statement
      * | SELECT concat(array['red','blue'],array['yellow','green'])
      Search and analysis result
      ["red","blue","yellow","green"]

      contains

      The contains function is used to determine whether an array contains a specified element and return true if the array contains the element.

      Syntax

      contains(x, element)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.
      element
      Element in an array.

      Returned value type

      Boolean

      Sample

      Determine whether the array field value contains 23.
      Field sample
      array:[12,23,26,48,26]
      Search and analysis statement
      * | SELECT contains(cast(json_parse(array) as array(varchar)),'23')
      Search and analysis result
      TRUE

      element_at

      The element_at function is used to return the yth element in an array.

      Syntax

      element_at(x, y)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.
      element
      Array subscript, starting from 1. The parameter value is of the bigint type.

      Returned value type

      Any data type

      Sample

      Return the second element of the number field value.
      Field sample
      array:[12,23,26,48,26]
      Search and analysis statement
      * | SELECT element_at(cast(json_parse(number) AS array(varchar)), 2)
      Search and analysis result
      23

      filter

      The filter function is used to filter elements in an array and return only the elements that comply with a specified Lambda expression

      Syntax

      filter(x, lambda_expression)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.
      lambda_expression
      Lambda expression. For more information, see Lambda Function.

      Returned value type

      Array

      Sample

      Return elements greater than 0 in the [5,-6,null,7] array, where x -> x > 0 is the Lambda expression.
      Search and analysis statement
      * | SELECT filter(array[5,-6,null,7],x -> x > 0)
      Search and analysis result
      [5,7]

      flatten

      The flatten function is used to convert a two-dimensional array to a one-dimensional array.

      Syntax

      flatten(x)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.

      Returned value type

      Array

      Sample

      Convert the two-dimensional array "array[1,2,3,4],array[4,3,2,1]" into a one-dimensional array.
      Search and analysis statement
      * | SELECT flatten(array[array[1,2,3,4],array[4,3,2,1]])
      Search and analysis result
      [1,2,3,4,4,3,2,1]

      reduce

      The reduce function is used to add the elements in an array as defined by the Lambda expression and return the result.

      Syntax

      reduce(x, lambda_expression)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.
      lambda_expression
      Lambda expression. For more information, see Lambda Function.

      Returned value type

      Bigint

      Sample

      Return the sum of the elements in array [5, 20, 50].
      Search and analysis statement
      * | SELECT reduce(array[5,20,50],0,(s, x) -> s + x, s -> s)
      Search and analysis result
      75

      reverse

      The reverse function is used to reverse the elements in an array.

      Syntax

      reverse(x)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.

      Returned value type

      Array

      Sample

      Reverse the elements in array [1,2,3,4,5].
      Search and analysis statement
      * | SELECT reverse(array[1,2,3,4,5])
      Search and analysis result
      [5,4,3,2,1]

      sequence

      The sequence function is used to return an array of consecutive and increasing values within the specified starting value range.

      Syntax

      The increment interval is the default value 1.
      sequence(x, y)
      The increment interval is custom.
      sequence(x, y, step)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the bigint or timestamp type (UNIX timestamp or date and time expression).
      y
      The parameter value is of the bigint or timestamp type (UNIX timestamp or date and time expression).
      step
      Value interval.If the parameter value is a date and time expression, the format of step is as follows:
      interval 'n' year to month: the interval is n years.
      interval 'n' day to second: the interval is n days.

      Returned value type

      Array

      Sample

      Example 1. Return even numbers between 0 and 10.
      Search and analysis statement
      * | SELECT sequence(0,10,2)
      Search and analysis result
      [0,2,4,6,8,10]
      Example 2. Return dates between 2017-10-23 and 2021-08-12 at an interval of one year.
      Search and analysis statement
      * | SELECT sequence(from_unixtime(1508737026),from_unixtime(1628734085),interval '1' year to month )
      Search and analysis result
      ["2017-10-23 05:37:06.0","2018-10-23 05:37:06.0","2019-10-23 05:37:06.0","2020-10-23 05:37:06.0"]
      Example 3. Return UNIX timestamps between 1628733298 and 1628734085 at an interval of 60 seconds.
      Search and analysis statement
      * | SELECT sequence(1628733298,1628734085,60)
      Search and analysis result
      [1628733298,1628733358,1628733418,1628733478,1628733538,1628733598,1628733658,1628733718,1628733778,1628733838,1628733898,1628733958,1628734018,1628734078]

      shuffle

      shuffle

      The shuffle function is used to randomize the elements in an array.

      Syntax

      shuffle(x)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.

      Returned value type

      Array

      Sample

      Randomize the elements in the [1,2,3,4,5] array.
      Search and analysis statement
      * | SELECT shuffle(array[1,2,3,4,5])
      Search and analysis result
      [5,2,4,1,3]

      slice

      The slice function is used to return a subset of an array.

      Syntax

      slice(x, start, length)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.
      start
      Index start position.
      If start is negative, start from the end.
      If start is positive, start from the beginning.
      length
      Number of elements in a subset.

      Returned value type

      Array

      Sample

      Return a subset of the [1,2,4,5,6,7,7] array, starting from the third element and consisting of two elements.
      Search and analysis statement
      * | SELECT slice(array[1,2,4,5,6,7,7],3,2)
      Search and analysis result
      [4,5]

      transform

      The transform function is used to apply a Lambda expression to each element of an array.

      Syntax

      transform(x, lambda_expression)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.
      lambda_expression
      Lambda expression. For more information, see Lambda Function.

      Returned value type

      Array

      Sample

      Increment each element in the [5,6] array by 1 and return.
      Search and analysis statement
      * | SELECT transform(array[5,6],x -> x + 1)
      Search and analysis result
      [6,7]

      zip

      The zip function is used to combine multiple arrays into a two-dimensional array, and elements with the same subscript in each original array form a new array.

      Syntax

      zip(x, y)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.
      y
      The parameter value is of the array type.

      Returned value type

      Array

      Sample

      Combine the arrays [1,2] and [3,4] into a two-dimensional array.
      Search and analysis statement
      * | SELECT zip(array[1,2], array[3,4])
      Search and analysis result
      ["{1, 3}","{2, 4}"]

      zip_with

      The zip_with function is used to merge two arrays into one as defined by a Lambda expression.

      Syntax

      zip_with(x, y, lambda_expression)

      Parameter description

      Parameter
      Description
      x
      The parameter value is of the array type.
      y
      The parameter value is of the array type.
      lambda_expression
      Lambda expression. For more information, see Lambda Function.

      Returned value type

      Array

      Sample

      Use Lambda expression (x, y) -> x + y to add the elements in arrays [1,2] and [3,4], respectively, and return the sum results as an array.
      Search and analysis statement
      * | SELECT zip_with(array[1,2], array[3,4],(x,y) -> x + y)
      Search and analysis result
      [4,6]
      
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy