Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/en/sql-reference/functions/array_size below:

Website Navigation

• Reference
• Function and stored procedure reference
• Semi-structured and structured data

ARRAY_SIZE | Snowflake Documentation

Categories:

Semi-structured and structured data functions (Array/Object)

Returns the size of the input array.

A variation of ARRAY_SIZE takes a VARIANT value as input. If the VARIANT value contains an array, the size of the array is returned; otherwise, NULL is returned if the value is not an array.

ARRAY_SIZE( <array> )

ARRAY_SIZE( <variant> )

Copy

The data type of the returned value is INTEGER.

• Takes an ARRAY value as input and returns the size of the array (i.e. the largest index + 1).

  If the array is a sparse array, this means that the size includes the undefined elements as well as the defined elements.

• A NULL argument returns NULL as a result.

Here is a simple example:

SELECT ARRAY_SIZE(ARRAY_CONSTRUCT(1, 2, 3)) AS SIZE;
+------+
| SIZE |
|------|
|    3 |
+------+

Copy

Here is a slightly more complex example, this time using VARIANT data type:

CREATE OR replace TABLE colors (v variant);

INSERT INTO
   colors
   SELECT
      parse_json(column1) AS v
   FROM
   VALUES
     ('[{r:255,g:12,b:0},{r:0,g:255,b:0},{r:0,g:0,b:255}]'),
     ('[{r:255,g:128,b:0},{r:128,g:255,b:0},{r:0,g:255,b:128},{r:0,g:128,b:255},{r:128,g:0,b:255},{r:255,g:0,b:128}]')
    v;

Copy

Retrieve the size for each array in the VARIANT column:

SELECT ARRAY_SIZE(v) from colors;
+---------------+
| ARRAY_SIZE(V) |
|---------------|
|             3 |
|             6 |
+---------------+

Copy

Retrieve the last element of each array in the VARIANT column:

SELECT GET(v, ARRAY_SIZE(v)-1) FROM colors;
+-------------------------+
| GET(V, ARRAY_SIZE(V)-1) |
|-------------------------|
| {                       |
|   "b": 255,             |
|   "g": 0,               |
|   "r": 0                |
| }                       |
| {                       |
|   "b": 128,             |
|   "g": 0,               |
|   "r": 255              |
| }                       |
+-------------------------+

Copy

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4