HelloCho

[MariaDB] Json Data 검색하기 (Json Data Search) 본문

MariaDB

[MariaDB] Json Data 검색하기 (Json Data Search)

쵸송 2022. 5. 24. 18:06

- Create Table

CREATE TABLE `tblDataTest` (
  `seq` bigint(20) NOT NULL AUTO_INCREMENT,
  `data` varchar(2048) NOT NULL DEFAULT '',
);

- Insert Data

insert into tblDataTest(`seq`, `data`)
values(1, '{"id":1,"level":1,"players":[123, 456]}'),
(3, '{"id":2,"level":2,"players":[789, 1112]}'),
(2, '{"id":2,"level":2,"players":[1112, 123]}');

- Result

seq data
1 {"id":1,"level":1,"players":[123, 456]}
2 {"id":2,"level":2,"players":[789, 1112]}
3 {"id":3,"level":3,"players":[1112, 123]}

- id 값이 1인 데이터 검색

select * from tblDataTest where json_value(`data`, '$.id') = 1;

- players 배열 0번에 1112 값인 데이터 검색

select * from tblDataTest where json_value(`data`, '$.players[0]') = 1112;

- players 배열에서 1112 값 일치하는 데이터 검색

select *
from tblDataTest
where json_search(JSON_EXTRACT(`data`, '$.players'), 'one', 1112) is not null;

 

 

Comments