MySQL JSON 함수 — JSON_OBJECT·EXTRACT·REPLACE 완전 정리

 

 

Database · MySQL

MySQL JSON 함수 — JSON_OBJECT·EXTRACT·REPLACE 완전 정리

JSON 값을 만들고, 검증하고, 검색하고, 경로로 꺼내고, 일부 값을 수정하는 MySQL JSON 함수 흐름

시작 오늘 분석할 코드

원본 예제는 MySQL에서 관계형 테이블의 컬럼을 JSON 객체로 만들고, 문자열 JSON을 변수에 담아 검증·검색·추출·삽입·교체·삭제하는 흐름을 보여준다.

SELECT JSON_OBJECT('name', name, 'height', height) AS 'JSON 값' FROM usertbl WHERE height >= 180; SET @json = '{ "usertbl": [ {"name":"임재범","height":182}, {"name":"이승기","height":182}, {"name":"성시경","height":186} ] }'; SELECT JSON_VALID(@json) AS JSON_VALID; SELECT JSON_SEARCH(@json, 'one', '성시경') AS JSON_SEARCH; SELECT JSON_EXTRACT(@json, '$.usertbl[2].name') AS JSON_EXTRACT; SELECT JSON_INSERT(@json, '$.usertbl[0].mDate', '2009-09-09') AS JSON_INSERT; SELECT JSON_REPLACE(@json, '$.usertbl[0].name', '홍길동') AS JSON_REPLACE; SELECT JSON_REMOVE(@json, '$.usertbl[0]') AS JSON_REMOVE;

실제 결과 미리보기

함수	예상 결과	의미
JSON_VALID(@json)	1	문법상 올바른 JSON
JSON_SEARCH(@json,'one','성시경')	"$.usertbl[2].name"	값이 위치한 경로
JSON_EXTRACT(@json,'$.usertbl[2].name')	"성시경"	경로에 있는 값

용어 용어 정리

JSON키와 값, 배열, 객체로 데이터를 표현하는 텍스트 기반 구조

JSON_OBJECT()키와 값을 번갈아 받아 JSON 객체를 만드는 함수

JSON_VALID()문자열이 유효한 JSON 형식인지 1 또는 0으로 검사

JSON_SEARCH()특정 값을 찾아 JSON path 문자열을 반환

JSON_EXTRACT()JSON path로 지정한 위치의 값을 꺼냄

JSON_INSERT()없는 경로에 새 값을 추가. 이미 있는 경로는 덮어쓰지 않음

JSON_REPLACE()이미 존재하는 경로의 값을 교체. 없는 경로는 추가하지 않음

JSON_REMOVE()지정 경로의 값이나 객체를 제거

JSON path$.usertbl[2].name처럼 JSON 내부 위치를 가리키는 주소

1 JSON_OBJECT — 테이블 행을 JSON 객체로 만들기

JSON_OBJECT('name', name, 'height', height)는 왼쪽에는 JSON 키 이름, 오른쪽에는 컬럼 값을 넣는다. 결과는 각 행마다 하나의 JSON 객체가 된다.

JSON_OBJECT('name', name, 'height', height) -- name 컬럼 값이 '성시경', height 컬럼 값이 186이면 -- {"name": "성시경", "height": 186}

키 이름은 문자열로 직접 쓰고, 값 자리에는 컬럼명·상수·함수 결과를 넣을 수 있다.

2 JSON_VALID — 먼저 문법을 검사한다

JSON 함수는 입력 JSON이 올바른 형식이라고 가정하고 동작한다. 외부에서 받은 문자열이나 직접 조립한 JSON은 먼저 JSON_VALID()로 검사하는 습관이 좋다.

SELECT JSON_VALID(@json) AS JSON_VALID;

결과 1

JSON 형식이 올바르다. 이후 검색·추출·수정 함수를 적용할 수 있다.

결과 0

따옴표, 중괄호, 쉼표, 배열 문법 중 어딘가가 잘못되었다는 뜻이다.

3 JSON path 읽는 법

$.usertbl[2].name은 JSON 내부 위치를 가리키는 주소다. $는 전체 JSON의 루트, usertbl은 키, [2]는 배열의 세 번째 요소, name은 그 객체의 이름 필드다.

1

$JSON 문서 전체의 시작점

2

.usertbl루트 객체 안의 usertbl 키로 이동

3

