영리치
나의 재물운? 연애운은?
AI가 봐주는 내 사주팔자 - 운세박사
[MYSQL] JSON_ARRAYAGG join group by duplicates
영리치
0
2,106
2022.06.28 21:45
SELECT
accomodation.*
, JSON_ARRAYAGG(
JSON_OBJECT(
"id", accomodation_images.id,
"image_url", accomodation_images.image_url
)
) AS images
, JSON_ARRAYAGG(
JSON_OBJECT(
"id", room.id,
"image_url", room.name
)
) AS rooms
FROM accomodation_images, room
JOIN accomodation ON accomodation.id = room.accomodation_id
WHERE room.accomodation_id=${id} and accomodation_images.accomodation_id=${id}
GROUP BY accomodation.id
위와 같이, JOIN을 해서 SQL문을 짜면 중복값이 발생한다.
결과 값은 아래와 같다
{
"data": [
{
"id": 3,
"name": "[강릉세인트존스호텔] 바다 바로 앞 오션뷰!!",
"content": "세인트존스 경포호텔은 강문해변 앞에 위치해있으며, 오션뷰 객실로 시원한 동해 바다와 소나무를 함께 보실 수 있습니다. 숙소 바로 앞에 해변가를 따라 소나무 길이 위치해 있어 거리를 산책하며 청정 자연을 만끽할 수 있습니다. 호텔 주변에는 경포대와 최근 인기가 많아진 카페거리가 위아래로 인접해 있으며, 동해바다와 소나무를 따라 펼처진 해안도로는 드라이브의 운치를 더해줍니다. 행복하고 잊지 못할 추억을 만들어가세요 :)",
"location": "영도동, 해운대구, 부산, 한국",
"city": "부산",
"stay_type": "펜션",
"theme": "가족여행",
"created_at": "2022-06-22T11:20:31+00:00",
"updated_at": null,
"images": [
{
"id": 4,
"image_url": "https://a0.muscache.com/im/pictures/miso/Hosting-44176459/original/b7edb368-506f-4764-8abf-593c60d55c40.jpeg?im_w=1200"
},
{
"id": 4,
"image_url": "https://a0.muscache.com/im/pictures/miso/Hosting-44176459/original/b7edb368-506f-4764-8abf-593c60d55c40.jpeg?im_w=1200"
},
{
"id": 5,
"image_url": "https://a0.muscache.com/im/pictures/miso/Hosting-44176459/original/8abcd419-96aa-4971-a7fd-abfd4203d007.jpeg?im_w=720"
},
{
"id": 5,
"image_url": "https://a0.muscache.com/im/pictures/miso/Hosting-44176459/original/8abcd419-96aa-4971-a7fd-abfd4203d007.jpeg?im_w=720"
}
],
"rooms": [
{
"id": 5,
"image_url": "디럭스 더블 베드룸"
},
{
"id": 13,
"image_url": "디럭스 싱글 베드룸"
},
{
"id": 5,
"image_url": "디럭스 더블 베드룸"
},
{
"id": 13,
"image_url": "디럭스 싱글 베드룸"
}
]
}
]
}
위처럼, rooms와 images 하위에 중복값이 생긴다.
JOIN을 해서 문제다.
JOIN을 2개가 아니라
세개 네개까지 해야할 일이 있을텐데
이때마다 중복값이 생기면 어떻게 해결해야할까?
SELECT p.id, p.name AS productName, cg.category, JSON_ARRAYAGG(JSON_OBJECT("id", pa.id, "color", JSON_OBJECT("id", pa.color_id, "color", pa.color), "images", pa.images)) AS colorImage, p.sales_count
FROM (
SELECT pc.id, product_id, pc.color_id, pi.images, c.color
FROM product_color pc
JOIN color c on pc.color_id = c.id
JOIN (
SELECT product_color_id AS id, JSON_ARRAYAGG(JSON_OBJECT("id",product_images.id, "url", product_images.url)) AS images
FROM product_images WHERE MOD(id,2) = 1
GROUP BY product_color_id
) pi on pi.id = pc.id
) pa
JOIN products p ON p.id = pa.product_id
JOIN category cg ON cg.id = p.category_id
GROUP BY product_id, sales_count
ORDER BY sales_count DESC limit 20;
검색을 해보면 위와 같이 FROM 안에 또 SELECT를 넣어서 중복을 제거하는 작업을 한다.
근데 이렇게 쿼리를 짤꺼면 그냥 ORM 쓰고 말지.
이렇게까지 쿼리를 짜야하는 이유가 뭘까?
그냥 너무 어렵고 복잡해서 글로벌 기업 빅데이터 팀 형한테 물어봤더니
준 답은 아래와 같다.
WITH A AS(
SELECT
accomodation.*
, JSON_ARRAYAGG(
JSON_OBJECT(
"id",
accomodation_images.id,
"image_url",
accomodation_images.image_url
)
) AS images
FROM accomodation
JOIN accomodation_images ON accomodation.id = accomodation_images.accomodation_id
WHERE accomodation.id=${id}
GROUP BY accomodation.id
)
,B AS(
SELECT
accomodation.*
, JSON_ARRAYAGG(
JSON_OBJECT(
"id",
room.id,
"image_url",
room.name
)
) AS rooms
FROM accomodation
JOIN room ON accomodation.id = room.accomodation_id
WHERE accomodation.id=${id}
GROUP BY accomodation.id
)
SELECT *
FROM A
JOIN B ON A.id = B.id
이런 식으로 JOIN이 늘어가면
A, B, C, D 이렇게 늘려가면 된다.
이렇게 처리하면 엄청 쉽다.
근데도 검색해도 안나온다니 ㅠㅠㅠㅠ
검색해도 안나오는 정보를 뿌린다.
쿼리 JOIN 시 중복값 제거 방법
번외로 쿼리 JOIN과 GROUP BY, JSON_ARRAYAGG를 활용하여
테이블의 하위 테이블까지 json 자식 형태로 만드는 방법은 다음과 같다.
WITH A AS(
SELECT
accomodation.*
, JSON_ARRAYAGG(
JSON_OBJECT(
"id",
accomodation_images.id,
"image_url",
accomodation_images.image_url
)
) AS images
FROM accomodation
JOIN accomodation_images ON accomodation.id = accomodation_images.accomodation_id
WHERE accomodation.id=${id}
GROUP BY accomodation.id
)
,B AS(
SELECT
accomodation.*
, JSON_ARRAYAGG(
JSON_OBJECT(
"id",
C.id,
"name",
C.name,
"content",
C.content,
"images",
C.images
)
) AS rooms
FROM (
SELECT
room.*
, JSON_ARRAYAGG(
JSON_OBJECT(
"id", room_images.id,
"image_url", room_images.image_url
)
) AS images
FROM room
JOIN room_images ON room.id = room_images.room_id
GROUP BY room.id
) C
JOIN accomodation ON accomodation.id = C.accomodation_id
WHERE accomodation.id=${id}
GROUP BY accomodation.id
)
SELECT *
FROM A
JOIN B ON A.id = B.id
위와 같은 방법을 이용하면
아래와 같이 하위 테이블의 하위 테이블을,
json 자식 형태로 무한히 반복하여 줄 수 있다.
{
"data": [
{
"id": 19,
"name": "헬로우월드",
"content": " 카페이자 라이프 굿즈가 구비되어 있는 웰컴 플레이스가 1층에 있습니다. 신선한 음료와 베이커리, 계절음료를 만나보세요.",
"location": "서울 종로구 견지동 25",
"city": "서울",
"stay_type": "게스트하우스",
"theme": "도심속휴식",
"created_at": "2022-06-22T13:58:11+00:00",
"updated_at": null,
"images": [
{
"id": 97,
},
{
"id": 98,
},
{
"id": 99,
},
{
"id": 100,
},
{
"id": 101,
}
],
"rooms": [
{
"id": 38,
"name": "nomor",
"images": [
{
"id": 60,
},
{
"id": 61,
}
],
"content": "매일 같이 반복되는 평범한 일상 속에서 우리들만의 행복한 순간을 이곳에서 쌓아가길 바랍니다. "
},
{
"id": 39,
"name": "nomore wander",
"images": [
{
"id": 62,
},
{
"id": 63,
}
],
"content": "더블룸은 퀸침대가 놓여진 공간으로 연인이나 친구가 사용하시기에 적합합니다."
}
]
}
]
}
이상
Comments
나의 재물운? 연애운은?
AI가 봐주는 내 사주팔자 - 운세박사
