SQL/Redshift

Redshift : json_extract_array_element_text() (Redshift에서 array indexing하기, array 선택하기, array 값 추출)

CosmosProject 2023. 8. 4. 19:27
728x90
반응형

 

 

 

Redshift에서 json_extract_array_element_text() 함수는 indexing을 통해 array에서 값을 추출해줍니다.

 

Syntax

json_extract_array_element_text(json_array_text, index)

 

사용법은 위와 같습니다.

 

json_array_text -> JSON array 형태로 된 text를 받습니다.

index -> 추출할 index의 번호를 의미합니다.

 

 

 

select  '["apple", "banana", "pineapple", "peach"]' as json_arr_text
        , json_extract_array_element_text(json_arr_text, 0) as col0
        , json_extract_array_element_text(json_arr_text, 1) as col1
        , json_extract_array_element_text(json_arr_text, 2) as col2
        , json_extract_array_element_text(json_arr_text, 3) as col3
        , json_extract_array_element_text(json_arr_text, 4) as col4



-- Result
 col0     col1        col2    col3    col4
apple   banana   pineapple   peach    null

 

예시르 ㄹ보면 바로 이해가 갈겁니다.

 

먼저 JSON array 형태인 text를 만듭니다.

'["apple", "banana", "pineapple", "peach"]'

 

Python의 list와 매우 흡사합니다.

 

- json_extract_array_element_text(json_arr_text, 0) as col0

json_arr_text에서 index = 0 위치에 있는 값을 return합니다.

array에서 가장 첫 번째 element의 index는 0입니다.

따라서 위 식은 가장 첫 번째에 있는 apple을 return합니다.

 

나머지도 마찬가지입니다.

 

 

 

- json_extract_array_element_text(json_arr_text, 4) as col4

한 가지 주의할 것은 바로 위 예시입니다.

 

json_arr_text를 보면 총 4개의 element가 있습니다.

그러니까 각 element의 index는 0, 1, 2, 3이겠죠.

4라는 index를 가진 값은 존재하지 않습니다.

 

이렇게 존재하지 않는 위치의 값을 참조하려고 시도하면 null값을 return합니다.

 

 

 

 

 

 

 

 

 

 

select  '["apple", ["banana", ["pineapple", "strawberry]], "shine muscat"]' as json_arr_text
        , json_extract_array_element_text(json_arr_test, 0) as idx_0
        , json_extract_array_element_text(json_arr_test, 1) as idx_1
        , json_extract_array_element_text(idx_1, 0) as idx_1_0
        , json_extract_array_element_text(idx_1, 1) as idx_1_1
        , json_extract_array_element_text(idx_1_1, 0) as idx_1_1_0
        , json_extract_array_element_text(idx_1_1, 1) as idx_1_1_1
        , json_extract_array_element_text(json_arr_test, 2) as idx_2
        , json_extract_array_element_text(json_arr_test, 3) as idx_3



-- Result
    idx_0  ->  apple
    idx_1  ->  ["banana", ["pineapple", "strawberry]]
  idx_1_0  ->  banana
  idx_1_1  ->  ["pineapple", "strawberry]
idx_1_1_0  ->  pineapple
idx_1_1_1  ->  strawberry
    idx_2  ->  shine muscat
    idx_3  ->  null

 

JSON array는 단순히 1차원적인 array 뿐 아니라 array속 array가 들어가있는 다차원 array도 가능합니다.

 

 

 

 

 

        , json_extract_array_element_text(json_arr_test, 1) as idx_1
        , json_extract_array_element_text(idx_1, 0) as idx_1_0
        , json_extract_array_element_text(idx_1, 1) as idx_1_1
        , json_extract_array_element_text(idx_1_1, 0) as idx_1_1_0
        , json_extract_array_element_text(idx_1_1, 1) as idx_1_1_1



    idx_1  ->  ["banana", ["pineapple", "strawberry]]
  idx_1_0  ->  banana
  idx_1_1  ->  ["pineapple", "strawberry]
idx_1_1_0  ->  pineapple
idx_1_1_1  ->  strawberry

 

위 부분이 다차원 array를 사용하는 방법입니다.

json_extract_array_element_text 함수는 다중 index를 명시하는 것이 불가능해서 위처럼 json_extract_array_element_text 함수를 여러 번 사용하여 array 속의 array를 다뤄야 합니다.

 

보시면 idx_1 컬럼은 원본 array에서 index = 1 위치에 있는 값을 return하므로 아래 array를 return합니다.

["banana", ["pineapple", "strawberry]]

 

그리고 idx_1_0 컬럼은 idx_1에서 return된 내부 array에서 index = 0 위치에 있는 값을 return하므로 banana를 return합니다.

 

 

 

 

 

 

728x90
반응형