[MySQL] (JSON3️⃣) JSON_EXTRACT, JSON_UNQUOTE, JSON_CONTAINS 파헤치기

MySQL에서 JSON 데이터를 읽고 해석하려면 JSON_EXTRACT, JSON_UNQUOTE, JSON_CONTAINS 같은 전용 함수를 잘 활용해야 한다.

✅ JSON_EXTRACT: JSON 내부 값 읽기

JSON_EXTRACT는 JSON 필드에서 특정 경로의 값을 추출하는 기본 함수다.
연산자 -> 와 같은 역할이지만 더 명확한 문법으로 사용된다.

SELECT JSON_EXTRACT('{"name":"지훈", "info":{"age":28}}', '$.info.age');
-- 결과: 28

JSON_EXTRACT의 특징은 결과가 JSON 형식 그대로 반환된다는 점이다.

-- 문자열이 아닌 JSON 타입으로 반환됨
SELECT JSON_EXTRACT('{"name":"지훈"}', '$.name');
-- 결과: "지훈"

✅ Tip: 여러 필드를 동시에 뽑을 수도 있다

SELECT JSON_EXTRACT('{"a":1,"b":2,"c":3}', '$.a', '$.c');
-- 결과: [1, 3]

반응형

🔓 JSON_UNQUOTE: 따옴표 제거

JSON_EXTRACT로 값을 가져오면 문자열도 따옴표로 감싸진 상태로 반환된다.
JSON_UNQUOTE()를 사용하면 따옴표를 제거해서 순수 문자열로 변환할 수 있다.

SELECT JSON_UNQUOTE(JSON_EXTRACT('{"name":"지훈"}', '$.name'));
-- 결과: 지훈

또는 축약형 연산자인 ->>를 써도 같은 효과

SELECT '{"name":"지훈"}' ->> '$.name';
-- 결과: 지훈

✅ Tip: 검색 조건이나 LIKE 비교 시에는 꼭 UNQUOTE 처리하자

🔍 JSON_CONTAINS: 포함 여부 확인

JSON 내부에 특정 값이 존재하는지 확인할 때 사용하는 함수다.
배열 안에 값이 있는지, 혹은 객체에 특정 구조가 포함됐는지 등을 체크할 수 있다.

-- 배열에 특정 값이 있는지
SELECT JSON_CONTAINS('["apple", "banana", "cherry"]', '"banana"');
-- 결과: 1 (있음)

-- 객체에 부분 구조가 포함됐는지
SELECT JSON_CONTAINS('{"a":1, "b":2, "c":3}', '{"b":2}');
-- 결과: 1 (있음)

✅ 주의: 대상도 JSON 형식이어야 한다. 문자열이 아닌 "값"으로 감싸야 함.

-- 틀린 예: 숫자 그대로 비교하면 실패함
SELECT JSON_CONTAINS('[1,2,3]', '2');
-- 결과: 0

-- 올바른 예
SELECT JSON_CONTAINS('[1,2,3]', '2', '$');
-- 결과: 1

💡 실무 활용 예시

-- 회원 테이블에서 관심 카테고리에 'books'가 포함된 사용자 조회
SELECT *
FROM users
WHERE JSON_CONTAINS(interests, '"books"');

-- 설정 JSON에서 특정 키가 존재하는지 확인
SELECT *
FROM settings
WHERE JSON_EXTRACT(config, '$.dark_mode') IS NOT NULL;

-- 값 가져올 때 따옴표 제거해서 보기 좋게 출력
SELECT JSON_UNQUOTE(JSON_EXTRACT(profile, '$.nickname')) AS nickname
FROM users;

반응형

🧠 한눈에 보는 차이점 요약

함수	설명	반환 결과
JSON_EXTRACT	JSON 내부 경로의 값을 가져옴	JSON 형식
JSON_UNQUOTE	따옴표 제거 (문자열로 변환)	일반 문자열
JSON_CONTAINS	특정 값 포함 여부 검사	0 or 1

📘 공식 문서 참고

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