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 values into varchar columns
- Use JSON Function json_extract_array_element_text() to Extract Required Array field
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
- Amazon Redshift JSON functions, usage and Examples
- Redshift Extract Function Usage and Examples
- Amazon Redshift Extract Numbers using Regular Expressions
- Amazon Redshift Split Delimited Fields into Table Records and Examples
- Redshift User Defined Functions, usage and Examples
Hope this helps 🙂