MYSQL JSON¶

중요

이 문서에서 다루는 JSON 관련 함수는 Mysql 5.7.29 이상에서 테스트되었습니다.

JSON Column 만들기
JSON Insert
JSON 속성 Update
JSON 속성 Delete
JSON 속성 조회
JSON 속성으로 찾기
JSON 속성으로 그룹화 하기
JSON 속성으로 정렬

JSON Column 만들기 ¶

JSON 데이터를 사용하기 위해서는 테이블의 컬럼을 JSON으로 만들어야합니다.

CREATE TABLE `test_json` (
    `idx` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'pKey',
    `doc` JSON NULL COMMENT '데이타',
    `status` VARCHAR(10) NOT NULL DEFAULT 'ready' COMMENT '상태' COLLATE 'utf8_general_ci',
    `created_at` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '등록일시',
    PRIMARY KEY (`idx`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

JSON Insert ¶

JSON 컬럼에 데이터를 삽입할 때는 쿼리빌더의 insert()를 사용하며 데이터는 json_encode()로 변환합니다.

$data = [
    'name' => 'test'
    , 'birth' => '2010-10-01'
];

$this->qb
    ->set('doc', json_encod($data))
    ->insert('test_json')
    ->exec();

/* test_json.doc 컬럼

    {"name":"test", "birth":"2010-10-01"}
*/

JSON 속성 Update ¶

JSON 컬럼의 속성별 데이터를 업데이트할 때는 mysql 함수 JSON_SET()을 사용합니다.

/* test_json.doc 컬럼
    {"name":"test", "birth":"2010-10-01"}
*/

$this->qb
    ->set('doc', "JSON_SET(doc, '$.name', 'test2')", false)
    ->where('idx', 1)
    ->update('test_json')
    ->exec();

/* test_json.doc 컬럼
    {"name":"test2", "birth":"2010-10-01"}
*/

주석

Mysql JSON 속성 지정 방법

$ => this , $.name => this.name , $.addr[0] => this.addr[0]

JSON 개체에 속성이 없는 경우 지정한 속성이 JSON 개체에 추가됩니다.

/* test_json.doc 컬럼
    {"name":"test", "birth":"2010-10-01"}
*/

$this->qb
    ->set('doc', "JSON_SET(doc, '$.addr', '주소')", false)
    ->where('idx', 1)
    ->update('test_json')
    ->exec();

/* test_json.doc 컬럼
    {"name":"test2", "birth":"2010-10-01", "addr":"주소"}
*/

기존 속성이 배열이 아닌 경우 배열로 자동 변환된 후 값을 추가합니다.

/* test_json.doc 컬럼
    {"name":"test", "birth":"2010-10-01"}
*/

$this->qb
    ->set('doc', "JSON_SET(doc, '$.addr[1]', '주소2')", false)
    ->where('idx', 1)
    ->update('test_json')
    ->exec();

/* test_json.doc 컬럼
    {"name":"test2", "birth":"2010-10-01", "addr":["주소", "주소2"]}
*/

JSON 배열의 보다 큰 index를 지정하면 새로운 값으로 JSON 배열이 확장됩니다.

/* test_json.doc 컬럼
    {"name":"test", "birth":"2010-10-01"}
*/

$this->qb
    ->set('doc', "JSON_SET(doc, '$.addr[100]', '주소100')", false)
    ->where('idx', 1)
    ->update('test_json')
    ->exec();

/* test_json.doc 컬럼
    {"name":"test2", "birth":"2010-10-01", "addr":["주소", "주소2", "주소100"]}
*/

JSON 속성 Delete ¶

JSON 컬럼의 속성 삭제는 mysql 함수 JSON_REMOVE()를 사용합니다.

/* test_json.doc 컬럼
    {"name":"test2", "birth":"2010-10-01", "addr":["주소", "주소2", "주소100"]}
*/

$this->qb
    ->set('doc', "JSON_REMOVE(doc, '$.birth')", false)
    ->where('idx', 1)
    ->update('test_json')
    ->exec();

/* test_json.doc 컬럼
    {"name":"test", "addr":["주소", "주소2", "주소100"]}
*/

JSON 배열의 원소를 삭제합니다.

/* test_json.doc 컬럼
    {"name":"test2", "addr":["주소", "주소2", "주소100"]}
*/

$this->qb
    ->set('doc', "JSON_REMOVE(doc, '$.addr[1]')", false)
    ->where('idx', 1)
    ->update('test_json')
    ->exec();

/* test_json.doc 컬럼
    {"name":"test", "addr":["주소", "주소100"]}
*/

JSON 속성 조회 ¶

JSON 컬럼의 속성을 조회 할 때는 select()를 사용합니다.

/* test_json.doc 컬럼
    {"name":"test2", "birth":"2010-10-01", "addr":["주소", "주소2", "주소100"]}
*/

$row = $this->qb
    ->select("doc->>'$.name' AS name", false)
    ->from('test_json')
    ->where('idx', 1)
    ->exec()
    ->getRowArray();

/* $row
    ['name' => 'test']
*/

주석

Select JSON 속성 지정 방법

doc->>'$' => doc , doc->>'$.name' => this.name , doc->>'$.addr[0]' => this.addr[0]

JSON 속성으로 찾기 ¶

JSON 컬럼의 속성을 이용하여 검색할 때는 where()를 사용합니다.

/* test_json.doc 컬럼
    {"name":"test2", "birth":"2010-10-01", "addr":["주소", "주소2", "주소100"]}
*/

$row = $this->qb
    ->select("doc->>'$.birth' AS birth", false)
    ->from('test_json')
    ->where("doc->>'$.name'", 'test', false)
    ->exec()
    ->getResultRow();

/* $row
    ['birth' => '2010-10-01']
*/

like()를 사용하여 유사한 데이터 검색도 가능합니다.

/* test_json.doc 컬럼
    {"name":"test2", "birth":"2010-10-01", "addr":["주소", "주소2", "주소100"]}
*/

$row = $this->qb
    ->select("doc->>'$.birth' AS birth", false)
    ->from('test_json')
    ->like("doc->>'$.name'", 't', 'both', false)
    ->exec()
    ->getResultRow();

/* $row
    ['birth' => '2010-10-01']
*/

주석

both => '%t%', after => 't%', before => '%t'

JSON 속성으로 그룹화 하기 ¶

groupBy()를 사용하여 JSON 컬럼의 속성을 그룹화할 수 있습니다.

/* test_json.doc 컬럼
    {"name":"test2", "birth":"2010-10-01", "addr":["주소", "주소2", "주소100"]}
*/

$row = $this->qb
    ->select("doc->>'$.name' AS name", false)
    ->select("COUNT(*) AS cnt", false)
    ->from('test_json')
    ->groupBy("doc->>'$.name'", false)
    ->exec()
    ->getResultRow();

/* $row
    ['name' => 'test', 'cnt' => 1]
*/

JSON 속성으로 정렬 ¶

orderBy()를 사용하여 JSON 컬럼의 속성으로 정렬할 수 있습니다.

$row = $this->qb
    ->select("doc->>'$.name' AS name", false)
    ->from('test_json')
    ->orderBy("doc->>'$.name'", 'asc', false)
    ->exec()
    ->getResultRow();

주석

asc => 오름차순, desc => 내림차순