2023-10-21 게시 됨2023-10-27 업데이트 됨Programming / Database13분안에 읽기 (약 1939 단어)

MariaDB 에는 보통 관계형 테이블에서 SQL을 이용해 JSON data를 사용하기 위해서 별도의 json type을 지원하고 있다.

MySQL / MariaDB json type

MariaDB 에서 일반 테이블에서 json을 지원하기 위해 json type을 지원하고 있다. 단 MySQL 의 json data type에 호환하기 위해서 MariaDB json type은 longtext 의 alias 로 제공한다.

https://mariadb.com/kb/en/json-data-type/
json이 sql 표준에 모순되어서 mariadb는 최소 성능에을 유지하는 벤치마크를 가지고 있다.
적절한 json 데이터를 유지하기 위해서 json_valid 함수를 check 구문에 사용할 수 있다. (v10.4.3)

JSON 데이터 형식에 대해서는 다양한 소개를 검색할 수 있다.

문자여로로 숫자, 문자, 객체, 날짜 및 자료의 배열 등의 형식을 표현할 수 있다.

https://docs.fileformat.com/ko/web/json/

MySQL / Oracle / MariaDB / PostgreSQL / MSSQL 등에서 Bigdata 분야에서 관심 받는 No-SQL의 자료 구조로 각광받고 있다. json은 1:n 의 관계에서 유동적인 자료를 처리하기 위해서 지원하는 추세이다.

MariaDB: json datatype 사용하기

1
2
3

> USE lecture;
> 
> SHOW VARIABLES LIKE 'version';

json datatype 을 가진 테이블 생성

json_test 테이블을 생성한다. JSON 데이터 타입으로 선언하면 내부적으로 longtext로 저장 됨

> create table json_test (id  int ,  data JSON );
> DESC json_test;

> SHOW CREATE TABLE json_test;

SHOW CREATE 결과는 아래와 같을 것이다.

