Mysql JSON 형태로 조회하기

* 2022년 9월 16일 velog에 작성했던 게시글을 옮겨온 글입니다.

 

 

일대다 관계를 갖는 데이터 테이블 형태는 아주 흔하게 볼 수 있다. 웹 서비스의 가장 간단하고 기본적인 형태인 게시판을 예로 들면 한명의 사용자는 여러 게시글을 작성할 수 있다. 반대로 한개의 게시글은 여러명이 작성자가 될 수는 없다. 이것이 바로 onetomany 형태의 관계이다.

만약 사용자가 내가 쓴 글을 보려고 마이페이지에 들어가 내가 쓴 글을 조회한다면 우리는 어떻게 데이터를 넘겨줘야 할까. 보통 데이터를 주고 받을 때 Json, xml 형태로 가장 많이 자료를 넘긴다. 애초에 둘 다 데이터를 저장하고 전달하기 위해 만들어진 형태이기 때문이다. 또한 계층적인 데이터 구조를 가지기 때문에 한번에 얽혀있는 정보들을 표현할 수 있다는 장점이 있다.

 

잠깐! JSON이란?

 

JSON(JavaScript Object Notation)은 Javascript 객체 문법으로 구조화된 데이터를 표현하기 위한 문자 기반의 표준 포맷입니다. 웹 어플리케이션에서 데이터를 전송할 때 일반적으로 사용합니다(서버에서 클라이언트로 데이터를 전송하여 표현하려거나 반대의 경우)._

나는 한명의 사용자의 정보와 이 사용자가 쓴 게시글 여러개를 한번에 전달하여 마이페이지를 구성하고자 한다. http 통신을 이용하여 이러한 데이터들을 JSON 형태로 전달하고자 한다면 SELECT문을 사용하여 데이터들을 모두 가져온 후 이를 JavaScript로 로직을 짜 객체의 형태로 만들어야 한다. 빈 객체를 선언하고, 배열을 선언하고, 또 객체의 key에 알맞은 value들을 할당해야 한다. 컬럼의 수가 적다면 이런식으로 해도 괜찮겠지만 아주 많다면 코드도 아주 길어질 것이다. Mysql 쿼리만으로 데이터를 가공하고 싶어 찾아보니 function을 발견할 수 있었다.

 

JSON_ARRAYAGG, JSON_OBJECT

 

mysql> SELECT JSON_OBJECT('id', 1, 'name', 'Jack');
+-----------------------------------------+
| JSON_OBJECT('id', 1, 'name', 'Jack') |
+-----------------------------------------+
| {"id": 1, "name": "Jack"}            |
+-----------------------------------------+

 

사용법은 위와 같다. JSON_OBJECT를 사용하여 key, value를 순서대로 나열하면 객체의 형태로 결과를 보여준다. 사실 실제 데이터가 아니라 해당 function의 실행 결과를 보여준 것이다. 그럼 실제 데이터로 예를 들어보겠다.

 

users 테이블

 

posts 테이블

 

 

JSON_OBJECT 사용 예시에서 보여줬던 것처럼 실제 데이터를 이용하여 조회해도 객체의 형태로 출력된다. 쿼리는 다음과 같다.

 

SELECT JSON_OBJECT('key값', 컬럼명) FROM 테이블;

 

 

GUI를 사용하지 않고 터미널 상에서 mysql을 출력하였더니 결과가 깔끔하게 보이진 않지만 결과는 제대로 나왔다. 이번엔 JSON_ARRAYAGG를 사용하여 여러 객체를 하나의 JSON 배열로 묶었다. 게시글은 여러개이므로 위의 회원을 객체로 불러왔던 것처럼 JSON_OBJECT로 하나씩 객체로 만들어준 후 이들을 posting이라고 별칭지어 배열에 담았다. 이제 회원 아이디와 게시글을 묶어 하나의 JSON 형태로 출력하는 일만 남았다.

 