[2]배열에서 0, 1, 2 순서로 세 번째 요소 선택

4

.name선택된 객체 안의 name 값 추출

SQL의 문자열 위치는 1부터 세는 함수가 많지만, JSON 배열 인덱스는 0부터 시작한다. 이 차이가 가장 흔한 실수다.

 

반응형

 

4 JSON_SEARCH와 JSON_EXTRACT의 차이

JSON_SEARCH()는 값을 찾아서 "위치"를 돌려준다. JSON_EXTRACT()는 지정한 위치에서 "값"을 꺼낸다. 하나는 경로 탐색, 하나는 값 추출이다.

함수	질문	반환	예시
JSON_SEARCH	성시경이 어디에 있나?	경로	$.usertbl[2].name
JSON_EXTRACT	이 경로의 값은 무엇인가?	값	"성시경"

실무에서는 먼저 SEARCH로 위치를 찾고, 그 위치를 기준으로 EXTRACT나 수정 함수를 연결하는 흐름을 만들 수 있다.

5 JSON_INSERT와 JSON_REPLACE — 추가와 교체의 기준

두 함수는 비슷해 보이지만 동작 조건이 반대다. JSON_INSERT()는 없는 경로에만 새 값을 넣고, JSON_REPLACE()는 이미 있는 경로의 값만 바꾼다.

JSON_INSERT

새 속성을 추가할 때 사용한다. 경로가 이미 있으면 기존 값을 보존한다.

JSON_REPLACE

기존 속성을 수정할 때 사용한다. 경로가 없으면 아무 것도 추가하지 않는다.

SELECT JSON_INSERT(@json, '$.usertbl[0].mDate', '2009-09-09'); SELECT JSON_REPLACE(@json, '$.usertbl[0].name', '홍길동');

6 JSON_REMOVE — 객체나 배열 요소 지우기

JSON_REMOVE(@json, '$.usertbl[0]')는 usertbl 배열의 첫 번째 객체 전체를 제거한다. 배열 요소를 지우면 뒤 요소들의 인덱스가 앞으로 당겨진다는 점을 기억해야 한다.

배열에서 여러 요소를 삭제할 때는 인덱스가 바뀐다. 보통 뒤쪽 인덱스부터 지우거나, 삭제 대상 조건을 별도로 정리한 뒤 처리한다.

7 실무 활용 패턴

상황	사용 함수	이유
API 응답 형태로 행을 묶어야 함	JSON_OBJECT	컬럼 값을 키-값 구조로 바로 만들 수 있다
JSON 문자열이 깨졌는지 확인	JSON_VALID	검색·수정 전에 입력값을 검증한다
특정 이름이 어디 있는지 찾아야 함	JSON_SEARCH	값의 위치를 JSON path로 얻는다
경로를 알고 값을 꺼내야 함	JSON_EXTRACT	지정 경로의 값을 반환한다
새 속성을 추가	JSON_INSERT	없는 경로에만 안전하게 추가한다
기존 속성을 수정	JSON_REPLACE	이미 있는 값만 교체한다

8 자주 하는 실수

실수	증상	해결
JSON 문자열 안의 큰따옴표 누락	JSON_VALID 결과가 0으로 나온다	키와 문자열 값은 큰따옴표로 감싼다
배열 인덱스를 1부터 셈	원하는 값보다 한 칸 뒤 값을 꺼낸다	JSON 배열은 0부터 시작한다고 기억한다
INSERT와 REPLACE를 혼동	추가가 안 되거나 수정이 안 된다	없는 경로 추가는 INSERT, 있는 경로 수정은 REPLACE
JSON_SEARCH 결과를 값으로 착각	경로 문자열을 실제 데이터처럼 사용한다	SEARCH는 위치, EXTRACT는 값이라는 역할을 분리한다
배열 삭제 후 인덱스 변화를 무시	다음 삭제 대상이 밀려서 잘못 지워진다	뒤 인덱스부터 삭제하거나 삭제 후 다시 검색한다

9 함수 선택 기준

1

만들기테이블 컬럼을 JSON으로 포장해야 하면 JSON_OBJECT를 쓴다.

2

검증하기외부 문자열이나 직접 만든 JSON은 JSON_VALID로 먼저 확인한다.

3

찾기값의 위치를 모르면 JSON_SEARCH로 경로를 찾는다.

4

