0. 요구사항

• Visit를 추출할때 restaurant, review데이터를 join하여 함께 추출하려고함
• 이때 쿼리 결과값으로 restaurant와 같은 column이 존재하고 그 내부에 restaurant object가 존재하길 원함

1. row_to_json() 사용

row_to_json() 를 활용하여 PostgreSQL을 사용할때 테이블의 로우(행)를 JSON 형식으로 변환할 수 있다.

SELECT 쿼리 내에서 원하는 로우 또는 테이블 조인 결과에 row_to_json(테이블 별칭.*)의 형태로 적용할 수 있습니다.

나의 예시는, 방문(Visit), 식당(Restaurant), 리뷰(Review)를 각각의 JSON 객체로 변환하고, 이들을 한 번에 포함하는 결과를 만들고자하였다.

Query

SELECT
    v.id AS visit_id,
    v.created_at AS visit_created_at,
    v.user_id,
    row_to_json(r.*) AS restaurant,
	row_to_json(re.*) AS review
FROM
    Visit v
JOIN
    Restaurant r ON v.restaurant_id = r.id
LEFT JOIN
    Review re ON v.id = re.visit_id;

result

{
visit_id:
1
visit_created_at:
2023-04-01 10:00:00
user_id:
1
restaurant:
{"id":1,"name":"샐러드앤트라타","address":"서울시 마포구 익선로 3길-12","location_description":"맛있는 집이요~","opening_hours":"평일 오전 10시~오후9시, 주말 휴무","created_at":"2024-03-22T06:25:46.6683","image":"https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcRAkXNEj_HppWytn-hzaS6wUdCsYRmWVjFovA&usqp=CAU","phone":"02-1234-5678","table_count":10,"max_stamp_count":10,"category":"한식"}
review:
{"id":1,"created_at":"2024-03-24T08:57:33.798479","rating":"good","visit_id":1,"bad_taste":false,"bad_hygiene":false,"bad_service":false}
}

2. jsonb_build_object사용

추가적으로 해당 레스토랑이 현재 유저와 단골관계에 있는지 확인하고자 하는 요구사항이 있다.

이를 위해 restaurant 내부에서 is_dangol column이 존재하고 이것의 존재유무에 따라 단골관계인지 확인할 수 있도록 하려고한다.

jsonb_build_object 함수는 PostgreSQL에서 키-값 쌍을 인자로 받아 JSONB 형식의 객체를 구성.

Query

SELECT
    v.id AS visit_id,
    v.created_at AS visit_created_at,
    v.user_id,
    jsonb_build_object(
        'id', r.id,
        'name', r.name,
        'category', r.category,
        'address', r.address,
        'location_description', r.location_description,
        'opening_hours', r.opening_hours,
        'image', r.image,
        'phone', r.phone,
        'table_count', r.table_count,
        'max_stamp_count', r.max_stamp_count,
        'is_dangol', EXISTS (
            SELECT 1
            FROM dangol d
            WHERE d.restaurant_id = r.id AND d.user_id = 1
        )
    ) AS restaurant,
    row_to_json(re.*) AS review
FROM
    Visit v
JOIN
    Restaurant r ON v.restaurant_id = r.id
LEFT JOIN
    Review re ON v.id = re.visit_id;

Result

{
visit_id:
1
visit_created_at:
2023-04-01 10:00:00
user_id:
1
restaurant:
{"id": 1, "name": "샐러드앤트라타", "image": "https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcRAkXNEj_HppWytn-hzaS6wUdCsYRmWVjFovA&usqp=CAU", "phone": "02-1234-5678", "address": "서울시 마포구 익선로 3길-12", "category": "한식", "is_dangol": true, "table_count": 10, "opening_hours": "평일 오전 10시~오후9시, 주말 휴무", "max_stamp_count": 10, "location_description": "맛있는 집이요~"}
review:
{"id":1,"created_at":"2024-03-24T08:57:33.798479","rating":"good","visit_id":1,"bad_taste":false,"bad_hygiene":false,"bad_service":false}
}

3. jsonb_agg

방문 데이터를 추출할때 각각의 방문데이터에서 수령한 쿠폰 보상들을 같이 출력해야하는 요구사항이 있었다.

jsonb_agg는 여러 로우의 값을 JSONB 형식의 배열로 집계. jsonb_agg를 사용하면, 쿼리 결과로 반환되는 여러 로우의 데이터를 단일 JSON 배열로 그룹화

Query

  (SELECT jsonb_agg(jsonb_build_object(
        'id', sr.id,
        'stampCount', sr.stamp_count,
        'description', sr.description
    )) FROM stamp_exchange se
    INNER JOIN stamp_reward sr ON se.stamp_reward_id = sr.id
    WHERE se.visit_id = v.id) AS stamp_exchanges

Response

stamp_exchanges:
[{"id": 1, "stampCount": 3, "description": "샐러드 1개 무료"}]