Redshift: Return rows if value exists in cell array

Question

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.

Shivam Kumar · Answer 1 · 2017-01-03T18:12:50.063

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 < 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/

score 0 · Answer 2 · answered Jun 28 '17 at 01:35

Just to add a little to Shivam's answer, if your JSON column contains NULLs and you need to handle those (for example a dimension in an ETL process), rather than using the JOIN shorthand of FROM table1, table2 use the full LEFT JOIN syntax like:

WITH exploded_array AS (
    SELECT id,name,phone, JSON_EXTRACT_ARRAY_ELEMENT_TEXT(values, seq.i) AS NewExtractedValues
    FROM SampleTable 
    LEFT JOIN seq_0_to_2 AS seq
        ON seq.i < JSON_ARRAY_LENGTH(values)
  )
SELECT *
FROM exploded_array
where NewExtractedValues = 6;

Redshift: Return rows if value exists in cell array

2 Answers2