Redshift: Return rows if value exists in cell array How do I return the rows that include a particular value in a column's cell array?

Imagine we have a table like so:

id    name    phone           values
1     Taylor  xxx.xxx.xxxx    [4,6,5]
2     John    yyy.yyy.yyyy    [1,5,2]
3     Peter   zzz.zzz.zzzz    [6,2,6]


I need to create an SQL query that will return the rows where the value '6' exists in the values array.  So the expected output would be:

id    name    phone           values
1     Taylor  xxx.xxx.xxxx    [4,6,5]
3     Peter   zzz.zzz.zzzz    [6,2,6]


We are working in Redshift.  So, if it is easier, the possibility of using the json_extract_array_element_text('json_string', pos) function could be used.  Please note that the length of the values arrays could be different from each other.
I will give it a try :

First use this query to find the maximum length of longest array for values field in your SampleTable:

SELECT MAX(JSON_ARRAY_LENGTH(values)) FROM SampleTable;


Then create a view according to the result you get from above query using the below format( I am supposing the max size output as 2 with index starting from 0 as per the sample table data you have provided ):

CREATE VIEW seq_0_to_2 AS (
    SELECT 0 AS i UNION ALL
    SELECT 1 UNION ALL
    SELECT 2
);


Then use the below query to get the desired results:

WITH exploded_array AS (
    SELECT id,name,phone, JSON_EXTRACT_ARRAY_ELEMENT_TEXT(values, seq.i) AS NewExtractedValues
    FROM SampleTable, seq_0_to_2 AS seq
    WHERE seq.i &lt; JSON_ARRAY_LENGTH(values)
  )
SELECT *
FROM exploded_array
where NewExtractedValues = 6;


Hope this helps.

Inspired from https://torsten.io/stdout/expanding-json-arrays-to-rows/