[MySQL] JSON_EXTRACT와 -> 연산자로 값 조회하기

MySQL에서 JSON 타입 컬럼에 저장된 데이터는 단순 문자열이 아닌 구조화된 객체이므로, 내부 값을 효율적으로 추출할 수 있는 전용 함수와 연산자가 제공된다. 그중 가장 많이 쓰이는 것이 바로 JSON_EXTRACT() 함수와 ->, ->> 연산자이다.

1. JSON_EXTRACT 함수란?

JSON_EXTRACT(json_doc, path)

• json_doc: JSON 형식의 컬럼 또는 문자열
• path: 내부 키를 탐색하는 경로 (예: '$.name', '$.items[0]')

예제

SELECT JSON_EXTRACT('{"name": "홍길동", "age": 30}', '$.name');
-- 결과: "홍길동"

• 결과가 JSON 문자열로 반환됨 (따옴표 포함)
• 실제 문자열 값으로 쓰려면 ->> 또는 JSON_UNQUOTE() 사용

2. -> 와 ->> 연산자 차이

연산자	기능	반환값
->	JSON_EXTRACT와 동일	JSON 값
->>	추출된 값을 문자열로 변환	TEXT

예제

SELECT '{"name": "홍길동"}' -> '$.name';
-- 결과: "홍길동" (따옴표 포함)

SELECT '{"name": "홍길동"}' ->> '$.name';
-- 결과: 홍길동 (순수 문자열)

반응형

3. 배열 요소 접근하기

SELECT JSON_EXTRACT('["A", "B", "C"]', '$[1]');
-- 결과: "B"

-> 연산자도 동일하게 사용 가능:

SELECT '["A", "B", "C"]' ->> '$[2]';
-- 결과: C

4. 테이블 내 JSON 컬럼 값 추출

CREATE TABLE users (
  id INT,
  profile JSON
);

INSERT INTO users VALUES
(1, '{"name": "영희", "age": 25}'),
(2, '{"name": "철수", "age": 31}');

SELECT 
  id,
  profile ->> '$.name' AS 이름,
  profile -> '$.age' AS 나이_JSON,
  profile ->> '$.age' AS 나이_문자열
FROM users;

• ->는 JSON 형식으로 반환되어 정렬이나 비교 연산에 제약
• ->>는 문자열로 변환되어 WHERE 조건이나 LIKE 등에 유리

5. 중첩된 JSON 경로 추출

SELECT JSON_EXTRACT(
  '{"user": {"info": {"email": "test@ex.com"}}}',
  '$.user.info.email'
);
-- 결과: "test@ex.com"

SELECT '{"user": {"info": {"email": "test@ex.com"}}}' ->> '$.user.info.email';
-- 결과: test@ex.com

반응형

6. 실무 활용 팁

• WHERE 조건에서 ->>를 사용하면 문자열로 비교되어 성능이 안정적이다.

SELECT * FROM users
WHERE profile ->> '$.name' = '철수';

• JSON 컬럼을 직접 조건문에 사용하려면 항상 경로 지정이 명확해야 한다.
• 정렬, LIKE, IN 등 문자열 기반 연산에는 ->>를 추천

7. JSON 데이터 존재 여부 확인

SELECT JSON_CONTAINS_PATH(profile, 'one', '$.name') FROM users;
-- 결과: 1 (존재), 0 (없음)

• JSON_CONTAINS_PATH()와 JSON_CONTAINS()는 필드 존재 여부 체크 시 활용됨

8. 보너스: 값이 없는 경우 대체 처리

SELECT IFNULL(profile ->> '$.nickname', '별명없음') AS 닉네임
FROM users;

• 존재하지 않는 JSON 키 조회 시 NULL 반환되므로 IFNULL, COALESCE 등과 함께 사용

참고 문서

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html
https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html