How to Query a JSON Column in Redshift using json_extract_path_text
You can store JSON in Redshift as a CHAR or VARCHAR column, but Amazon Web Services recommends using JSON sparingly, because it does not leverage Redshift's design.
Here are examples of what you can do with JSON values in Redshift:
-- Check if a field contains a valid JSON-formatted value
select is_valid_json(json_column) from table_name;
-- Check if a field contains a valid JSON array
select is_valid_json_array(json_column) from table_name;
-- Extract the value of `status` from a column named json_values
select json_extract_path_text(json_values, 'status')
from event_attributes;
-- Look for rows that have status: live in json_values column
select *
from event_attributes
where json_extract_path_text(json_values, 'status') = 'live';
-- Get the array length from a JSON array
select json_array_length(json_column) from table_name;
-- Get the 2nd element from a JSON array (first element is at position 0)
-- 3rd optional parameter tells Redshift to return NULL if not a valid JSON array
select json_extract_array_element_text(json_array_column, 1, true)
from event_attributes;
-- Combine JSON_EXTRACT_ARRAY_ELEMENT_TEXT with JSON_EXTRACT_PATH_TEXT
-- to extract an array element nested within a JSON value
select json_extract_array_element_text(json_extract_path_text(json_column, 'user_ids'), 2)
from event_attributes;
