Amazon Redshift Array Support and Alternatives – Example

  • Post author:
  • Post last modified:September 20, 2019
  • Post category:Redshift
  • Reading time:5 mins read

It is common to store values in the form of array in the database. Many databases like Netezza, PostgreSQL supports array functions to manipulate the array types. However, Amazon Redshift does not support array types. In this article, we will check alternative method that you can use.

Amazon Redshift Array Support

As mentioned in the previous section, Amazon Redshift does not support array types or functions. Though, Redshift uses PostgreSQL, but they yet to provide support to arrays. You can check unsupported features in the official documentation.

Best part is, Amazon Redshift provides support for JSON functions. As an alternative, you can use JSON function to manipulate and interact with array types.

Redshift Array Function Alternative Example

In this example, we will use Redshift JSON functions to get values out of array types.

Note that, in this article, we will be demonstrating only on how to extract values from array filed.

Follow below steps to work with array types in Redshift:

Store Array Value In VARCHAR Type Field

Create a table with varchar type and insert array type value that you want to store.

For example, consider below example to store array values.

create array_test (col1 varchar(20));

insert into _test values ('[10,100,1000]');
Use JSON Function json_extract_array_element_text() to Extract Array Value

Now, use Redshift provided json function json_extract_array_element_text() to extract the required value from the array that you stored in previous steps. For example, consider below example to extract second value from the array.

dev=# select json_extract_array_element_text(col1, 2) from array_test;

 json_extract_array_element_text
---------------------------------
 1000
(1 row)

As you can see from above example, you can extract values from array values without using specific array functions.

Related Articles

Hope this helps 🙂

Tags: ,