[MySQL] JSON_CONTAINS, JSON_LENGTH 등 검사 함수 모음

MySQL에서 JSON 데이터를 다루다 보면 내부에 특정 값이 존재하는지 확인하거나, 배열/객체의 크기를 계산하는 일이 자주 발생한다. 이럴 때 사용하는 것이 JSON_CONTAINS, JSON_LENGTH, JSON_CONTAINS_PATH, JSON_TYPE 등의 검사 함수다.

1. JSON_CONTAINS: 값 포함 여부 확인

JSON_CONTAINS(target_json, candidate_json[, path])

• target_json에 candidate_json이 포함되어 있는지 여부 반환
• 반환값: 1 (존재), 0 (없음)

예제

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

SELECT JSON_CONTAINS('{"name": "철수", "age": 25}', '{"age": 25}');
-- 결과: 1

경로 지정 예제

SELECT JSON_CONTAINS('{"user": {"name": "영희"}}', '"영희"', '$.user.name');
-- 결과: 1

• JSON 경로를 명시하면 중첩 구조에서도 탐색 가능

반응형

2. JSON_CONTAINS_PATH: 경로 존재 여부 확인

JSON_CONTAINS_PATH(json_doc, 'one'|'all', path1[, path2, ...])

• 'one': 하나라도 존재하면 1
• 'all': 모두 존재해야 1

예제

SELECT JSON_CONTAINS_PATH('{"a":1, "b":2}', 'one', '$.a', '$.x');
-- 결과: 1

SELECT JSON_CONTAINS_PATH('{"a":1, "b":2}', 'all', '$.a', '$.x');
-- 결과: 0

3. JSON_LENGTH: 요소 개수 구하기

JSON_LENGTH(json_doc[, path])

• JSON 객체: key 개수
• JSON 배열: 요소 개수

예제

SELECT JSON_LENGTH('{"a":1, "b":2}');
-- 결과: 2

SELECT JSON_LENGTH('["x", "y", "z"]');
-- 결과: 3

경로 지정 예제

SELECT JSON_LENGTH('{"data": [1, 2, 3]}', '$.data');
-- 결과: 3

4. JSON_TYPE: 값의 타입 확인

JSON_TYPE(json_doc[, path])

• 결과: OBJECT, ARRAY, INTEGER, STRING, NULL 등

예제

SELECT JSON_TYPE('{"x": 1}', '$.x');
-- 결과: INTEGER

SELECT JSON_TYPE('[10, 20, 30]');
-- 결과: ARRAY

반응형

5. JSON_VALID: 유효한 JSON 여부 검사

SELECT JSON_VALID('{"a": 1}');       -- 결과: 1
SELECT JSON_VALID('"a": 1');         -- 결과: 0 (유효하지 않음)

• JSON 데이터 유효성 검증 시 사용
• INSERT 또는 UPDATE 전에 확인할 때 유용

6. 실무 활용 팁

특정 필드가 존재할 때만 SELECT

SELECT *
FROM users
WHERE JSON_CONTAINS_PATH(profile, 'one', '$.nickname');

• nickname 필드가 존재하는 사용자만 조회

배열 크기가 일정 이상인 데이터 필터링

SELECT *
FROM logs
WHERE JSON_LENGTH(tags) >= 3;

• tags 배열에 3개 이상 태그가 있는 로그만 조회

유효성 확인 후 삽입

INSERT INTO products (id, spec)
SELECT 1, '{"cpu": "i5"}'
WHERE JSON_VALID('{"cpu": "i5"}') = 1;

• JSON 형식이 맞을 때만 INSERT 수행

참고 문서

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