- 주어진 JSON 값이 유효한 JSON 형식인지 확인하는 데 사용됩니다. JSON 형식의 데이터를 다룰 때 유효성 검사를 수행하여 문제가 발생하는 것을 미리 방지할 수 있습니다. - 유효하지 않으면(0), 유효하면(1), NULL의 값이면(NULL)의 값을 반환합니다.
💡 사용예시
- JSON_VALID() 함수를 이용하여 텍스트를 확인하여 JSON 함수인지 여부를 반환합니다.
💡 JSON_PRETTY(json_val) - JSON 문서를 사람이 읽을 수 있는 형식으로 변환합니다. 이는 주로 들여 쓰기를 통해 JSON 문서의 구조를 명확하게 보여주는 데 사용됩니다. - 이 함수는 JSON 형식의 데이터를 다룰 때, 데이터의 구조와 내용을 한눈에 이해하기 쉽게 표현하고자 할 때 유용합니다.
💡 사용 예시
- JSON_PRETTY() 함수를 이용하여 문자열(텍스트)로 구성된 JSON을 실제 JSON 구조로 반환해 줍니다.
- 문자열 형태가 아닌 키-값 쌍의 JSON 객체를 생성합니다. 이 함수는 키와 값의 쌍을 인수로 받아 JSON 객체를 생성하며, 이때 키는 문자열이어야 합니다.
💡 사용예시
- JSON_OBJECT() 함수를 이용하여서 키가 “addr”이고 값이 “부산”인 Object와 키가 “age”이고 값이 38인 remark라는 JSON 타입의 컬럼의 데이터를 변경합니다.
UPDATE EMP SET remark = JSON_OBJECT("addr","부산", "age", 38) WHERE id = 1;
4. JSON_ARRAY()
💡 JSON_ARRAY([val[, val] ...])
- 입력된 값을 JSON 배열로 반환합니다. 입력된 값은 컴마(,)로 구분되며, 이는 JSON 배열의 각 요소를 나타냅니다. - 이 함수는 보통 여러 개의 값을 하나의 JSON 배열로 묶어 주는 데 사용됩니다.
💡 사용예시
- JSON Object 형태를 가진 Object를 JSON Array형태로 합치는 예시입니다.
UPDATE EMP
SET remark = JSON_OBJECT("addr","대구", "age", 29,
"family", JSON_ARRAY(
JSON_OBJECT("name", "lee", "relation", "모"),
JSON_OBJECT("name", "kim", "relation", "부")
)
) WHERE id = 3;
4) JSON 데이터 함수 활용 -2 : JSON 요소 조회
1. 인라인 패스(->, ->>) 조회
💡 인라인 패스(->, ->>) 조회
- JSON 데이터에서 특정 요소를 조회하는 방법입니다. ->, ->> 연산자를 활용하여 JSON내의 특정 요소를 조회합니다.
연산자
설명
반환 값
예시 값
->
JSON 데이터에서 특정 값을 조회
JSON 형태
“부산”
->>
JSON 데이터에서 특정 값을 조회
일반 텍스트 형태
부산
💡 사용예시
- 인라인 패스를 이용하여 remark라는 JSON 컬럼 내에서 조회를 수행합니다. - ->를 이용하여 JSON 내의 키가 “addr”인 데이터를 탐색하여 값을 조회해 옵니다.(JSON 형태로 반환받습니다.) - ->>를 이용하여 JSON 내의 키가 “addr”인 데이터를 탐색하여 값을 조회해 옵니다.(일반 텍스트 형태로 반환받습니다.)
SELECT remark
, remark -> '$.addr'
, remark ->> '$.addr'
FROM EMP
WHERE remark IS NOT NULL;
2. JSON_VALUE
💡 JSON_VALUE(json_doc, path)
- JSON 객체에 특정 값을 추출하는 함수입니다. 이는 JSON 문서(json_doc)와 JSON 경로(path)를 인자로 받습니다. - 인자 중 JSON 경로(path)를 통해 지정된 위치의 값을 반환하며, ‘일반 텍스트 형태’로 반환해 줍니다.
- JSON_SEARCH 함수를 이용하여 위치한 경로를 반환받는 사용예시입니다. 해당 값이 존재하지 않는다면 NULL 값을 반환합니다. - 첫 번째 인자로는 검색하려는 JSON이 포함된 컬럼을 참조하였고, 두 번째로는 가장 먼저 찾게 된 경로를 반환해 주는 ‘one’ 인자를 참조하였고, 세 번째로는 검색하려는 값을 참조하였습니다.
SELECT remark
, JSON_SEARCH(remark, 'one', '부산')
, JSON_SEARCH(remark, 'one', 'kim')
, JSON_VALUE(remark, '$.family[1].name')
FROM EMP
WHERE remark IS NOT NULL;
5. JSON_CONTAINS
💡 JSON_CONTAINS(json_doc, val, path)
- JSON 객체 내에 특정 값이 존재하는지 확인하는 함수입니다. 이는 JSON 문서, 확인하고자 하는 값, 그리고 확인하고자 하는 JSON 경로를 인자로 받습니다. - 해당 값이 JSON 객체 내에 존재하면 1을 반환하고, 그렇지 않으면 0을 반환합니다.
💡 사용예시
- JSON_CONTAINS 함수를 이용하여 JSON 데이터 컬럼인 remark 내에서 특정 값이 존재하는지 확인하는 사용예시입니다. - 아래 예시에서는 '부산'이라는 값이 존재하는지 확인하고 있습니다.
SELECT remark
, JSON_CONTAINS(remark, '"부산"', '$.addr')
, JSON_CONTAINS(remark, '38', '$.age')
FROM EMP
WHERE remark IS NOT NULL;
JSON_CONTAINS() 활용예시
💡 JSON_CONTAINS() 활용예시
- SELECT 절 내에서 보다 조건절 내에서 JSON_CONTAINS() 함수는 자주 이용된다고 합니다. - JSON 내에 찾으려는 값이 존재하는지 여부를 통해 조건을 성립할 수 있습니다.
SELECT * FROM EMP WHERE JSON_CONTAINS(remark, '"부산"', '$.addr')
5) JSON 데이터 함수 활용 -3 : JSON 정보 조회
1. JSON_TYPE
💡 JSON_TYPE(json_val) - JSON 객체 내에서 특정 값의 데이터 유형을 확인하는 함수입니다. 이 함수는 JSON 값(json_val)을 인자로 받습니다.
- 반환되는 형태는 해당 값의 데이터 유형을 문자열로 반환합니다. - 예를 들어, JSON 값이 TRUE일 경우 'BOOLEAN', JSON 값이 숫자일 경우 'INTEGER' 또는 'DOUBLE', JSON 값이 문자열일 경우 'STRING', JSON 값이 배열일 경우 'ARRAY' 등을 반환합니다.
💡 사용예시
- JSON_TYPE 함수를 이용하여 JSON 데이터의 특정 키의 값의 데이터 유형을 확인하는 사용예시입니다. - JSON 내의 addr 키 값의 데이터 타입을 조회하여 STRING을 반환하였습니다. - JSON 내의 age 키 값의 데이터 타입을 조회하여 INTEGER를 반환하였습니다.
SELECT JSON_TYPE(remark -> '$.addr')
, JSON_TYPE(remark -> '$.age')
FROM EMP
WHERE remark IS NOT NULL;
2. JSON_LENGTH
💡 JSON_LENGTH(json_doc[, path])
- JSON 객체나 배열의 길이(요소의 수)를 반환하는 함수입니다. 이 함수는 JSON 문서(json_doc)와 선택적으로 경로(path)를 인수로 받습니다. - 만약 경로가 지정되면, 그 경로에 있는 JSON 배열이나 객체의 길이를 반환합니다. 경로가 지정되지 않으면, 최상위 레벨의 JSON 문서의 길이를 반환합니다.
💡 사용 예시
- 해당 예시에서는 remark에 대한 JSON 타입에 대한 JSON 길이를 반환합니다. - 즉, 상위 JSON 내에 key-value로 구성된 개수를 반환합니다.
SELECT remark
, JSON_LENGTH(remark)
FROM EMP
WHERE remark IS NOT NULL;
3. JSON_DEPTH
💡 JSON_DEPTH(json_doc)
- 입력한 JSON 문서의 최대 깊이를 반환하는 함수입니다. 이 함수는 JSON 문서(json_doc)를 인자로 받습니다. - JSON 문서의 깊이는 최상위 레벨에서 시작하여 JSON 배열이나 객체가 중첩되는 레벨까지 계산됩니다. 최상위 레벨은 깊이 1로 계산되며, 그 아래 각각의 레벨은 추가로 1씩 깊이가 늘어납니다.
💡 사용 예시 - JSON_DEPTH 함수를 이용하여 JSON 데이터의 최대 깊이를 계산하는 사용예시입니다.
SELECT remark
, JSON_LENGTH(remark)
, JSON_DEPTH(remark)
FROM EMP
WHERE remark IS NOT NULL;
4. JSON_KEYS
💡 JSON_KEYS(json_doc[, path])
- JSON 객체의 모든 키를 JSON 배열로 반환하는 함수입니다. 이 함수는 JSON 문서(json_doc)와 선택적으로 JSON 경로(path)를 인자로 받습니다. - 만약 경로가 지정되면, 해당 경로에 있는 JSON 객체의 모든 키를 반환합니다. 경로가 지정되지 않으면, 최상위 레벨의 JSON 객체의 모든 키를 반환합니다. - 반환된 키의 순서는 JSON 객체 내에서의 원래 순서와 같습니다.
💡 사용 예시 - JSON_KEYS 함수를 이용하여 JSON 데이터의 모든 키를 반환하는 사용예시입니다.
SELECT remark
, JSON_LENGTH(remark)
, JSON_DEPTH(remark)
, JSON_KEYS(remark)
FROM EMP
WHERE remark IS NOT NULL;
5. JSON_STORAGE_FREE, JSON_STORAGE_SIZE
💡 JSON_STORAGE_FREE(json_doc)
- JSON 문서(json_doc)에서 사용되지 않는 공간, 즉, 할당되었지만 현재 사용되지 않는 저장 공간의 크기를 바이트 단위로 반환합니다. 이 함수는 주로 JSON 문서 내의 불필요한 공간을 관리하거나 최적화를 위해 사용됩니다.
💡 JSON_STORAGE_SIZE(json_doc)
- JSON 문서(json_doc)가 실제로 사용하는 저장 공간의 크기를 바이트 단위로 반환합니다. - 이 함수는 JSON 문서의 저장 공간 사용량을 확인하거나 데이터 용량을 관리하는데 유용합니다.
💡 사용예시
- 해당 예시에서는 JSON 데이터가 차지하는 데이터 크기와 JSON 데이터의 여유 공간을 조회합니다. (데이터가 있으면 항상 0을 반환합니다)
SELECT remark
, JSON_STORAGE_FREE(remark)
, JSON_STORAGE_SIZE(remark)
, LENGTH(remark)
FROM EMP
WHERE remark IS NOT NULL;
사용예시 응용
💡 사용예시 응용
- UPDATE 문을 수행하여서, “부산광역시”라는 값을 “부산”으로 JSON 데이터를 수정하였습니다. - 수정 이후에 JSON_STORAGE_FREE를 확인하였을 때 “광역시”라는 값을 제외하였기에 JSON 데이터가 차지하는 데이터 크기가 커졌습니다.
UPDATE EMP
SET remark = JSON_SET(remark, '$.addr', '부산')
WHERE id = 1;
SELECT remark
, JSON_STORAGE_FREE(remark)
, JSON_STORAGE_SIZE(remark)
, LENGTH(remark)
FROM EMP
WHERE remark IS NOT NULL;
6) JSON 데이터 함수 활용 -4 : JSON 요소 조작
1. JSON_REPLACE
💡 JSON_REPLACE(json_doc, path, val[, path, val] ... ) - JSON 객체 내에서 특정 값을 다른 값으로 대체하는 함수입니다. 이 함수는 JSON 문서(json_doc)와 경로(path), 그리고 대체할 값을 인자로 받습니다. - 경로를 통해 지정된 위치의 값이 존재할 경우 그 값을 대체하며, 그렇지 않으면 원래의 JSON 문서를 그대로 반환합니다. - 이 함수는 보통 JSON 객체의 특정 값을 수정할 때 사용됩니다.
💡 사용 예시
- 해당 예시에서는 JSON 내의 path 값을 찾아서 값을 변경하는 함수입니다.
SELECT remark
, JSON_REPLACE(remark, '$.age', 1)
FROM EMP
WHERE remark ->> '$.age' > 10;
- JSON 객체 내에 새로운 값을 삽입하는 함수입니다. 이 함수는 JSON 문서(json_doc), 삽입할 경로(path), 그리고 삽입할 값을 인자로 받습니다. - 경로를 통해 지정된 위치에 값이 존재하지 않을 경우 새로운 값을 삽입하며, 그렇지 않으면 원래의 JSON 문서를 그대로 반환합니다. - 이 함수는 보통 JSON 객체에 새로운 값을 추가할 때 사용됩니다.
💡 사용예시
- 해당 사용예시에서는 JSON 데이터 내에 path 값을 찾아서 값을 추가하는 함수입니다.
SELECT remark
, JSON_INSERT(remark, '$.hobby', "book")
FROM EMP
WHERE remark IS NOT NULL
3. JSON_SET
💡 JSON_SET(json_doc, path, val[, path, val] ...)
- JSON 객체 내에서 특정 값을 다른 값으로 설정하는 함수입니다. 이 함수는 JSON 문서(json_doc), 설정할 경로(path), 그리고 설정할 값을 인자로 받습니다. - 경로를 통해 지정된 위치에 값이 존재하면 그 값을 대체하고, 그렇지 않으면 새로운 값을 추가합니다. - 이 함수는 보통 JSON 객체의 특정 값을 수정하거나 새로운 값을 추가할 때 사용됩니다.
💡 사용 예시
- 해당 예시에서는 JSON 데이터 내에서 path를 지정하여서 값을 변경합니다..
SELECT remark
, JSON_REMOVE(remark, '$.family[0]')
FROM EMP
WHERE remark IS NOT NULL;
4. JSON_REMOVE
💡 JSON_REMOVE(json_doc, path[, path] ...)
- JSON 객체에서 특정 값을 제거하는 함수입니다. 이 함수는 JSON 문서(json_doc)와 제거할 경로(path)를 인자로 받습니다.
- 경로를 통해 지정된 위치의 값을 제거하며, JSON 문서에서 그 경로가 존재하지 않으면 원래의 JSON 문서를 그대로 반환합니다. - 이 함수는 보통 JSON 객체에서 불필요한 값을 제거할 때 사용됩니다.
💡 사용예시
- 해당 예시에서는 JSON 데이터 내에서 path를 지정하여서 값을 제거합니다.
SELECT remark
, JSON_REMOVE(remark, '$.family[0]')
FROM EMP
WHERE remark IS NOT NULL;
- 두 개 이상의 JSON 문서를 병합하는 함수입니다. 이 함수는 병합하려는 JSON 문서들(json_doc1, json_doc2,...)을 인자로 받습니다. - 같은 키를 가진 값들이 있는 경우, 뒤의 JSON 문서의 값이 아닌 모든 값을 유지합니다. 배열이나 객체는 병합되어 새로운 배열이나 객체를 형성합니다. - 이 함수는 여러 JSON 문서를 하나로 합칠 때, 중복된 키를 가진 값들을 모두 유지하고 싶을 때 사용됩니다.
💡 사용예시
- 해당 예시에서는 기존의 존재하는 JSON 데이터에 추가하여 재구성합니다.
SELECT remark
, JSON_MERGE_PRESERVE(remark,
JSON_OBJECT('id', id),
JSON_OBJECT('addr', '대구'))
FROM EMP
WHERE remark IS NOT NULL;
- 두 개 이상의 JSON 문서를 병합하는 함수입니다. 이 함수는 병합하려는 JSON 문서들(json_doc1, json_doc2,...)을 인자로 받습니다.
- 이 함수는 JSON Merge Patch 알고리즘을 사용하여 두 개의 JSON 문서를 병합합니다. 이 알고리즘은 두 번째 JSON 문서에서 발견되는 빈 객체를 첫 번째 JSON 문서에서 제거하고, 두 번째 JSON 문서에서 발견되는 다른 객체를 첫 번째 JSON 문서에 병합하거나 추가합니다. - 이 함수는 두 JSON 객체를 병합할 때 유용하며, 두 번째 JSON 객체의 값이 첫 번째 JSON 객체를 수정하거나 덮어쓰는 방식으로 동작합니다.
💡 사용 예시
- 해당 예시에서는 JSON_MERGE_PRESERVE와 JSON_MERGE_PATCH 함수와 비교하여 확인합니다. - JSON_MERGE_PRESERVE를 이용한 경우 최초 데이터의 경우 첫 번째 row를 확인하면 addr이라는 키에 addr을 추가하게 된 경우 “addr”: [”부산”, “대구”] 형태로 추가가 됩니다. - JSON_MERGE_PATCH를 이용한 경우 최초 데이터의 경우 첫 번째 row를 확인하면 addr이라는 키에 addr을 추가하게 된 경우 “addr”: “대구”로 덮어지게 됩니다
SELECT remark
, JSON_MERGE_PRESERVE(remark,
JSON_OBJECT('id', id),
JSON_OBJECT('addr', '대구'))
, JSON_MERGE_PATCH(remark,
JSON_OBJECT('id', id),
JSON_OBJECT('addr', '대구'))
FROM EMP
WHERE remark IS NOT NULL;
8. JSON_TABLE
💡 JSON_TABLE(json_doc, path COLUMNS column_list)
- JSON 문서를 관계형 테이블 형태로 변환하는 함수입니다. 이 함수는 JSON 문서(json_doc), 경로(path) 및 열 목록(column_list)을 인자로 받습니다.
- JSON 문서의 각 객체는 테이블의 한 행으로, 객체의 각 키는 열로 변환됩니다. 이는 복잡한 JSON 구조를 SQL에서 쉽게 조회하고 분석할 수 있게 만들어줍니다. - 이 함수는 JSON 데이터를 SQL 쿼리와 함께 사용하려는 경우에 유용합니다.
💡 사용예시 - 해당 예시에서는 JSON_TABLE() AS jt 형태로 JSON 데이터를 테이블 형태로 구성합니다.
- 첫번째 인자로는 JSON 데이터가 들어가고, 두 번째는 경로를 지정합니다. 마지막으로는 실제 JSON 데이터와 매핑한 컬럼을 구성하는 데 사용됩니다
SELECT *
FROM
JSON_TABLE('[{"id":1,"age":10,"addr":"서울"},{"id":2,"age":20,"addr":"부산"}]',
'$[*]'
COLUMNS(
j_id INT PATH '$.id',
j_age INT PATH '$.age',
j_addr VARCHAR(9) PATH '$.addr'
)
) AS jt;
사용예시 활용
💡 사용예시 활용
- 해당 사용 예시에서는 DEFAULT, ON EMPTY, ONERROR 속성을 활용합니다.
- j_age 컬럼의 데이터의 경우 존재하지 않을 경우 DEFAULT 값으로 999로 지정합니다. - j_addr 컬럼의 데이터의 경우 존재하지 않을 경우 DEFAULT 값으로 “비었음”값으로 지정하고, 에러가 발생하는 경우 “에러” 값으로 지정합니다. - j_no 컬럼의 경우 존재하지 않을 경우 DEFAULT 값으로 0으로 지정하고 에러가 발생하였을 경우 “-1” 값으로 지정합니다.
SELECT *
FROM JSON_TABLE(
'[{"id":1,"age":10,"addr":"서울","no": "xxxx"},{"id":2,"age":20,"addr":"부산", "no" : 2}]',
'$[*]'
COLUMNS(
j_rowNum for ordinality,
j_id INT PATH '$.id',
j_age INT PATH '$.age' DEFAULT '999' ON EMPTY,
j_addr VARCHAR(9) PATH '$.addr' DEFAULT '"비었음"' ON EMPTY DEFAULT '"에러!"' ON ERROR,
j_no INT PATH '$.no' DEFAULT '0' ON EMPTY DEFAULT '-1' ON ERROR
)
) jtbl
INNER JOIN EMP e
ON jtbl.j_id = e.id
;
9. JSON_ARRAYAGG()
💡 JSON_ARRAYAGG(column_or_expression)
- JSON_ARRAYAGG() 함수는 선택한 컬럼이나 표현식의 값들을 JSON 배열로 집계하는 함수입니다.
- 이 함수는 각 행의 값들을 JSON 배열의 요소로 모아줍니다. 만약 NULL 값이 나타나면, 이 함수는 그 값을 무시합니다. - 이 함수를 이용하면, 단일 SQL 쿼리로 여러 행의 데이터를 JSON 배열 형태로 쉽게 변환할 수 있습니다.
💡 사용예시
- 해당 예시에서는 JSON 데이터들을 JSON 배열로 집계하여 반환해 주는 예시입니다.
SELECT JSON_ARRAYAGG(remark)
FROM EMP
WHERE remark IS NOT NULL;
10. JSON_OBJECTAGG()
💡 JSON_OBJECTAGG(key, value)
- JSON_OBJECTAGG() 함수는 키-값 쌍을 JSON 객체로 집계하는 함수입니다. - 이 함수는 각 행의 키-값 쌍을 이용해 JSON 객체를 만들어줍니다. 만약 NULL 값을 가진 키가 나타나면, 이 함수는 그 키-값 쌍을 무시합니다. - 이 함수를 이용하면, 단일 SQL 쿼리로 여러 행의 키-값 데이터를 JSON 객체 형태로 쉽게 변환할 수 있습니다.
💡 사용예시 - 해당 예시에서는 객체를 JSON 객체로 반환해 주는 예시입니다.
SELECT *
, JSON_OBJECTAGG('sal', salary)
FROM EMP
WHERE remark IS NOT NULL;