CREATE TABLE `json_test` (
  `id` int(11) DEFAULT NULL,
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`data`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

테스트한 테이블을 제거

1	> DROP TABLE json_test;

json data validation

check() 검증 함수를 이용해서 insert하는 json data의 형식/유효성을 검증할 수 있다.

CREATE TABLE json_test (
  id  int ,  
  data JSON CHECK (JSON_VALID(data))
);

SHOW CREATE TABLE json_test;

show 결과는 아래 같다.

CREATE TABLE `json_test` (
  `id` int(11) DEFAULT NULL,
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`data`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

json 입력

json 데이터 입력해 확인해 보자. 먼저 문자열을 입력해보자.

1 2	-- ERROR 4025 (23000): CONSTRAINT INSERT INTO json_test VALUES (1, 'invalid');

json 형식이 아니기 때문에 ERROR 4025 에러가 발생한다.

json datetype 컬럼에 json 데이터 형식을 가진 문자열로 입력할 수 있다.

-- string 
INSERT INTO json_test VALUES(1, '{ "name": "그르렁", "age": 30 }');
INSERT INTO json_test VALUES (2, '{"id": 1, "name": "으르렁", "age": 29, "address": "서울시"}');
SELECT * FROM json_test;

select 한 결과는 json data 컬럼에 JSON Object 형식으로 저장됨을 확인할 수 있다.

1 2	{ "name": "그르렁", "age": 30 } {"id": 1, "name": "으르렁", "age": 29, "address": "서울시"}

또한 json datetype 컬럼에 json_object() 함수를 사용해 데이터를 입력할 수 있다.

1	json_object(key, value [,key, value])

json_object 로 입력하고 select 로 확인 해보자.

1
2
3

-- key, value, key, value 형태로 계속 입력된다.
> INSERT INTO json_test VALUES (3 , json_object('name' , '노르라' , 'Sex' , '남자' , 'age', 25, '전화' , '010-111-2222')) ;
> SELECT * FROM json_test;

문자열 JSON notation 형식으로 저장되는 data 에서 정보를 얻을 때는 JSON path 형식의 표현식을 사용한다.

json object의 값 얻기

JSON object 를 얻기위해서 다양한 JSON_ 형식의 함수를 지원한다. 함수들은 모둔 json path expression 을 사용해 json object 를 탐색한다. 아래는 대표적인 함수

- json_value(json_doc, path): READING SCALAR DATA
- json_array(json_doc, path): list 에서 JSON array 를 반화
- JSON_EXTRACT(json_doc, path[, path] ...) : return values
- JSON_QUERY(json_doc, path[, path] ...) : return json object, array, null (invalid json)

간단히 사용해 보자.

json_test 테이블에서 age 속성을 select 해보자

> SELECT JSON_VALUE(DATA, '$.age') AS age FROM json_test;
30
29
25

이번에는 name, age, 전화 속성만 추출해 보자.

> SELECT JSON_VALUE(DATA, '$.name') AS name,
	JSON_VALUE(DATA, '$.age') AS age,
	JSON_VALUE(DATA, '$.전화') AS 전화
FROM json_test;

"name"	"age"	"전화"
"그르렁"	"30"	\N
"으르렁"	"29"	\N
"노르라"	"25"	"010-111-2222"

JSON path expression

JSON 함수에서 JSON_Function(json_doc, path) 인자 path 는 json path expression 을 사용한다.

MariaDB jsonpath 는 Mysql 의 jsonpath 에 근접해 구현하고, 와일드카드 ** 스텝은 표준 보다는 MySQL에 가깝게 구현하고 있다.

path expression:

path: ['lax'] '$' [step]
  `[lax]` : path mode 가 lax 지원.
  `$` : 콘텍스트 아이템 시작. path는 항상 $로 시작함.
  `[step]` : Json Object 인 object, array , wild card 가 이어진다.

object member selector

. : json object 를 선택시.
.* : 해당 object의 모든것

array 요소 selector

[n] : n 번째 요소
[*] : 모든 요소

mariadb 10.9 이후

[-n] : 마지막에서 -n 번 인덱스
[last-n] : 마지막에서 n번째 까지
[M to N] : m 과 n 사이 인덱스

예를 들어보자,

1	$.name -- json객체의 name 속성을 지시한다.

배열 접근

1	$[5] -- 5 번재 요소

Object selector tutorial

json 객체가 있다.

SET @json='{
            "A": [0,
                  [1, 2, 3],
                  [4, 5, 6],
                  "seven",
                   0.8,
                   true,
                   false,
                   "eleven",
                  [12, [13, 14], {"key1":"value1"},[15]],
                  true],
            "B": {"C": 1},
            "D": 2
           }';

이 json 객체를 SQL 과 json 함수를 이용해 탐색하는 예이다.

SELECT JSON_EXTRACT(@json, '$.A');             -- A객체
SELECT JSON_EXTRACT(@JSON, '$.A[2]');           -- A객체의 2 번째 요소
SELECT JSON_EXTRACT(@json, '$.A[-8][1]');

SELECT JSON_EXTRACT(@json, '$.B');

SELECT JSON_EXTRACT(@json, '$.A[last-1]');
SELECT JSON_EXTRACT(@json, '$.A[last-1][2]');
SELECT JSON_EXTRACT(@json, '$.A[last-7][1]');

SELECT JSON_EXTRACT(@json, '$.A[3 to 5]');

SELECT JSON_EXTRACT(@json, '$.A[3 to last]');

-- wildcard step '**'

SELECT JSON_EXTRACT(@JSON, '$**[1]');

SELECT JSON_EXTRACT(@JSON, '$**.C');

json functions 사용

테스트로 생성한 json_test 테이블을 사용해서 많이 사용하는 json 함수를 사용해 보자.

JSON_VALUE:

1	JSON_VALUE(json_doc, path) : path 에 있는 스칼라 값을 반환

1	select json_value('{"key1":123}', '$.key1');

객체 안의 배열과 스칼라 값 객체의 json_valie() 결과

-- key1 속성에 배열과 스칼라 값 객체의 json_valie() 결과
select json_value('{"key1": [1,2,3], "key1":123}', '$.key1');
123

select json_value('{"key1": [1,2,3], "key1":123}', '$.*');

배열은 [index[last]] 형식으로 탐색

1	select JSON_VALUE('[{"key1": [1,2,3], "key1":123}]', '$[0].key1');

JSON_EXTRACT:

1	JSON_EXTRACT(json_doc, path[, path] ...) : path에 일치하는 모든 return values. null (invalid)

SET @json = '[1, 2, [3, 4]]';
SELECT JSON_EXTRACT(@json, '$[1]');
SELECT JSON_EXTRACT(@json, '$[last]');


SELECT id, JSON_EXTRACT(DATA, '$**.age') FROM json_test;
SELECT id, JSON_EXTRACT(DATA, '$**.전화') FROM json_test;

SELECT id, JSON_EXTRACT(DATA, '$.name') FROM json_test;
SELECT id, JSON_EXTRACT(DATA, '$.age') FROM json_test;
SELECT id, JSON_EXTRACT(DATA, '$.전화') FROM json_test;

constraint error 는 보자, 새로운 객체를 추가한다.

1
2
3

INSERT INTO json_test VALUES(4, '{ "name": "가로미", "age": 29 }');
INSERT INTO json_test VALUES(5, '{ "name": "가로미", "age": 29 }');
SELECT * FROM json_test;

기존 4번 인덱스가 있어서 다음은 constraint error 를 발생한다.

1 2	-- constraint error: INSERT INTO json_test VALUES(4, '{ "name": "가로미", "age": 29 }, { "name": "그러릿", "age": 21, "address": "경기도 성남시" }');

json_array()

JSON array 를 생성해 준다.

1	json_array(): list 에서 JSON array 를 반화

여러 데이터를 json_array로 배열로 입력한다.

INSERT INTO json_test VALUES(7, JSON_ARRAY(129, 3.1416, 'My name is "Foo"', NULL) );
INSERT INTO json_test VALUES(8, JSON_ARRAY( '{ "name": "크르렁", "age": 30 }', '{ "name": "갸르릉", "age": 21 }') );

INSERT INTO json_test VALUES(6, '{ "address" : [ { "name": "고로릿", "age": 20 }, { "name": "그러릿", "age": 21 }] } ');
INSERT INTO json_test VALUES(8, JSON_OBJECT( "address", JSON_ARRAY( '{ "name": "크르렁", "age": 30 }', '{ "name": "갸르릉", "age": 21 }') ) );

입력된 결과를 쿼리한다.

SELECT * FROM json_test;

SELECT id, JSON_EXTRACT(DATA, '$.address[0]') FROM json_test;

SELECT id, JSON_EXTRACT(DATA, '$.address[-1]') FROM json_test;


DROP TABLE json_test;

JSON_QUERY()

json_query 는 json 객체 형식의 표현을 입력해서 사용한다.

1 2	-- JSON_QUERY(json_doc, path -- JSON_QUERY(json_doc, path[, path] ...) : path의 json object, array를 반환, null (invalid json)

-- key1 객체
select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1');

-- key1 객체의 배열
select json_query('{"key1":123, "key1": [1,2,3]}', '$.key1');

json_table()

mariadb 10.6 이후.
json data 를 sql table 로 변환한다.

@json 변수에 json data 가 선언되고, sql 문에서 json_table 에 의해 관계형 테이블로 탐색된다.

set @json='
[
  {"name":"Laptop", "color":"black", "price":"1000"},
  {"name":"Jeans",  "color":"blue"}
]';

select * from json_table(@json, '$[*]' 
  columns(
   name  varchar(10) path '$.name', 
   color varchar(10) path '$.color',
   price decimal(8,2) path '$.price' ) 
) as jt;

SET 과 json 형식

SET 구문을 사용해서 json 변수를 선언하고 SQL 에서 사용할 수 있다.

1
2
3

SET @json = '{"key1":"60\\" Table", "key2":"1"}';

SELECT JSON_VALUE(@json,'$.key1') AS Name , json_value(@json,'$.key2') as ID;

Swallow Clone 에서 branch를 사용하자.

함께 보면 유용한 git 명령