그전에 GROUP BY를 알아야 한다. 말 그대로 특정 컬럼(들)을 그룹화하여 출력하라는 조건을 달아주는 것이다. 만약 1번 사용자의 정보와 게시글만 가져오는 것이 아닌, 모든 사용자들의 정보와 게시글을 가져오는 것이라면 users 테이블의 id, name을 그룹화하여 위의 사진처럼 불러올 수 있게 된다. 즉, id, name을 같은 것들끼리 묶어 나머지 데이터를 출력해준다. 1번 id를 가진 사용자의 게시글을 묶고, 2번도 마찬가지 또 n번째 사용자도 똑같은 객체 형태로 출력한다. 현재 우리는 한명의 사용자의 게시글들만 불러오면 되기 때문에 필요없는 쿼리문이기는 하다. 그러나 유용하게 쓰이므로 알아두는 것이 좋다.

 

 

최종 쿼리문은 위와 같다. 하나의 쿼리 안에 또 다른 쿼리가 들어가는 것을 서브쿼리라고 한다. 정확히 말하면 FROM 절에 서브쿼리가 들어가는 것은 인라인 뷰라고 한다. 서브쿼리의 결과를 실제 생성된 테이블인 것처럼 사용할 수 있고, 이것은 실제로 테이블이 생성된 것이 아닌 우리가 필요한 정보를 얻기 위해 가상으로 임의 테이블을 생성하는 것이다.

 

 

쿼리를 이용하여 조회하고 이를 바로 data로 담아 보여줄 수 있을 것이라고 생각했지만, 위의 테이블 조회 결과에서도 볼 수 있듯이 객체는 객체이지만 실제 Object형의 데이터를 넣은 것이 아닌 임시로 형태를 만들어준 것이기 때문에 그냥 data를 반환하면 다음과 같이 출력된다.

 

 

결과를 그저 하나의 문자열로 인식해버린다. 고로 쿼리문으로 객체의 형태까지 만들 수 있었지만 전혀 전처리 없이 데이터만을 바로 반환할 수는 없었다. 하지만 전보다 훨씬 간단한 한줄의 코드이기에 사용하는 의미가 있다고 생각한다. 항상 JSON과 JS Object를 혼동하였는데 이번에도 그런 경우였다.

 

 

"JSON은 문자열 형태로 존재합니다" 라고 MDN에 친절하게 나와있다. function 명칭 그대로 'JSON'_OBJECT이므로 결과의 자료형은 VARCHAR이다.

 

 

JSON string과 같은 문자열 형태를 네이티브 객체로 변환할 때 위에서 사용한 JSON.parse 메서드를 사용하는데 이러한 과정을 파싱(Parsing)이라고 한다. 반대로 네트워크를 통해 전달할 수 있게 객체를 문자열로 변환하는 과정은 문자열화(Stringification)이라고 한다.

 

JavaScript 내장 메서드로 객체를 만들었을 때

 

json_arrayagg를 사용했을 때

 

각각의 코드 길이 차이가 꽤 나는 걸 볼 수 있습니다.

 

최종적으로 반환하는 데이터의 형식

 

//console.log() 결과 순차적으로 나열

 

console.log(data);

 

console.log(data[0]);

 

console.log(Object.values(data[0]));

 

파싱 과정을 거치면 데이터는 우리가 원하는 형태로 출력된다.

 

 

user 1의 정보와 게시글 조회

 

 

user 2의 정보와 게시글 조회

성공!

 

 

 

 

 

*아래의 자료들을 참고하였습니다.

JSON_OBJECT

MySQL :: WL#11574: Add JSON_ARRAYAGG and JSON_OBJECTAGG windowing functions

MySQL :: MySQL 8.0 Reference Manual :: 12.18.2 Functions That Create JSON Values

 

velog 주간 인기글에 오른 게시글

 

지식 공유의 즐거움을 느끼게 해준 첫번째 게시글이라 의미가 있어 기록으로 남기고 싶었습니다.