Redshift：如果單元格數組中存在值，則返回行

如何在列的元胞數組中返回包含特定值的行？

想像一下我們有一個這樣的表：

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]

values我需要創建一個 SQL 查詢，該查詢將返回數組中存在值“6”的行。所以預期的輸出是：

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

我們在 Redshift 工作。因此，如果它更容易，可以使用使用該json_extract_array_element_text('json_string', pos)功能的可能性。請注意，值數組的長度可能彼此不同。

我會試一下 ：

values首先使用此查詢查找您的欄位中最長數組的最大長度SampleTable：

SELECT MAX(JSON_ARRAY_LENGTH(values)) FROM SampleTable;

然後根據您從上述查詢中獲得的結果使用以下格式創建一個視圖（我假設最大大小輸出為 2，根據您提供的範例表數據，索引從 0 開始）：

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

然後使用以下查詢來獲得所需的結果：

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;

希望這可以幫助。

靈感來自https://torsten.io/stdout/expanding-json-arrays-to-rows/

只是為了給 Shivam 的答案添加一點，如果您的 JSON 列包含 NULL 並且您需要處理這些（例如 ETL 過程中的維度），而不是使用FROM table1, table2完整的 LEFT JOIN 語法的 JOIN 速記，例如：

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;

引用自：https://dba.stackexchange.com/questions/159841