[MySQL] JSON_ARRAGG, JSON_OBJECT

MySQL로 테이블을 조인해서 데이터를 불러오면 아래와 같이 출력된다.

SELECT users.id, users.profile_image, posts.id, posts.post_image_url, posts.content
FROM users
INNER JOIN posts on posts.user_id = users.id;

 

group by, subquery등 구글링을 하다가 방법들을 알아내기는 했지만

group by의 경우 집합함수(aggregate functions => ex. (COUNT(), MAX(), MIN(), SUM(), AVG() ))에 주로 쓰인다는 코멘트와 사례들을 보고 group by 예약어로는 불가능하구나 라고 생각하고 있었다.

 

그렇게 중복된 row를 어떻게 묶어줄 수 있을까 고민하고 있던 차에 과제에서 미션이 떨어졌다.

 

해당 과제는 특정 유저가 생성한 게시글을 조회하는 API를 raw query로 구현하는 것이었는데 나는 해당 아래처럼 쿼리를 2개로 작성했었다.

// 첫 번째 코드
app.get('/posts/userId/:id', async (req, res) => {
  const [user] = await myDataSource.query(
    `SELECT
    	users.id as userID,
    	users.profile_image as userProfileImage
     FROM users
     WHERE users.id = ${id}`
  );
  const post = await myDataSource.query(
    `SELECT
    	posts.id as postingId,
    	posts.post_image as postingImageUrl,
    	posts.content as postingContent
    FROM posts
    WHERE posts.user_id = ${id}`
  );
  user.posting = post;
  const userpost = user;
  res.status(200).json(userpost)
});

멘토님의 미션은 "나의 Query문에서 JOIN을 사용하여 해결해 보세요." 였다.

미션을 받고 멘붕이 왔지만, 참고자료로 보내주신 내용들을 차분히 읽고 아래와 같이 코드를 수정했다.

// 수정 코드
app.get('/posts/userId/:id', async (req, res) => {
  const { id } = req.params;
  await myDataSource.query(
    `SELECT
      users.id AS userID, users.profile_image AS userProfileImage,
      pi.postings
     FROM users
     JOIN (
     	SELECT user_id,
        	JSON_ARRAYAGG(
          	JSON_OBJECT(
            	"postingId", posts.id,
            	"postingImageUrl", posts.post_image,
            	"postingContent", posts.content
                )) AS postings
      	FROM posts
      	GROUP BY user_id
        ) pi
    ON users.id = pi.user_id
    WHERE users.id = ?
    GROUP BY users.id`,
    [id]
  );

  (err, rows) => res.status(200).json(rows);
});

이때 새로 배운 쿼리 문법이 JSON_ARRAYGG, JSON_OBJECT 였다.

 

JSON_ARRAYAGG(column_or_expression)
// 주어진 JSON 또는 SQL 값의 요소를 포함하는 JSON '배열'로 반환

JSON_OBJECT([key, value[, key, value] ...)
// 키:값 쌍의 목록을 평가하고 해당 쌍을 JSON '객체'로 반환

 

위의 내 코드도 작동하지만, 가독성을 고려했을 때 아래와 같이 코드를 작성하는 것이 좋겠다고 멘토님이 코멘트주셔서 바꾸었다.

요청에 대해 보내줘야 하는 응답 값과 쿼리문의 모양을 비교해 보니 훨씬 직관적인 것 같아서 반영했다.

// 수정 코드
app.get('/posts/userId/:id', async (req, res) => {
  const { id } = req.params;
  await myDataSource.query(
    `SELECT
      u.id userID,
      u.profile_image userProfileImage,
      JSON_ARRAYAGG(JSON_OBJECT(
        "postingId", p.id,
        "postingImageUrl", p.post_image_url,
        "postingContent", p.content
      )) postings
    FROM users u
    INNER JOIN posts p ON p.user_id = u.id
    WHERE u.id = ?
    GROUP BY u.id`,
    [id]
  );

  (err, rows) => res.status(200).json(rows);
});

 

 

정리하자면, RDB에서 정보를 1:1, 1:N, N:N 관계로 테이블을 여러개로 쪼갤텐데, 그 쪼갠 테이블의 데이터를 통합해서 응답해주려면 필요한 SQL 문법 같았다. 단순히 join문만을 활용했을 때 불필요하게 중복되는 데이터가 너무 많이 만들어진다. 그래서, 중복되는 column을 묶어서 (GROUP BY) 중복되지 않는 데이터 column을 JSON 형식으로 만들어주면(JSON_ARRAYAGG, JSON_OBJECT) 된다.