ARRAY_LENGTH()

Documentation

Home » Documentation » Using VoltDB

ARRAY_LENGTH()

ARRAY_LENGTH() — Returns the number of elements in a JSON array.

Synopsis

ARRAY_LENGTH( JSON-array )

Description

The ARRAY_LENGTH() returns the length of a JSON array; that is, the number of elements the array contains. The length is returned as an integer.

The ARRAY_LENGTH() function can be combined with other functions, such as FIELD(), to traverse more complex JSON structures.

The function returns NULL if the argument is a valid JSON string but does not represent an array. The function returns an error if the argument is not a valid JSON string.

Example

The following example uses the ARRAY_LENGTH(), ARRAY_ELEMENT(), and FIELD() functions to return the last element of an array in a larger JSON string. The functions perform the following actions:

  • Innermost, the FIELD() function extracts the JSON field "alerts", which is assumed to be an array, from the column messages.

  • ARRAY_LENGTH() determines the number of elements in the array.

  • ARRAY_ELEMENT() returns the last element based on the value of ARRAY_LENGTH() minus one (because the array positions are zero-based).

SELECT ARRAY_ELEMENT(FIELD(messages,'alerts'),
       ARRAY_LENGTH(FIELD(messages,'alerts'))-1) AS last_alert,
       station FROM reportlog 
       WHERE station=?;