꺼내기·수정하기경로를 알면 JSON_EXTRACT, JSON_INSERT, JSON_REPLACE, JSON_REMOVE를 목적에 맞게 고른다.

10 JSON path 실전 연습

JSON 함수에서 가장 중요한 감각은 path를 정확히 읽는 것이다. 같은 JSON이라도 객체 키로 내려가는지, 배열 인덱스로 들어가는지에 따라 경로가 완전히 달라진다.

-- 예제 JSON { "usertbl": [ {"name": "임재범", "height": 182}, {"name": "이승기", "height": 182}, {"name": "성시경", "height": 186} ] }

원하는 값	JSON path	이유
첫 번째 사람 객체	$.usertbl[0]	배열은 0부터 시작하므로 첫 요소는 [0]
두 번째 사람 이름	$.usertbl[1].name	두 번째 객체로 들어간 뒤 name 키 선택
세 번째 사람 키	$.usertbl[2].height	세 번째 객체의 height 값 선택
전체 배열	$.usertbl	루트 객체의 usertbl 키 전체 선택

path가 헷갈리면 루트에서부터 하나씩 읽어 내려가면 된다. "$에서 usertbl로, 배열 2번으로, name으로"처럼 말로 풀어 읽으면 실수가 줄어든다.

11 저장할 때와 조회할 때의 기준

MySQL에서 JSON을 다룰 때 모든 데이터를 JSON 컬럼에 넣는 것이 정답은 아니다. 검색과 조인이 자주 필요한 값은 일반 컬럼으로 두고, 부가 정보처럼 구조가 자주 바뀌는 값만 JSON으로 두는 편이 관리하기 쉽다.

일반 컬럼이 적합한 값

회원명, 키, 주문일, 상태처럼 WHERE, JOIN, ORDER BY에 자주 쓰는 값. 인덱스와 제약조건을 명확히 걸기 좋다.

JSON이 적합한 값

옵션, 설정값, 외부 API 원문, 항목 수가 자주 바뀌는 부가 속성. 스키마 변경 부담을 줄일 수 있다.

1

검색 조건이면 컬럼자주 필터링하는 값은 일반 컬럼으로 꺼내 둔다.

2

구조가 바뀌면 JSON속성 목록이 자주 바뀌는 값은 JSON으로 보관하는 편이 유연하다.

3

조회 전 VALID 확인문자열로 받은 값은 JSON_VALID로 먼저 검사한다.

4

수정 함수 역할 분리추가는 INSERT, 교체는 REPLACE, 삭제는 REMOVE로 목적을 분명히 한다.

JSON은 편하지만 무조건 좋은 저장 방식은 아니다. 자주 검색하는 값까지 JSON 안에만 넣으면 쿼리, 인덱스, 검증 로직이 복잡해진다.

-- 자주 검색하는 값은 일반 컬럼 name, height, status -- 구조가 자주 바뀌는 부가 정보는 JSON profile_options, api_payload, extra_meta

JSON 컬럼을 쓰더라도 자주 꺼내는 경로는 문서화해 두는 것이 좋다. 같은 값을 여러 화면에서 서로 다른 path로 읽기 시작하면 유지보수가 급격히 어려워진다.

핵심 요약

JSON_OBJECT컬럼 값을 키-값 구조의 JSON 객체로 만든다.

JSON_VALID문자열이 올바른 JSON인지 먼저 검사한다.

JSON_SEARCH / EXTRACTSEARCH는 경로를 찾고, EXTRACT는 경로의 값을 꺼낸다.

INSERT / REPLACE / REMOVE없는 경로 추가, 있는 경로 교체, 지정 경로 삭제로 역할을 나눠 쓴다.

JSON path$는 루트, .key는 객체 속성, [n]은 0부터 시작하는 배열 인덱스다.

Tags

#MySQL #JSON_OBJECT #JSON_VALID #JSON_SEARCH #JSON_EXTRACT #JSON_INSERT #JSON_REPLACE #JSON_REMOVE #JSON함수 #SQL #데이터베이스 #DB #웹개발 #티스토리

티스토리 태그 입력란 복사용

MySQL, JSON_OBJECT, JSON_VALID, JSON_SEARCH, JSON_EXTRACT, JSON_INSERT, JSON_REPLACE, JSON_REMOVE, JSON 함수, SQL, 데이터베이스, DB, 웹개발, 티스토리