최종 수정: 에
JSON
JSON 소개
JSON(JavaScript Object Notation)은 사람이 읽을 수 있는 텍스트를 사용하여 데이터를 저장하고 전송하는 개방형 표준 파일 형식이자 데이터 교환 형식입니다. 공식 명세인 RFC7159에 따르면 JSON은 다음 기본 타입을 지원합니다.
- Bool
- Null
- Number
- String
- Array
- Object
JSON 데이터 타입은 JSON 데이터를 바이너리 형식으로 효율적으로 저장하며, JSON 함수를 통해 내부 필드에 접근할 수 있습니다.
기본적으로 최대 1048576바이트(1MB)를 지원하며, 최대 2147483643바이트(2GB)까지 늘릴 수 있습니다. 이 값은 string_type_length_soft_limit_bytes 설정으로 조정할 수 있습니다.
일반 STRING 타입에 JSON 문자열을 저장하는 것과 비교하면 JSON 타입에는 두 가지 주요 장점이 있습니다.
- 데이터를 삽입할 때 JSON 형식을 검증합니다.
- 더 효율적인 바이너리 저장 형식을 사용하므로,
json_extract같은 함수를 사용하면get_json_xx함수보다 JSON 내부 필드에 더 빠르게 접근할 수 있습니다.
참고
1.2.x 버전에서는 JSON 타입의 이름이 JSONB였습니다. MySQL과의 호환성을 유지하기 위해 2.0.0 버전부터 JSON으로 이름이 변경되었습니다. 기존 테이블에서는 이전 이름을 계속 사용할 수 있습니다.
JSON 숫자 정밀도 문제
JSON을 사용할 때는 숫자 정밀도에 특히 주의해야 합니다.
- 대부분의 시스템은 Number 타입을 IEEE 754-2008의 바이너리 64비트 배정밀도 부동소수점 수로 구현합니다(예: C++의 double 타입).
- JSON 명세는 Number의 기반 타입을 엄격하게 정의하지 않으며, JSON 데이터는 서로 다른 시스템 간에 텍스트로 교환되므로 정밀도 손실이 발생할 수 있습니다.
예를 들어 JSON 문자열이 {"abc": 18446744073709551616}인 경우:
-- Conversion result in MySQL
cast('{"abc": 18446744073709551616}' as json)
-- Result: {"abc": 1.8446744073709552e19}
// Conversion result in JavaScript
console.log(JSON.parse('{"abc": 18446744073709551616}'));
// Result: {abc: 18446744073709552000}
시스템 간에 데이터를 교환할 때 숫자 정밀도를 유지하려면 큰 숫자를 문자열로 저장해야 합니다. 예: {"abc": "18446744073709551616"}.
Doris의 JSON 타입
Doris는 JSON 표준 명세를 준수하는 데이터 타입을 지원하며, 효율적인 JSONB(JSON Binary) 형식을 사용하여 데이터를 바이너리로 인코딩하고 저장합니다.
지원 타입
Doris JSONB는 모든 표준 JSON 타입을 지원합니다. 주요 차이점은 Doris 내부 타입에 더 정확하게 매핑할 수 있도록 Number 타입을 더 세분화하여 확장했다는 점입니다.
| JSON 타입 | 하위 타입 | 대응하는 Doris 타입 |
|---|---|---|
| Bool | - | BOOLEAN |
| Null | - | (직접 대응하는 타입 없음, JSON null 값을 나타냄) |
| Number | Int8 | TINYINT |
| Int16 | SMALLINT | |
| Int32 | INT | |
| Int64 | BIGINT | |
| Int128 | LARGEINT | |
| Double | DOUBLE | |
| Float | FLOAT | |
| Decimal | DECIMAL | |
| String | - | STRING |
| Array | - | ARRAY |
| Object | - | STRUCT |
주요 참고 사항:
- Null의 의미:
- JSON의 Null은 "빈 값"을 나타내는 유효한 값입니다. 이는 "알 수 없음" 또는 "누락"을 나타내는 SQL의 NULL과 다릅니다.
- CAST('null' AS JSON)의 결과는 JSON null 값을 포함하는 JSONB 열이며, 이 열 자체는 SQL 수준에서 NULL이 아닙니다.
- CAST('null' AS JSON) IS NULL은 false(0)를 반환합니다. 열에 알려진 값인 JSON null이 포함되어 있고, 이 값은 SQL NULL이 아니기 때문입니다.
연산 및 제한 사항
- 비교 및 산술 연산:
- JSONB 열은 다른 데이터 타입(다른 JSONB 열 포함)과 직접 비교하거나 산술 연산에 사용할 수 없습니다.
- 해결 방법: JSON_EXTRACT 함수를 사용하여 JSONB에서 스칼라 값(INT, DOUBLE, STRING, BOOLEAN 등)을 추출한 다음, 대응하는 Doris 기본 타입으로 변환하여 비교하거나 계산합니다.
- 정렬:
- JSONB 열은 ORDER BY 연산을 지원하지 않습니다.
- 암시적 변환:
- 입력 시에만 지원: JSONB 열에 데이터를 입력할 때 STRING 타입은 JSONB로 암시적으로 변환될 수 있습니다(문자열 내용이 유효한 JSON 텍스트인 경우). 다른 Doris 타입은 JSONB로 암시적으로 변환할 수 없습니다.
JSON의 GROUP BY 및 DISTINCT 지원
예제 1: JSON 열에 GROUP BY 사용
mysql> SELECT * FROM test_jsonb_groupby;
+------+---------------+
| id | j |
+------+---------------+
| 1 | {"a":1,"b":2} |
| 2 | {"a":1,"b":3} |
| 3 | {"a":2,"b":2} |
| 4 | {"a":2,"b":2} |
| 5 | {"a":1,"b":2} |
| 6 | {"a":2,"b":2} |
+------+---------------+
6 rows in set (0.07 sec)
mysql> SELECT j, COUNT(*) FROM test_jsonb_groupby GROUP BY j;
+---------------+----------+
| j | COUNT(*) |
+---------------+----------+
| {"a":1,"b":3} | 1 |
| {"a":2,"b":2} | 3 |
| {"a":1,"b":2} | 2 |
+---------------+----------+
예제 2: JSON 열에 DISTINCT 쿼리 사용
mysql> SELECT DISTINCT j FROM test_jsonb_groupby;
+---------------+
| j |
+---------------+
| {"a":1,"b":3} |
| {"a":2,"b":2} |
| {"a":1,"b":2} |
+---------------+
참고 사항
-
바이너리 비교: JSON은 바이너리를 기준으로 비교합니다. 두 JSON 데이터가 의미상 같더라도 바이너리 표현이 다르면 같은 그룹으로 묶을 수 없습니다. 예를 들어:
mysql> SELECT * FROM test_jsonb;
+------+------+
| id | j |
+------+------+
| 1 | 123 |
| 2 | 123 |
+------+------+
mysql> SELECT j, COUNT(*) FROM test_jsonb GROUP BY j;
+------+----------+
| j | COUNT(*) |
+------+----------+
| 123 | 1 |
| 123 | 1 |
+------+----------+첫 번째
123은 내부적으로 64비트 정수로 저장되고(json_type은bigint를 반환), 두 번째123은 폭이 더 좁은 정수로 저장되기 때문에 바이너리 표현이 다릅니다(json_type은int를 반환하며, 이는bigint보다 폭이 좁은 모든 정수 타입에 해당합니다). 다음 쿼리로 타입을 확인할 수 있습니다.mysql> SELECT j, json_type(j, '$') FROM test_jsonb;
+------+------------------+
| j | json_type(j, '$') |
+------+------------------+
| 123 | bigint |
| 123 | int |
+------+------------------+마찬가지로 키 순서가 다른 JSON 객체도 같은 그룹으로 묶을 수 없습니다. 예를 들어:
mysql> SELECT * FROM test_jsonb;
+------+---------------+
| id | j |
+------+---------------+
| 2 | {"b":2,"a":1} |
| 1 | {"a":1,"b":2} |
+------+---------------+
mysql> SELECT j, COUNT(*) FROM test_jsonb GROUP BY j;
+---------------+----------+
| j | COUNT(*) |
+---------------+----------+
| {"b":2,"a":1} | 1 |
| {"a":1,"b":2} | 1 |
+---------------+----------+ -
숫자 타입 일관성: 숫자 타입의 차이를 무시하려면
NORMALIZE_JSON_NUMBERS_TO_DOUBLE함수를 사용하여 JSON의 모든 숫자를DOUBLE타입으로 변환합니다.mysql> SELECT NORMALIZE_JSON_NUMBERS_TO_DOUBLE(j), COUNT(*)
FROM test_jsonb
GROUP BY NORMALIZE_JSON_NUMBERS_TO_DOUBLE(j);
+-------------------------------------+----------+
| NORMALIZE_JSON_NUMBERS_TO_DOUBLE(j) | COUNT(*) |
+-------------------------------------+----------+
| 123 | 2 |
+-------------------------------------+----------+텍스트 파싱으로 JSON 객체를 생성하면(예: CAST를 사용하여 문자열을 JSON으로 변환), Doris가 저장에 적합한 숫자 타입을 자동으로 선택하므로 일반적으로 숫자 타입 불일치 문제는 발생하지 않습니다.
-
키 순서 일관성:
SORT_JSON_OBJECT_KEYS함수를 사용하여 키를 정렬합니다.mysql> SELECT SORT_JSON_OBJECT_KEYS(j), COUNT(*)
FROM test_jsonb
GROUP BY SORT_JSON_OBJECT_KEYS(j);
+--------------------------+----------+
| SORT_JSON_OBJECT_KEYS(j) | COUNT(*) |
+--------------------------+----------+
| {"a":1,"b":2} | 2 |
+--------------------------+----------+텍스트 파싱으로 JSON 객체를 생성하면(예: CAST를 사용하여 문자열을 JSON으로 변환), Doris는 텍스트의 키 순서를 유지합니다. 따라서 JSONB 데이터가 텍스트 파싱으로 생성된 경우 위와 같은 그룹화 문제가 발생하지 않습니다.
권장 사항
JSON 데이터의 숫자 타입이나 키 순서가 일관적인지 보장할 수 없다면, NORMALIZE_JSON_NUMBERS_TO_DOUBLE 및 SORT_JSON_OBJECT_KEYS 함수로 데이터를 전처리한 후 GROUP BY 연산을 수행하여 예상한 결과를 얻는 것이 좋습니다.
구문
정의:
json_column_name JSON
삽입:
INSERT INTO VALUES를 사용할 때는 따옴표로 감싼 문자열 형식으로 지정합니다. 예를 들어:
INSERT INTO table_name(id, json_column_name) VALUES (1, '{"k1": "100"}')
- STREAM LOAD에서는 해당 열의 형식이 추가 따옴표가 없는 문자열입니다. 예를 들어:
12 {"k1":"v31", "k2": 300}
13 []
14 [123, 456]
'\'가 포함된 특수 문자(예:'\r','\t')가 JSON에 나타나면 replace 함수를 사용하여"\"를"\\"로 바꿔야 합니다. 예를 들어"\n"을"\\n"으로 바꿔야 합니다.
쿼리:
- JSON 열 전체를 직접 조회합니다.
SELECT json_column_name FROM table_name;
- JSON 함수를 사용하여 JSON에서 특정 필드나 기타 정보를 추출합니다. 예를 들어:
SELECT json_extract(json_column_name, '$.k1') FROM table_name;
- JSON 타입은 정수, 문자열, BOOLEAN, ARRAY, MAP으로 변환할 수 있으며, 이러한 타입을 JSON으로 변환할 수도 있습니다. 예를 들어:
SELECT CAST('{"k1": "100"}' AS JSON);
SELECT CAST(json_column_name AS STRING) FROM table_name;
SELECT CAST(json_extract(json_column_name, '$.k1') AS INT) FROM table_name;
팁
현재 JSON 타입은 ORDER BY 또는 비교 연산에 사용할 수 없습니다.
JSON 입력
CAST를 사용하여 JSON 구문을 준수하는 문자열을 JSONB로 변환합니다.
-- Simple scalar/basic values (numeric types, bool, null, string)
mysql> SELECT cast('5' as json);
+-------------------+
| cast('5' as json) |
+-------------------+
| 5 |
+-------------------+
-- Arrays with zero or more elements (elements don't need to be the same type)
mysql> SELECT cast('[1, 2, "foo", null]' as json);
+-------------------------------------+
| cast('[1, 2, "foo", null]' as json) |
+-------------------------------------+
| [1,2,"foo",null] |
+-------------------------------------+
-- Objects containing key-value pairs
-- Note that object keys must always be quoted strings
mysql> SELECT cast('{"bar": "baz", "balance": 7.77, "active": false}' as json);
+------------------------------------------------------------------+
| cast('{"bar": "baz", "balance": 7.77, "active": false}' as json) |
+------------------------------------------------------------------+
| {"bar":"baz","balance":7.77,"active":false} |
+------------------------------------------------------------------+
-- Arrays and objects can be nested arbitrarily
mysql> SELECT cast('{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}' as json);
+---------------------------------------------------------------------+
| cast('{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}' as json) |
+---------------------------------------------------------------------+
| {"foo":[true,"bar"],"tags":{"a":1,"b":null}} |
+---------------------------------------------------------------------+
Doris의 JSONB는 공백처럼 의미에 영향을 주지 않는 세부 사항을 보존하지 않습니다.
mysql> -- The input text and JSON output may not look the same
mysql> SELECT cast('[1, 2]' as json);
+----------------------------------------+
| cast('[1, 2]' as json) |
+----------------------------------------+
| [1,2] |
+----------------------------------------+
주요 차이점 및 참고 사항:
- CAST(string AS JSON): JSON 구문을 준수하는 문자열을 파싱하는 데 사용합니다. Number 타입의 경우 Int8, Int16, Int32, Int64, Int128, Double 타입만 파싱하며 Decimal 타입은 파싱하지 않습니다.
- 대부분의 다른 JSON 구현과 달리 Doris의 JSONB 타입은 최대 Int128 정밀도를 지원합니다. Int128 정밀도를 초과하는 숫자는 오버플로가 발생할 수 있습니다.
- 입력 숫자 문자열이 12.34이면 Double로 파싱합니다. 소수점이 없으면 정수로 파싱합니다(Int128 범위를 초과하는 경우 Double로 변환하지만 정밀도 손실이 발생합니다).
to_json을 사용하여 Doris 내부 타입을 JSONB 타입으로 변환
mysql> SELECT to_json(1) , to_json(3.14) , to_json("12345");
+------------+---------------+------------------+
| to_json(1) | to_json(3.14) | to_json("12345") |
+------------+---------------+------------------+
| 1 | 3.14 | "12345" |
+------------+---------------+------------------+
mysql> SELECT to_json(array(array(1,2,3),array(4,5,6)));
+-------------------------------------------+
| to_json(array(array(1,2,3),array(4,5,6))) |
+-------------------------------------------+
| [[1,2,3],[4,5,6]] |
+-------------------------------------------+
mysql> SELECT json_extract(to_json(array(array(1,2,3),array(4,5,6))), '$.[1].[2]');
+----------------------------------------------------------------------+
| json_extract(to_json(array(array(1,2,3),array(4,5,6))), '$.[1].[2]') |
+----------------------------------------------------------------------+
| 6 |
+----------------------------------------------------------------------+
mysql> SELECT to_json(struct(123,array(4,5,6),"789"));
+------------------------------------------+
| to_json(struct(123,array(4,5,6),"789")) |
+------------------------------------------+
| {"col1":123,"col2":[4,5,6],"col3":"789"} |
+------------------------------------------+
mysql> SELECT json_extract(to_json(struct(123,array(4,5,6),"789")),"$.col2");
+----------------------------------------------------------------+
| json_extract(to_json(struct(123,array(4,5,6),"789")),"$.col2") |
+----------------------------------------------------------------+
| [4,5,6] |
+----------------------------------------------------------------+
to_json은 JSONB 타입에 매핑되는 Doris 타입의 변환만 지원합니다. 예를 들어 DECIMAL은 to_json에 사용할 수 있습니다. 하지만 DATE는 사용할 수 없으므로 먼저 STRING으로 변환한 다음 to_json을 사용해야 합니다.
JSONB 출력
다른 시스템과 데이터를 주고받기 위해 일반 텍스트로 변환할 때, Doris의 JSONB 타입은 유효한 JSON 텍스트를 생성합니다.
- Null 값:
- null로 출력합니다(따옴표 없음).
- Boolean 값:
- true → true 출력
- false → false 출력
- 숫자 타입:
- 모든 숫자 값을 그대로 출력합니다.
- 예: 5 → 5 출력, 3.14 → 3.14 출력
- 문자열:
- 큰따옴표로 감싸서 출력합니다: "
" - 특수 문자는 이스케이프 처리합니다.
- " → "
- \ → \
- / → /
- 백스페이스 → \b
- 폼 피드 → \f
- 줄바꿈 → \n
- 캐리지 리턴 → \r
- 탭 → \t
- 그 밖의 제어 문자(ASCII < 32)는 Unicode 이스케이프 시퀀스로 변환합니다: \uXXXX
- 큰따옴표로 감싸서 출력합니다: "
- 객체:
- 형식: {
} - 키-값 쌍 형식: "
": - 여러 키-값 쌍은 쉼표로 구분합니다.
- 형식: {
- 배열:
- 형식: [
] - 여러 요소는 쉼표로 구분합니다.
- 형식: [
- 중첩 구조 처리:
- 객체와 배열은 중첩 깊이에 제한이 없습니다.
- 각 중첩 수준은 같은 규칙을 사용하여 재귀적으로 처리합니다.
숫자 정밀도 문제
to_json을 사용하여 Doris 내부 타입을 JSONB로 변환할 때는 정밀도 손실이 발생하지 않습니다. Doris 내부 JSON 함수를 사용할 때도 반환값이 JSONB 타입이면 정밀도 손실이 발생하지 않습니다. 하지만 Doris JSONB를 일반 텍스트로 변환한 후 다시 JSONB로 변환하면 정밀도 손실이 발생할 수 있습니다.
예제: Doris JSON 타입 객체
Object{
"a": (Decimal 18446744073709551616.123)
}
일반 텍스트로 변환한 결과:
{"a": 18446744073709551616.123}
일반 텍스트를 다시 Doris JSON 타입으로 변환한 결과:
Object{
"a": (Double 18446744073709552000) // precision loss
}
설정 및 제한 사항
- JSON은 기본적으로 1,048,576바이트(1 MB)를 지원합니다.
- 크기 제한은 BE 설정 매개변수 string_type_length_soft_limit_bytes로 조정할 수 있습니다.
- 최대 2,147,483,643바이트(약 2 GB)까지 조정할 수 있습니다.
- Doris JSON 타입 객체에서 키 길이는 255바이트를 초과할 수 없습니다.
- JSON은 최대 100단계의 중첩 깊이를 지원합니다.
사용 예제
테이블 생성, 데이터 적재, 쿼리를 포함하는 JSON 데이터 타입 튜토리얼입니다.
데이터베이스 및 테이블 생성
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE test_json (
id INT,
j JSON
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 10
PROPERTIES("replication_num" = "1");
데이터 적재
Stream Load로 test_json.csv 테스트 데이터 적재
- 열은 2개이며, 1번째 열은 id이고 2번째 열은 JSON 문자열입니다.
- 행은 25개이며, 처음 18개 행은 유효한 JSON이고 마지막 7개 행은 유효하지 않습니다.
1 \N
2 null
3 true
4 false
5 100
6 10000
7 1000000000
8 1152921504606846976
9 6.18
10 "abcd"
11 {}
12 {"k1":"v31", "k2": 300}
13 []
14 [123, 456]
15 ["abc", "def"]
16 [null, true, false, 100, 6.18, "abc"]
17 [{"k1":"v41", "k2": 400}, 1, "a", 3.14]
18 {"k1":"v31", "k2": 300, "a1": [{"k1":"v41", "k2": 400}, 1, "a", 3.14]}
19 ''
20 'abc'
21 abc
22 100x
23 6.a8
24 {x
25 [123, abc]
- 행의 28%가 유효하지 않으므로 기본 설정으로 Stream Load를 실행하면 "too many filtered rows" 오류 메시지와 함께 실패합니다.
curl --location-trusted -u root: -T test_json.csv http://127.0.0.1:8840/api/testdb/test_json/_stream_load
{
"TxnId": 12019,
"Label": "744d9821-9c9f-43dc-bf3b-7ab048f14e32",
"TwoPhaseCommit": "false",
"Status": "Fail",
"Message": "too many filtered rows",
"NumberTotalRows": 25,
"NumberLoadedRows": 18,
"NumberFilteredRows": 7,
"NumberUnselectedRows": 0,
"LoadBytes": 380,
"LoadTimeMs": 48,
"BeginTxnTimeMs": 0,
"StreamLoadPutTimeMs": 1,
"ReadDataTimeMs": 0,
"WriteDataTimeMs": 45,
"CommitAndPublishTimeMs": 0,
"ErrorURL": "http://172.21.0.5:8840/api/_load_error_log?file=__shard_2/error_log_insert_stmt_95435c4bf5f156df-426735082a9296af_95435c4bf5f156df_426735082a9296af"
}
- 헤더 설정을 'max_filter_ratio: 0.3'으로 지정하면 Stream Load가 성공합니다.
curl --location-trusted -u root: -H 'max_filter_ratio: 0.3' -T test_json.csv http://127.0.0.1:8840/api/testdb/test_json/_stream_load
{
"TxnId": 12017,
"Label": "f37a50c1-43e9-4f4e-a159-a3db6abe2579",
"TwoPhaseCommit": "false",
"Status": "Success",
"Message": "OK",
"NumberTotalRows": 25,
"NumberLoadedRows": 18,
"NumberFilteredRows": 7,
"NumberUnselectedRows": 0,
"LoadBytes": 380,
"LoadTimeMs": 68,
"BeginTxnTimeMs": 0,
"StreamLoadPutTimeMs": 2,
"ReadDataTimeMs": 0,
"WriteDataTimeMs": 45,
"CommitAndPublishTimeMs": 19,
"ErrorURL": "http://172.21.0.5:8840/api/_load_error_log?file=__shard_0/error_log_insert_stmt_a1463f98a7b15caf-c79399b920f5bfa3_a1463f98a7b15caf_c79399b920f5bfa3"
}
- SELECT를 사용하여 Stream Load로 적재한 데이터를 확인합니다. JSON 타입 열은 일반 JSON 문자열로 표시됩니다.
mysql> SELECT * FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+
| id | j |
+------+---------------------------------------------------------------+
| 1 | NULL |
| 2 | null |
| 3 | true |
| 4 | false |
| 5 | 100 |
| 6 | 10000 |
| 7 | 1000000000 |
| 8 | 1152921504606846976 |
| 9 | 6.18 |
| 10 | "abcd" |
| 11 | {} |
| 12 | {"k1":"v31","k2":300} |
| 13 | [] |
| 14 | [123,456] |
| 15 | ["abc","def"] |
| 16 | [null,true,false,100,6.18,"abc"] |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} |
+------+---------------------------------------------------------------+
18 rows in set (0.03 sec)
insert into를 사용하여 데이터 쓰기
- 1개 행을 삽입하면 전체 행 수가 18개에서 19개로 늘어납니다.
mysql> INSERT INTO test_json VALUES(26, '{"k1":"v1", "k2": 200}');
Query OK, 1 row affected (0.09 sec)
{'label':'insert_4ece6769d1b42fd_ac9f25b3b8f3dc02', 'status':'VISIBLE', 'txnId':'12016'}
mysql> SELECT * FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+
| id | j |
+------+---------------------------------------------------------------+
| 1 | NULL |
| 2 | null |
| 3 | true |
| 4 | false |
| 5 | 100 |
| 6 | 10000 |
| 7 | 1000000000 |
| 8 | 1152921504606846976 |
| 9 | 6.18 |
| 10 | "abcd" |
| 11 | {} |
| 12 | {"k1":"v31","k2":300} |
| 13 | [] |
| 14 | [123,456] |
| 15 | ["abc","def"] |
| 16 | [null,true,false,100,6.18,"abc"] |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} |
| 26 | {"k1":"v1","k2":200} |
+------+---------------------------------------------------------------+
19 rows in set (0.03 sec)
쿼리
json_extract 함수로 JSON에서 필드 추출
- JSON 전체를 추출합니다. '$'는 JSON 경로에서 루트를 나타냅니다.
+------+---------------------------------------------------------------+---------------------------------------------------------------+
| id | j | json_extract(`j`, '$') |
+------+---------------------------------------------------------------+---------------------------------------------------------------+
| 1 | NULL | NULL |
| 2 | null | null |
| 3 | true | true |
| 4 | false | false |
| 5 | 100 | 100 |
| 6 | 10000 | 10000 |
| 7 | 1000000000 | 1000000000 |
| 8 | 1152921504606846976 | 1152921504606846976 |
| 9 | 6.18 | 6.18 |
| 10 | "abcd" | "abcd" |
| 11 | {} | {} |
| 12 | {"k1":"v31","k2":300} | {"k1":"v31","k2":300} |
| 13 | [] | [] |
| 14 | [123,456] | [123,456] |
| 15 | ["abc","def"] | ["abc","def"] |
| 16 | [null,true,false,100,6.18,"abc"] | [null,true,false,100,6.18,"abc"] |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | [{"k1":"v41","k2":400},1,"a",3.14] |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} |
| 26 | {"k1":"v1","k2":200} | {"k1":"v1","k2":200} |
+------+---------------------------------------------------------------+---------------------------------------------------------------+
19 rows in set (0.03 sec)
- k1 필드를 추출합니다. 존재하지 않으면 NULL을 반환합니다.
mysql> SELECT id, j, json_extract(j, '$.k1') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+----------------------------+
| id | j | json_extract(`j`, '$.k1') |
+------+---------------------------------------------------------------+----------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | "v31" |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | "v31" |
| 26 | {"k1":"v1","k2":200} | "v1" |
+------+---------------------------------------------------------------+----------------------------+
19 rows in set (0.03 sec)
- 최상위 배열에서 인덱스 0의 요소를 추출합니다.
mysql> SELECT id, j, json_extract(j, '$[0]') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+----------------------------+
| id | j | json_extract(`j`, '$[0]') |
+------+---------------------------------------------------------------+----------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | NULL |
| 13 | [] | NULL |
| 14 | [123,456] | 123 |
| 15 | ["abc","def"] | "abc" |
| 16 | [null,true,false,100,6.18,"abc"] | null |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | {"k1":"v41","k2":400} |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
| 26 | {"k1":"v1","k2":200} | NULL |
+------+---------------------------------------------------------------+----------------------------+
19 rows in set (0.03 sec)
- 이름이 a1인 JSON 배열 전체를 추출합니다.
mysql> SELECT id, j, json_extract(j, '$.a1') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+------------------------------------+
| id | j | json_extract(`j`, '$.a1') |
+------+---------------------------------------------------------------+------------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | NULL |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | [{"k1":"v41","k2":400},1,"a",3.14] |
| 26 | {"k1":"v1","k2":200} | NULL |
+------+---------------------------------------------------------------+------------------------------------+
19 rows in set (0.02 sec)
- 배열 안의 객체에서 중첩 필드를 추출합니다.
mysql> SELECT id, j, json_extract(j, '$.a1[0]'), json_extract(j, '$.a1[0].k1') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+-------------------------------+----------------------------------+
| id | j | json_extract(`j`, '$.a1[0]') | json_extract(`j`, '$.a1[0].k1') |
+------+---------------------------------------------------------------+-------------------------------+----------------------------------+
| 1 | NULL | NULL | NULL |
| 2 | null | NULL | NULL |
| 3 | true | NULL | NULL |
| 4 | false | NULL | NULL |
| 5 | 100 | NULL | NULL |
| 6 | 10000 | NULL | NULL |
| 7 | 1000000000 | NULL | NULL |
| 8 | 1152921504606846976 | NULL | NULL |
| 9 | 6.18 | NULL | NULL |
| 10 | "abcd" | NULL | NULL |
| 11 | {} | NULL | NULL |
| 12 | {"k1":"v31","k2":300} | NULL | NULL |
| 13 | [] | NULL | NULL |
| 14 | [123,456] | NULL | NULL |
| 15 | ["abc","def"] | NULL | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | {"k1":"v41","k2":400} | "v41" |
| 26 | {"k1":"v1","k2":200} | NULL | NULL |
+------+---------------------------------------------------------------+-------------------------------+----------------------------------+
19 rows in set (0.02 sec)
- 특정 데이터 타입으로 필드를 추출합니다.
- json_extract_string은 필드를 string 타입으로 추출하며, 필드가 string이 아니면 문자열로 변환합니다.
mysql> SELECT id, j, json_extract_string(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+---------------------------------------------------------------+
| id | j | json_extract_string(`j`, '$') |
+------+---------------------------------------------------------------+---------------------------------------------------------------+
| 1 | NULL | NULL |
| 2 | null | null |
| 3 | true | true |
| 4 | false | false |
| 5 | 100 | 100 |
| 6 | 10000 | 10000 |
| 7 | 1000000000 | 1000000000 |
| 8 | 1152921504606846976 | 1152921504606846976 |
| 9 | 6.18 | 6.18 |
| 10 | "abcd" | abcd |
| 11 | {} | {} |
| 12 | {"k1":"v31","k2":300} | {"k1":"v31","k2":300} |
| 13 | [] | [] |
| 14 | [123,456] | [123,456] |
| 15 | ["abc","def"] | ["abc","def"] |
| 16 | [null,true,false,100,6.18,"abc"] | [null,true,false,100,6.18,"abc"] |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | [{"k1":"v41","k2":400},1,"a",3.14] |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} |
| 26 | {"k1":"v1","k2":200} | {"k1":"v1","k2":200} |
+------+---------------------------------------------------------------+---------------------------------------------------------------+
19 rows in set (0.02 sec)
mysql> SELECT id, j, json_extract_string(j, '$.k1') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+-----------------------------------+
| id | j | json_extract_string(`j`, '$.k1') |
+------+---------------------------------------------------------------+-----------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | v31 |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | v31 |
| 26 | {"k1":"v1","k2":200} | v1 |
+------+---------------------------------------------------------------+-----------------------------------+
19 rows in set (0.03 sec)
- json_extract_int는 필드를 int 타입으로 추출하며, 필드가 int가 아니면 NULL을 반환합니다.
mysql> SELECT id, j, json_extract_int(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+-----------------------------+
| id | j | json_extract_int(`j`, '$') |
+------+---------------------------------------------------------------+-----------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | 100 |
| 6 | 10000 | 10000 |
| 7 | 1000000000 | 1000000000 |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | NULL |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
| 26 | {"k1":"v1","k2":200} | NULL |
+------+---------------------------------------------------------------+-----------------------------+
19 rows in set (0.02 sec)
mysql> SELECT id, j, json_extract_int(j, '$.k2') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+--------------------------------+
| id | j | json_extract_int(`j`, '$.k2') |
+------+---------------------------------------------------------------+--------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | 300 |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 300 |
| 26 | {"k1":"v1","k2":200} | 200 |
+------+---------------------------------------------------------------+--------------------------------+
19 rows in set (0.03 sec)
- json_extract_bigint는 필드를 bigint 타입으로 추출하며, 필드가 bigint가 아니면 NULL을 반환합니다.
mysql> SELECT id, j, json_extract_bigint(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+--------------------------------+
| id | j | json_extract_bigint(`j`, '$') |
+------+---------------------------------------------------------------+--------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | 100 |
| 6 | 10000 | 10000 |
| 7 | 1000000000 | 1000000000 |
| 8 | 1152921504606846976 | 1152921504606846976 |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | NULL |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
| 26 | {"k1":"v1","k2":200} | NULL |
+------+---------------------------------------------------------------+--------------------------------+
19 rows in set (0.03 sec)
mysql> SELECT id, j, json_extract_bigint(j, '$.k2') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+-----------------------------------+
| id | j | json_extract_bigint(`j`, '$.k2') |
+------+---------------------------------------------------------------+-----------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | 300 |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 300 |
| 26 | {"k1":"v1","k2":200} | 200 |
+------+---------------------------------------------------------------+-----------------------------------+
19 rows in set (0.02 sec)
- json_extract_double은 필드를 double 타입으로 추출하며, 필드가 double이 아니면 NULL을 반환합니다.
mysql> SELECT id, j, json_extract_double(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+--------------------------------+
| id | j | json_extract_double(`j`, '$') |
+------+---------------------------------------------------------------+--------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | 100 |
| 6 | 10000 | 10000 |
| 7 | 1000000000 | 1000000000 |
| 8 | 1152921504606846976 | 1.152921504606847e+18 |
| 9 | 6.18 | 6.18 |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | NULL |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
| 26 | {"k1":"v1","k2":200} | NULL |
+------+---------------------------------------------------------------+--------------------------------+
19 rows in set (0.02 sec)
mysql> SELECT id, j, json_extract_double(j, '$.k2') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+-----------------------------------+
| id | j | json_extract_double(`j`, '$.k2') |
+------+---------------------------------------------------------------+-----------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | 300 |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 300 |
| 26 | {"k1":"v1","k2":200} | 200 |
+------+---------------------------------------------------------------+-----------------------------------+
19 rows in set (0.03 sec)
- json_extract_bool은 필드를 boolean 타입으로 추출하며, 필드가 boolean이 아니면 NULL을 반환합니다.
mysql> SELECT id, j, json_extract_bool(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+------------------------------+
| id | j | json_extract_bool(`j`, '$') |
+------+---------------------------------------------------------------+------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | 1 |
| 4 | false | 0 |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | NULL |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
| 26 | {"k1":"v1","k2":200} | NULL |
+------+---------------------------------------------------------------+------------------------------+
19 rows in set (0.01 sec)
mysql> SELECT id, j, json_extract_bool(j, '$[1]') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+---------------------------------+
| id | j | json_extract_bool(`j`, '$[1]') |
+------+---------------------------------------------------------------+---------------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | NULL |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | 1 |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
| 26 | {"k1":"v1","k2":200} | NULL |
+------+---------------------------------------------------------------+---------------------------------+
19 rows in set (0.01 sec)
- json_extract_isnull은 JSON null 타입의 필드인지 확인하며, 필드가 JSON null이면 1을, 그렇지 않으면 0을 반환합니다.
- JSON null은 SQL NULL과 다릅니다. SQL NULL은 필드에 값이 없음을 나타내지만, JSON null은 필드에 특수 값인 null이 있음을 나타냅니다.
mysql> SELECT id, j, json_extract_isnull(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+--------------------------------+
| id | j | json_extract_isnull(`j`, '$') |
+------+---------------------------------------------------------------+--------------------------------+
| 1 | NULL | NULL |
| 2 | null | 1 |
| 3 | true | 0 |
| 4 | false | 0 |
| 5 | 100 | 0 |
| 6 | 10000 | 0 |
| 7 | 1000000000 | 0 |
| 8 | 1152921504606846976 | 0 |
| 9 | 6.18 | 0 |
| 10 | "abcd" | 0 |
| 11 | {} | 0 |
| 12 | {"k1":"v31","k2":300} | 0 |
| 13 | [] | 0 |
| 14 | [123,456] | 0 |
| 15 | ["abc","def"] | 0 |
| 16 | [null,true,false,100,6.18,"abc"] | 0 |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | 0 |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 0 |
| 26 | {"k1":"v1","k2":200} | 0 |
+------+---------------------------------------------------------------+--------------------------------+
19 rows in set (0.03 sec)
json_exists_path로 JSON에 필드가 존재하는지 확인
mysql> SELECT id, j, json_exists_path(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+-----------------------------+
| id | j | json_exists_path(`j`, '$') |
+------+---------------------------------------------------------------+-----------------------------+
| 1 | NULL | NULL |
| 2 | null | 1 |
| 3 | true | 1 |
| 4 | false | 1 |
| 5 | 100 | 1 |
| 6 | 10000 | 1 |
| 7 | 1000000000 | 1 |
| 8 | 1152921504606846976 | 1 |
| 9 | 6.18 | 1 |
| 10 | "abcd" | 1 |
| 11 | {} | 1 |
| 12 | {"k1":"v31","k2":300} | 1 |
| 13 | [] | 1 |
| 14 | [123,456] | 1 |
| 15 | ["abc","def"] | 1 |
| 16 | [null,true,false,100,6.18,"abc"] | 1 |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | 1 |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 1 |
| 26 | {"k1":"v1","k2":200} | 1 |
+------+---------------------------------------------------------------+-----------------------------+
19 rows in set (0.02 sec)
mysql> SELECT id, j, json_exists_path(j, '$.k1') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+--------------------------------+
| id | j | json_exists_path(`j`, '$.k1') |
+------+---------------------------------------------------------------+--------------------------------+
| 1 | NULL | NULL |
| 2 | null | 0 |
| 3 | true | 0 |
| 4 | false | 0 |
| 5 | 100 | 0 |
| 6 | 10000 | 0 |
| 7 | 1000000000 | 0 |
| 8 | 1152921504606846976 | 0 |
| 9 | 6.18 | 0 |
| 10 | "abcd" | 0 |
| 11 | {} | 0 |
| 12 | {"k1":"v31","k2":300} | 1 |
| 13 | [] | 0 |
| 14 | [123,456] | 0 |
| 15 | ["abc","def"] | 0 |
| 16 | [null,true,false,100,6.18,"abc"] | 0 |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | 0 |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 1 |
| 26 | {"k1":"v1","k2":200} | 1 |
+------+---------------------------------------------------------------+--------------------------------+
19 rows in set (0.03 sec)
mysql> SELECT id, j, json_exists_path(j, '$[2]') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+--------------------------------+
| id | j | json_exists_path(`j`, '$[2]') |
+------+---------------------------------------------------------------+--------------------------------+
| 1 | NULL | NULL |
| 2 | null | 0 |
| 3 | true | 0 |
| 4 | false | 0 |
| 5 | 100 | 0 |
| 6 | 10000 | 0 |
| 7 | 1000000000 | 0 |
| 8 | 1152921504606846976 | 0 |
| 9 | 6.18 | 0 |
| 10 | "abcd" | 0 |
| 11 | {} | 0 |
| 12 | {"k1":"v31","k2":300} | 0 |
| 13 | [] | 0 |
| 14 | [123,456] | 0 |
| 15 | ["abc","def"] | 0 |
| 16 | [null,true,false,100,6.18,"abc"] | 1 |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | 1 |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 0 |
| 26 | {"k1":"v1","k2":200} | 0 |
+------+---------------------------------------------------------------+--------------------------------+
19 rows in set (0.02 sec)
json_type으로 JSON 필드의 데이터 타입 가져오기
- JSON 경로로 지정한 필드의 데이터 타입을 반환하며, 필드가 존재하지 않으면 NULL을 반환합니다.
mysql> SELECT id, j, json_type(j, '$') FROM test_json ORDER BY id;
+------+---------------------------------------------------------------+----------------------+
| id | j | json_type(`j`, '$') |
+------+---------------------------------------------------------------+----------------------+
| 1 | NULL | NULL |
| 2 | null | null |
| 3 | true | bool |
| 4 | false | bool |
| 5 | 100 | int |
| 6 | 10000 | int |
| 7 | 1000000000 | int |
| 8 | 1152921504606846976 | bigint |
| 9 | 6.18 | double |
| 10 | "abcd" | string |
| 11 | {} | object |
| 12 | {"k1":"v31","k2":300} | object |
| 13 | [] | array |
| 14 | [123,456] | array |
| 15 | ["abc","def"] | array |
| 16 | [null,true,false,100,6.18,"abc"] | array |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | array |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | object |
| 26 | {"k1":"v1","k2":200} | object |
+------+---------------------------------------------------------------+----------------------+
19 rows in set (0.02 sec)
mysql> select id, j, json_type(j, '$.k1') from test_json order by id;
+------+---------------------------------------------------------------+-------------------------+
| id | j | json_type(`j`, '$.k1') |
+------+---------------------------------------------------------------+-------------------------+
| 1 | NULL | NULL |
| 2 | null | NULL |
| 3 | true | NULL |
| 4 | false | NULL |
| 5 | 100 | NULL |
| 6 | 10000 | NULL |
| 7 | 1000000000 | NULL |
| 8 | 1152921504606846976 | NULL |
| 9 | 6.18 | NULL |
| 10 | "abcd" | NULL |
| 11 | {} | NULL |
| 12 | {"k1":"v31","k2":300} | string |
| 13 | [] | NULL |
| 14 | [123,456] | NULL |
| 15 | ["abc","def"] | NULL |
| 16 | [null,true,false,100,6.18,"abc"] | NULL |
| 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
| 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | string |
| 26 | {"k1":"v1","k2":200} | string |
+------+---------------------------------------------------------------+-------------------------+
19 rows in set (0.03 sec)
자주 묻는 질문
- JSON의 null과 SQL의 NULL(즉, IS NULL)은 다른가요?
네, 다릅니다. JSON의 null(예: {"key1": null})은 key1 키가 존재하며 그 값이 명시적으로 null임을 의미합니다. 이는 JSON 바이너리에 인코딩되는 특수 타입입니다.
반면 SQL NULL(IS NULL을 사용할 때)은 JSON 객체에 키 자체가 존재하지 않음을 나타낼 수 있습니다.
예를 들어:
mysql> SELECT JSON_EXTRACT_STRING('{"key1" : null}', "$.key1") IS NULL;
+----------------------------------------------------------+
| JSON_EXTRACT_STRING('{"key1" : null}', "$.key1") IS NULL |
+----------------------------------------------------------+
| 0 |
+----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_EXTRACT_STRING('{"key1" : null}', "$.key_not_exist") IS NULL;
+-------------------------------------------------------------------+
| JSON_EXTRACT_STRING('{"key1" : null}', "$.key_not_exist") IS NULL |
+-------------------------------------------------------------------+
| 1 |
+-------------------------------------------------------------------+
1 row in set (0.01 sec)
GET_JSON_XXX함수와JSON_EXTRACT_XXX함수는 어떻게 다르며, 어떤 함수를 선택해야 하나요?
GET_JSON_XXX 함수는 문자열 타입에 사용하도록 설계되었으며, 원본 JSON 문자열에서 직접 값을 추출합니다. 반면 JSON_EXTRACT_XXX 함수는 JSON 데이터 타입 전용으로 구현되었으며 해당 타입에 최적화되어 있습니다.
키워드
JSONB, JSON, json_parse, json_parse_error_to_null, json_parse_error_to_value, json_extract, json_extract_isnull, json_extract_bool, json_extract_int, json_extract_bigint, json_extract_double, json_extract_string, json_exists_path, json_type