SQL/Redshift

Redshift : json_extract_path_text() (Redshift에서 JSON data 참조하기, Redshift dictionary)

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

 

 

 

Redshift에서 json_extract_path_text() 함수를 사용하면 JSON 형태로 존재하는 text를 JSON을 다루듯이 사용할 수 있습니다.

 

Syntax

json_extract_path_text(json_text, key1, key2, ...)

 

사용법은 위와 같습니다.

첫 번째 인자로 JSON 형태의 텍스트를 받습니다.

그리고 두번째부터는 JSON에 있는 key값을 적어주면 됩니다.

 

 

 

select  '{"apple": 20000, "banana": 8000, "peach": 22000}' as json_string
        , json_extract_path_text(json_string, 'apple')   --> 20000
        , json_extract_path_text(json_string, 'banana')  --> 8000
        , json_extract_path_text(json_string, 'peach')   --> 22000
;

 

위 예시를 봅시다.

먼저 JSON 형태의 string을 가장 먼저 만들어줍니다.

{"apple": 20000, "banana": 8000, "peach": 22000}

마치 python의 dictionary같이 생겼습니다.

 

- json_extract_path_text(json_string, 'apple')

그리고 이건 json_string에서 apple이라는 key값을 갖는 value를 return하라는 의미입니다.

apple이라는 key값을 가진 것은 "apple": 20000이며 그래서 20000이 결과로 return됩니다.

 

다른 예시도 마찬가지입니다.

banana는 8000, peach는 22000을 return하는 것을 볼 수 있습니다.

 

 

 

 

 

 

 

이제 좀 더 복잡한 JSON을 다뤄봅시다.

 

select  '{
            "pc": {
                "monitor": 100000,
                "keyboard": 20000
            },
            "mobile": {
                "phone1": {
                    "price": 1000000,
                    "color: "white"
                },
                "phone2": {
                    "price": 2000000,
                    "color: "black"
                }
            }
        }' as json_string
        , json_extract_path_text(json_string, 'pc')



-- Result
{"monitor": 100000, "keyboard": 20000}

 

위 예시는 JSON이 좀 더 복잡합니다.

단순히 1차원적인 JSON이 아니라 중괄호 안에 중괄호가 있고 그 안에 또 다른 중괄호가 있는 다차원 JSON입니다.

이러한 JSON의 경우도 똑같이 key값을 이용해 값을 추출할 수 있습니다.

 

- json_extract_path_text(json_string, 'pc')

json_string에서 pc라는 key에 연결된 value값을 return합니다.

pc가는 key에 연결된 value는 {"monitor": 100000, "keyboard": 20000} 라는 또다른 JSON이므로 이 JSON자체가 return됩니다.

 

 

 

 

 

select  '{
            "pc": {
                "monitor": 100000,
                "keyboard": 20000
            },
            "mobile": {
                "phone1": {
                    "price": 1000000,
                    "color: "white"
                },
                "phone2": {
                    "price": 2000000,
                    "color: "black"
                }
            }
        }' as json_string
        , json_extract_path_text(json_string, 'pc', 'monitor') as col1
        , json_extract_path_text(json_string, 'pc', 'keyboard') as col2



-- Result
  col1    col2
100000   20000

 

json_extract_path_text는 JSON 속 JSON같이 다중으로 된 JSON data 또한 다룰 수 있습니다.

 

- json_extract_path_text(json_string, 'pc', 'monitor') as col1

위 예시는 json_string에서 먼저 pc라는 key에 연결된 value를 불러오고 거기서 monitor라는 key연결된 value를 최종적으로 return하라는 의미입니다.

pc라는 key에 연결된 value는 {"monitor": 100000, "keyboard": 20000}입니다.

여기서 monitor라는 key에 연결된 value는 100000이므로 최종적으로 100000이 return됩니다.

 

 

 

- json_extract_path_text(json_string, 'pc', 'keyboard') as col2

위 예시는 json_string에서 먼저 pc라는 key에 연결된 value를 불러오고 거기서 monitor라는 key연결된 value를 최종적으로 return하기 때문에 20000이 return됩니다.

 

 

 

 

 

 

 

select  '{
            "pc": {
                "monitor": 100000,
                "keyboard": 20000
            },
            "mobile": {
                "phone1": {
                    "price": 1000000,
                    "color: "white"
                },
                "phone2": {
                    "price": 2000000,
                    "color: "black"
                }
            }
        }' as json_string
        , json_extract_path_text(json_string, 'mobile') as col1
        , json_extract_path_text(json_string, 'mobile', 'phone1') as col2
        , json_extract_path_text(json_string, 'mobile', 'phone1', 'price') as col3



-- Result
col1 -> {"phone1": {"price": 1000000,"color: "white"},"phone2": {"price": 2000000,"color: "black"}}
col2 -> {"price": 1000000,"color: "white"}
col3 -> 1000000

 

위 예시 또한 비슷한 맥락입니다.

(위 예시의 결과는 보기 좋게 그냥 각 컬럼에 어떤 값이 return되는지를 표시하였습니다.)

 

col1은 mobile이라는 key에 연결된 value를 return하는데 그 value가 JSON이기 때문에 JSON을 return합니다.

{"phone1": {"price": 1000000,"color: "white"},"phone2": {"price": 2000000,"color: "black"}}

 

다른 것도 마찬가지 입니다.

 

 

 

 

 

 

 

 

 

select  '{
            "pc": {
                "monitor": 100000,
                "keyboard": 20000
            },
            "mobile": {
                "phone1": {
                    "price": 1000000,
                    "color: "white"
                },
                "phone2": {
                    "price": 2000000,
                    "color: "black"
                }
            }
        }' as json_string
        , json_extract_path_text(json_string, 'laptop') as col1



-- Result
null

 

json_string에서 laptop이라는 key는 없습니다.

이렇게 JSON에 존재하지 않는 key를 참조하려고 하는 경우 null값을 return합니다.

 

 

 

 

 

 

728x90
반응형