[trino] 몽고디비 json형태 꺼내오는 쿼리(feat.unnest)

몽고DB 데이터 저장

현재 몽고 DB에 아래와 같은 데이터가 저장되어 있습니다. KEY로는 reg_date, user_id, ids가 있으며, ids안에 다시 key:value 형태로 id와 score를 저장하고 있습니다.

{
    "_id" : ObjectId("abcdefg"),
    "reg_date" : "2023-05-07",
    "user_id" : "foo",
    "ids" : [
        {
            "id" : "boo",
            "score" : "100.0"
        },
        {
            "id" : "boo1",
            "score : "80.0"
        }
    ]
}

트리노 몽고DB 조회

트리노에서 위의 몽고 데이터를 조회하게 되면 아래와 같이 나옵니다.

reg_date	user_id	ids
2023-05-07	foo	{{id=boo, score=100.0},{id=boo1, score = 80.0}}

 

문제

앞서 말씀 드린 것과 같이 ids안에는 각각의 id와 score가 key:value형태로 저장되어있습니다. 

이번 포스팅에서는 json형태의 ids안에 key를 각 컬럼 value를 row로 변경해보려 합니다.

reg_date	user_id	ids
2023-05-07	foo	{{id=boo, score=100.0},{id=boo1, score = 80.0}}

                                                                         ▼

reg_date	user_id	id	score
2023-05-07	foo	boo	100.0
2023-05-07	foo	boo1	80.0

해결

아래와 같이 unnest, cross join을 통해 간단하게 추출할 수 있습니다.

select a.reg_date, a.user_id ,id ,score 
from mongo.db.table a
cross join unnest(ids) as x(id,score)
where reg_date '2023-05-07'
and user_id ='foo'