ARRAY_ELEMENT()

Documentation

Home » Documentation » Using VoltDB

ARRAY_ELEMENT()

ARRAY_ELEMENT() — Returns the element at the specified location in a JSON array.

Synopsis

ARRAY_ELEMENT( JSON-array, element-position )

Description

The ARRAY_ELEMENT() function extracts a single element from a JSON array. The array position is zero-based. In other words, the first element in the array is in position "0". The function returns the element as a string. For example, the following function invocation returns the string "two":

ARRAY_ELEMENT('["zero","one","two","three"]',2)

Note that the array element is always returned as a string. So in the following example, the function returns "2" as a string rather than an integer:

ARRAY_ELEMENT('[0,1,2,3]',2)

Finally, the element may itself be a valid JSON-encoded object. For example, the following function returns the string "[0,1,2,3]":

ARRAY_ELEMENT('[[0,1,2,3],["zero","one","two","three"]]',0)

The ARRAY_ELEMENT() function can be combined with other functions, such as FIELD(), to traverse more complex JSON structures. The function returns a NULL value if any of the following conditions are true:

  • The position argument is less than zero

  • The position argument is greater than or equal to the length of the array

  • The JSON string does not represent an array (that is, the string is a valid JSON scalar value or object)

The function returns an error if the first argument is not a valid JSON string.

Example

The following example uses the ARRAY_ELEMENT() function along with FIELD() to extract specific array elements from one field in a JSON-encoded VARCHAR column:

SELECT language, 
       ARRAY_ELEMENT(FIELD(words,'colors'),1) AS color,
       ARRAY_ELEMENT(FIELD(words,'numbers'),2) AS number
       FROM world_languages WHERE language = 'French'; 

Assuming the column words has the following structure, the query returns the strings "French', "vert", and "trois".

{"colors":["rouge","vert","bleu"],
 "numbers":["un","deux","trois"]}