[MySQL] Json - SELECT, INSERT, UPDATE, CREATE

# 생성
CREATE TABLE objectInfo ( 
     objectID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     param VARCHAR(128),    
     param2 JSON
);



# 저장
INSERT INTO objectInfo
SET param = '{"keep":23,"storage":"1"}', param2 = '{"keep":23,"storage":"1"}';
  ( OR )
INSERT INTO objectInfo
SET param = '{"keep":23,"storage":"1"}', param2 = JSON_OBJECT("keep", 23, "storage", "1");



TABLE : objectInfo

objectID	param	param2
1	{"storage":"1"}	{"storage":"1"}
2	{"keep":23,"storage":"1"}	{"keep":23,"storage":"1"}

# 검색
SELECT objectID, param FROM objectInfo;

objectID	param	param2
1	{"storage":"1"}	{"storage":"1"}
2	{"keep":23,"storage":"1"}	{"keep":23,"storage":"1"}

/*
  화살표(->)로 필드와 경로식을 구분. 
  param 컬럼에 있는 JSON 데이터에서 key 이름이 keep, storage 요소를 가리킨다.
  param->'$.keep' 문은 json_extract(param, '$.keep') 과 동일.
*/
SELECT objectID,
         param->'$.keep' AS keep,
         param->'$.storage' AS storage ,
         CAST(param->'$.storage' AS SIGNED) AS castIntStorage 
FROM objectInfo;

objectID	keep	storage	castIntStorage
1	(NULL)	1	1
2	23	1	1

# 수정
UPDATE는 JSON_SET(), JSON_REPLACE(), JSON_REMOVE() 함수 이용.

     JSON_SET(): 기존 값 수정하고 미존재 시 추가.
     JSON_REPLACE(): 기존 값 변경.
     JSON_REMOVE(): 기존 값 삭제. -> NULL.

문법 : UPDATE table_name  SET json_col = JSON_SET(json_col, '$.valueName1', UPPER(json_col->>'$.valueName1')),       json_col = JSON_REPLACE(json_col, '$.valueName2', int_col),       json_col = JSON_REMOVE(json_col, '$.valueName3'),       int_col = int_col + 1; UPDATE table_name SET json_col = JSON_REPLACE(                                     JSON_REMOVE(json_col, '$.valueName3'),                                     '$.valueName1', UPPER(json_col->>'$.valueName1'),                                     '$.valueName2', int_col),         int_col = int_col + 1;

UPDATE objectInfo
SET param2 = JSON_SET(info, "$.keep", 46) WHERE objectID = 1;

SELECT * from objectInfo where objectID = 1;

objectID	param	param2
1	{"keep":23,"storage":"1"}	{"keep":46,"storage":"1"}

# JSON 함수
JSON_ARRAY : JSON 배열 반환.

  SELECT JSON_ARRAY("apple", "car", "tv", "ios");

JSON_ARRAY("apple", "car", "tv", "ios")

["apple", "car", "tv", "ios"]

JSON_EXTRACT : JSON 경로와 일치하는 데이터 반환.

  SET @j = '[10, 20, [30, 40]]';
  SELECT JSON_EXTRACT(@j, '$[0]');

JSON_EXTRACT(@j, '$[0]')

10

# Array Select

DB 테이블 JSON 컬럼 :  json_info

{"info_list":[{"type":1,"time":1652961788},{"type":2,"time":1652961371},{"type":3,"time":1652961356}]}

SELECT JSON_EXTRACT(payment_list, '$.info_list[0].time');
>> 1652961788


# Data Select

DB 테이블 JSON 컬럼 :  json_info

{"category":2, "product_id":0,"quantity":50000}

SELECT JSON_EXTRACT( json_info, '$.category')
     ,JSON_EXTRACT( json_info, '$.product_id')
     ,JSON_EXTRACT( json_info, '$.quantity');
>> 2     0     50000




JSON_LENGTH : 크기.

DB 테이블 JSON 컬럼 :  json_info

{"info_list":[{"type":1,"time":1652961788},{"type":2,"time":1652961371},{"type":3,"time":1652961356}]}

# Array Size
SELECT JSON_LENGTH(json_info, '$.info_list');
>> 3
# Json Data Size
SELECT JSON_LENGTH(json_info);
>> 1




JSON_OBJECT : SELECT 결과를 JSON 형태로 변환해서 받아보고 싶을 때 사용.

  SELECT JSON_OBJECT("name","yundream", "age", 25, "address", "seoul");

JSON_OBJECT("name","yundream", "age", 25, "address", "seoul")

{"age": 25, "name": "yundream", "address": "seoul"}

JSON_SET : JSON 문서에 필드를 추가나 갱신 위해서 사용.

  SET @j = JSON_OBJECT("a", 1, "b", JSON_ARRAY(2,3));
  SELECT @j;

@j

{"a": 1, "b": [2, 3]}

  SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');

JSON_SET(@j, '$.a', 10, '$.c', '[true, false]')

{"a": 10, "b": [2, 3], "c": "[true, false]"}

JSON_INSERT : 새로운 필드만 추가.

  SELECT @j;

@j

{"a": 1, "b": [2, 3]}

  SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[treu, false]');

JSON_INSERT(@j, '$.a', 10, '$.c', '[treu, false]')

{"a": 1, "b": [2, 3], "c": "[treu, false]"}

JSON_REPLACE : 같은 필드 존재할 경우 변경. 존재하지 않는 필드는 무시.

  SELECT @j;

@j

{"a": 1, "b": [2, 3]}

  SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[treu, false]');

JSON_REPLACE(@j, '$.a', 10, '$.c', '[treu, false]')

{"a": 10, "b": [2, 3]}

JSON_VALID : JSON 문서 형식이 올바른지 검사.

  SELECT JSON_VALID('{"a": 1}');

JSON_VALID('{"a": 1}')

1

  SELECT JSON_VALID('hello');

JSON_VALID('hello')

0

JSON_STORAGE_SIZE & JSON_STORAGE_FREE : JSON 문서 저장하는데 사용된 바이트 수 반환.

  SELECT JSON_STORAGE_SIZE(param2) AS JsonSize FROM objectInfo;





출처 :
Mysql JSON - SELECT, INSERT, UPDATE, 색인 (joinc.co.kr)

Mysql JSON - SELECT, INSERT, UPDATE, 색인

[DB] MYSQL - JSON 이란? : 네이버 블로그 (naver.com)

 
 

[DB] MYSQL - JSON 이란?