MySQL JSON: 키 값을 기반으로 개체 검색

MySQL 테이블은 종종 JSON 열을 사용하여 복잡한 데이터 구조를 저장합니다. JSON으로 작업할 때 일반적인 작업은 키-값 쌍을 기반으로 특정 개체를 검색하는 것입니다. 그러나 객체를 직접 찾는 것이 항상 간단하지는 않을 수 있습니다.

JSON 스키마 예:

다음 스키마를 고려하세요.

CREATE TABLE `fields` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

샘플 데이터 포함:

INSERT INTO `fields` (label, options) VALUES
(
  'My Field', 
  '[{"text": "Grass", "value": "1"}, {"text": "Synthetic (New Type - Soft)", "value": "2"}, {"text": "Synthetic  (Old Type - Hard)", "value": "3"}, {"text": "Gravel", "value": "5"}, {"text": "Clay", "value": "6"}, {"text": "Sand", "value": "7"}, {"text": "Grass/Synthetic Mix", "value": "8"}]'
);

문제:

해당 "값"이 "1"임을 알고 옵션 JSON 배열에서 문자열 "Grass"를 검색한다고 가정합니다. JSON_EXTRACT()만 사용하려면 쉽게 사용할 수 없는 배열의 인덱스가 필요합니다.

해결책 1: JSON_SEARCH() 및 끔찍한 문자열 조작 사용

이 접근 방식 JSON_SEARCH()를 결합하여 인덱스를 부분적으로 찾은 다음 문자열에서 원하지 않는 부분을 제거합니다.

SELECT
JSON_EXTRACT(`options`,CONCAT('$[',REPLACE(REPLACE(JSON_SEARCH(`options`, 'one', '1'), '"$[', ''), '].value"', ''), '].text'))
from `fields`;

해결책 2: JSON_TABLE() 사용

MySQL 8.0에서는 이 작업을 단순화하기 위해 JSON_TABLE()을 도입했습니다.

select field_options.* from fields cross join 
json_table(fields.options, 
'$[*]' columns(
  text text path '$.text',
  value text path '$.value'
 )
) as field_options 
where field_options.value = 1;

대체 접근 방식: 관계형 데이터 모델

이 예와 같이 더 간단한 데이터 구조의 경우 다음 열이 있는 관계형 테이블에 데이터를 저장하는 것이 좋습니다. 키와 값 쌍, 직접 검색 가능:

SELECT * FROM field_options WHERE value = '1';