Functions That Search JSON Values

datahub 같은 걸 mysql에 만들고 있는데 json 형태의 데이터가 꽤 많이 들어 간다. 이럴꺼면 elasticsearch나 mongodb 같은 걸 쓸걸 그랬나 싶지만 일단 mysql에서도 json 형태의 데이터 타입을 지원하고 검색 기능도 꽤 있다.

 

자꾸 까먹어서 정리한다.

 

 

MySQL은 JSON 데이터를 다루기 위한 강력한 검색 기능을 제공합니다. JSON 컬럼에서 데이터를 검색하거나 특정 조건에 따라 필터링할 때 유용하게 활용할 수 있습니다. MySQL 8.0 이상 버전에서는 JSON 검색 관련 함수들이 다양하게 지원됩니다.

1. 기본 개념

• JSON 데이터: MySQL에서 JSON 컬럼은 구조화된 데이터 형식을 저장하는 데 사용되며, SQL 명령을 사용해 쉽게 접근하고 조작할 수 있습니다.
• 검색 필요성: JSON 데이터는 중첩 구조를 가질 수 있어, 특정 값을 검색하거나 조건에 맞는 데이터를 찾는 데 특화된 기능이 필요합니다.

---

2. 주요 JSON 검색 함수

a. JSON_CONTAINS()

• JSON 배열이나 객체에서 특정 값을 포함하는지 확인합니다.
• 문법:

JSON_CONTAINS(target, candidate[, path])

• 예제:

SELECT JSON_CONTAINS('["apple", "banana", "cherry"]', '"banana"'); -- 결과: 1 (banana가 포함됨)

 

 

b. JSON_SEARCH()

• JSON 데이터에서 특정 값을 검색하여 해당 값의 경로를 반환합니다.
• 문법:

JSON_SEARCH(target, one_or_all, search_str[, escape_char[, path]])

• 옵션:
  • one_or_all: ONE 또는 ALL로 지정. ONE은 첫 번째 일치 항목, ALL은 모든 일치 항목 경로를 반환.
• 예제:

SELECT JSON_SEARCH('["apple", "banana", "cherry"]', 'one', 'banana'); -- 결과: '$[1]'

 

c. JSON_CONTAINS_PATH()

• JSON 문서에서 특정 경로가 존재하는지 확인합니다.
• 문법

JSON_CONTAINS_PATH(target, one_or_all, path[, path ...])

• 예제:

SELECT JSON_CONTAINS_PATH('{"a": 1, "b": 2}', 'one', '$.a'); -- 결과: 1 (경로 $.a가 존재함)

---

3. 활용 예시

JSON 배열에서 값 검색

SELECT * FROM my_table WHERE JSON_CONTAINS(tags, '"example"', '$');

 

JSON 객체에서 특정 키 검색

SELECT * FROM my_table WHERE JSON_SEARCH(data, 'one', 'target_value', NULL, '$.key');

 

JSON 경로의 존재 여부 확인

SELECT * FROM my_table WHERE JSON_CONTAINS_PATH(json_column, 'all', '$.key1', '$.key2');

---

4. 주요 사용 사례

• 동적 데이터 필터링: JSON 배열에 포함된 태그나 키워드를 기준으로 조건 검색.
• 중첩 데이터 접근: JSON 객체의 특정 키 또는 중첩 구조에 대해 값 확인.
• 데이터 유효성 확인: JSON 데이터의 특정 경로 존재 여부 확인.

---

5. 주의 사항

• 인덱스 사용 제한: JSON 함수는 인덱스를 바로 활용하지 못할 수 있습니다. 효율성을 위해 생성 컬럼을 활용하거나 JSON 데이터를 정규화해야 할 수도 있습니다.
• 성능 최적화: JSON 함수는 복잡한 데이터에서 성능 저하를 유발할 수 있으므로 필요한 데이터만 검색하도록 설계해야 합니다.

 

---

 

 

공식 사이트

MySQL :: MySQL 8.4 Reference Manual :: 14.17.3 Functions That Search JSON Values