文章目录
1.doris表基本概念
- 在 Doris 中，数据都以关系表（Table）的形式进行逻辑上的描述。
1.1.1Row & Column
- 一张表包括行（Row）和列（Column）
- Row 即用户的一行数据
- Column 用于描述一行数据中不同的字段 - 在
默认的数据模型中，Column 只分为排序列和非排序列。存储引擎会按照排序列对数据进行排序存储，并建立稀疏索引，以便在排序数据上进行快速查找。- 而在聚合模型中，Column 可以分为两大类：Key 和 Value。从业务角度看，Key 和Value 可以分别对应维度列和指标列。从聚合模型的角度来说，Key 列相同的行，会聚合成一行。其中 Value 列的聚合方式由用户在建表时指定。
1.1.2Partition & Tablet
- 在 Doris 的存储引擎中，
用户数据首先被划分成若干个分区（Partition），划分的规则通常是按照用户指定的分区列进行范围划分，比如按时间划分。而在每个分区内，数据被进一步的按照 Hash 的方式分桶，分桶的规则是要找用户指定的分桶列的值进行 Hash 后分桶。每个分桶就是一个数据分片（Tablet），也是数据划分的最小逻辑单元。 - Tablet 之间的数据是
没有交集的，独立存储的。Tablet 也是数据移动、复制等操作的最小物理存储单元。 - Partition 可以视为是
逻辑上最小的管理单元。数据的导入与删除，都可以或仅能针 对一个 Partition 进行。
2.doris字段类型
字段类型TINYINT1 字节范围：-2^7 + 1 ~ 2^7 - 1SMALLINT2 字节范围：-2^15 + 1 ~ 2^15 - 1INT4 字节范围：-2^31 + 1 ~ 2^31 - 1BIGINT8 字节范围：-2^63 + 1 ~ 2^63 - 1LARGEINT16 字节范围：-2^127 + 1 ~ 2^127 - 1FLOAT4 字节支持科学计数法DOUBLE12 字节支持科学计数法DECIMAL(M[,D])16 字节“默认是DECIMAL(9, 0)高精度定点数，M 代表一共有多少个有效数字(precision)，D 代表小数位有多少数字(scale)，有效数字 M 的范围是 [1, 27]，小数位数字数量 D 的范围是 [0, 9]，整数位数字数量的范围是 [1, 18]，另外，M 必须要大于等于 D 的取值。”DECIMALV3(M[,D])Doris中的DECIMALV3是真正意义上的高精度定点数，相比于老版本的Decimal 1.可表示范围更大,有效数字 M 的范围是 [1, 38]，小数位数字数量 D 的范围是 [0, precision]。2.性能更高。老版本的DECIMAL在内存中需要占用16 bytes，在存储中占用12 bytes，而DECIMALV3进行了自适应调整。3.更完备的精度推演。对于不同的表达式，应用不同的精度推演规则对结果的精度进行推演。DATE3 字节范围：0000-01-01 ~ 9999-12-31DATEV2DATEV2类型相比DATE类型更加高效，在计算时，DATEV2相比DATE可以节省一半的内存使用量。DATETIME8 字节范围：0000-01-01 00:00:00 ~ 9999-12-31 23:59:59DATETIMEV2([P])可选参数P表示时间精度，取值范围是[0, 6]，即最多支持6位小数（微秒）。不设置时为0。取值范围是[‘0000-01-01 00:00:00[.000000]’, ‘9999-12-31 23:59:59[.999999]’] 相比DATETIME类型，DATETIMEV2更加高效，并且支持了最多到微秒的时间精度。CHAR[(length)]定长字符串。长度范围：1 ~ 255。默认为 1VARCHAR[(length)]变长字符串。长度范围：1 ~ 65533 注意：变长字符串是以UTF-8编码存储的，因此通常英文字符占1个字节，中文字符占3个字节。BOOLEAN与 TINYINT 一样，0 代表 false，1 代 表 trueHLL1~16385 个字节HLL不能作为key列使用，建表时配合聚合类型为HLL_UNION。用户不需要指定长度和默认值。长度根据数据的聚合程度系统内控制。并且HLL列只能通过配套的hll_union_agg、hll_raw_agg、hll_cardinality、hll_hash进行查询或使用。
HLL是模糊去重，在数据量大的情况性能优于Count Distinct。HLL的误差通常在1%左右，有时会达到2%。
BITMAP1.不能作为key列使用，建表时配合聚合类型为BITMAP_UNION。2.用户不需要指定长度和默认值。长度根据数据的聚合程度系统内控制。 3.BITMAP列只能通过配套的bitmap_union_count、bitmap_union、bitmap_hash等函数进行查询或使用。4.离线场景下使用BITMAP会影响导入速度，在数据量大的情况下查询速度会慢于HLL，并优于Count Distinct。5.注意：实时场景下BITMAP如果不使用全局字典，使用了bitmap_hash()可能会导致有千分之一左右的误差。STRING变长字符串，0.15 版本支持，最大支持 2147483643 字节（2GB-4），长度还受 be 配置
string_type_soft_limit
, 实际能存储的最大长度取两者最小值。只能用在 value 列，不能用在 key 列和分区、分桶列。 注意：变长字符串是以UTF-8编码存储的，因此通常英文字符占1个字节，中文字符占3个字节。ARRAY<T>由T类型元素组成的数组，不能作为key列使用。目前支持在Duplicate模型的表中使用。T支持的类型有：BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DATE,DATEV2, DATETIME, DATETIMEV2, CHAR, VARCHAR, STRINGQUANTILE_STATEQUANTILE_STATE不能作为key列使用，建表时配合聚合类型为QUANTILE_UNION。用户不需要指定长度和默认值。长度根据数据的聚合程度系统内控制。并且QUANTILE_STATE列只能通过配套的QUANTILE_PERCENT、QUANTILE_UNION、TO_QUANTILE_STATE等函数进行查询或使用。QUANTILE_STATE 是一种计算分位数近似值的类型，在导入时会对相同的key，不同 value 进行预聚合，当value数量不超过2048时采用明细记录所有数据，当 value 数量大于2048时采用 TDigest 算法，对数据进行聚合（聚类）保存聚类后的质心点。JSONBJSONB(JSON Binary)类型 二进制JSON类型，采用二进制JSONB格式存储，通过jsonb函数访问JSON内部字段。与普通STRING类型存储的JSON字符串相比，JSONB类型有两点优势1. 数据写入时进行JSON格式校验2. 二进制存储格式更加高效，通过jsonb_extract等函数可以高效访问JSON内部字段，比get_json_xx函数快几倍
- bitmap使用场景
selecthour,bitmap_union_count(pv)over(orderbyhour) uv
from(selecthour,bitmap_union(device_id)as pv
from metric_table -- 查询每小时的累计UVwhere datekey =20200622groupbyhourorderby1) final
;
- hll 使用场景
selecthour,hll_union_agg(pv)over(orderbyhour) uv
from(selecthour,hll_raw_agg(device_id)as pv
from metric_table -- 查询每小时的累计uvwhere datekey =20200622groupbyhourorderby1) final
;
- ARRAY<T>使用场景
建表示例如下：
mysql>CREATETABLE`array_test`(`id`int(11)NULLCOMMENT"",`c_array` ARRAY<int(11)>NULLCOMMENT"")ENGINE=OLAP
DUPLICATEKEY(`id`)COMMENT"OLAP"DISTRIBUTEDBYHASH(`id`) BUCKETS 1
PROPERTIES ("replication_allocation"="tag.location.default: 1","in_memory"="false","storage_format"="V2");
插入数据示例：
mysql>INSERTINTO`array_test`VALUES(1,[1,2,3,4,5]);
mysql>INSERTINTO`array_test`VALUES(2,[6,7,8]),(3,[]),(4,null);
查询数据示例：
mysql>SELECT*FROM`array_test`;+------+-----------------+| id | c_array |+------+-----------------+|1|[1,2,3,4,5]||2|[6,7,8]||3|[]||4|NULL|+------+-----------------+
- JSONB 使用场景
设置容错率参数 'max_filter_ratio: 0.3'
curl --location-trusted -u root: -H 'max_filter_ratio: 0.3' -T test_jsonb.csv http://127.0.0.1:8840/api/testdb/test_jsonb/_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"
}
查看stream load导入的数据，JSONB类型的列j会自动转成JSON string展示
mysql>SELECT*FROM test_jsonb ORDERBY 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]} |+------+---------------------------------------------------------------+18rowsinset(0.03 sec)insertinto 插入数据
insert1条数据，总数据从18条增加到19条
mysql>INSERTINTO test_jsonb VALUES(26,'{"k1":"v1", "k2": 200}');
Query OK,1row affected (0.09 sec)
{'label':'insert_4ece6769d1b42fd_ac9f25b3b8f3dc02','status':'VISIBLE','txnId':'12016'}
mysql>SELECT*FROM test_jsonb ORDERBY 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} |+------+---------------------------------------------------------------+19rowsinset(0.03 sec)
查询
用jsonb_extract取json内的某个字段
获取整个json，$ 在json path中代表root，即整个json
+------+---------------------------------------------------------------+---------------------------------------------------------------+| id | j | jsonb_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} |+------+---------------------------------------------------------------+---------------------------------------------------------------+19rowsinset(0.03 sec)
获取k1字段，没有k1字段的行返回NULL
mysql>SELECT id, j, jsonb_extract(j,'$.k1')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+----------------------------+| id | j | jsonb_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"|+------+---------------------------------------------------------------+----------------------------+19rowsinset(0.03 sec)
获取顶层数组的第0个元素
mysql>SELECT id, j, jsonb_extract(j,'$[0]')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+----------------------------+| id | j | jsonb_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|+------+---------------------------------------------------------------+----------------------------+19rowsinset(0.03 sec)
获取整个json array
mysql>SELECT id, j, jsonb_extract(j,'$.a1')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+------------------------------------+| id | j | jsonb_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|+------+---------------------------------------------------------------+------------------------------------+19rowsinset(0.02 sec)
获取json array中嵌套object的字段
mysql>SELECT id, j, jsonb_extract(j,'$.a1[0]'), jsonb_extract(j,'$.a1[0].k1')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+-------------------------------+----------------------------------+| id | j | jsonb_extract(`j`,'$.a1[0]')| jsonb_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|+------+---------------------------------------------------------------+-------------------------------+----------------------------------+19rowsinset(0.02 sec)
获取具体类型的
jsonb_extract_string 获取string类型字段，非string类型转成string
mysql>SELECT id, j, jsonb_extract_string(j,'$')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+---------------------------------------------------------------+| id | j | jsonb_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} |+------+---------------------------------------------------------------+---------------------------------------------------------------+19rowsinset(0.02 sec)
mysql>SELECT id, j, jsonb_extract_string(j,'$.k1')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+-----------------------------------+| id | j | jsonb_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 |+------+---------------------------------------------------------------+-----------------------------------+19rowsinset(0.03 sec)
jsonb_extract_int 获取int类型字段，非int类型返回NULL
mysql>SELECT id, j, jsonb_extract_int(j,'$')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+-----------------------------+| id | j | jsonb_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|+------+---------------------------------------------------------------+-----------------------------+19rowsinset(0.02 sec)
mysql>SELECT id, j, jsonb_extract_int(j,'$.k2')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+--------------------------------+| id | j | jsonb_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|+------+---------------------------------------------------------------+--------------------------------+19rowsinset(0.03 sec)
jsonb_extract_bigint 获取bigint类型字段，非bigint类型返回NULL
mysql>SELECT id, j, jsonb_extract_bigint(j,'$')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+--------------------------------+| id | j | jsonb_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|+------+---------------------------------------------------------------+--------------------------------+19rowsinset(0.03 sec)
mysql>SELECT id, j, jsonb_extract_bigint(j,'$.k2')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+-----------------------------------+| id | j | jsonb_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|+------+---------------------------------------------------------------+-----------------------------------+19rowsinset(0.02 sec)
jsonb_extract_double 获取double类型字段，非double类型返回NULL
mysql>SELECT id, j, jsonb_extract_double(j,'$')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+--------------------------------+| id | j | jsonb_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|+------+---------------------------------------------------------------+--------------------------------+19rowsinset(0.02 sec)
mysql>SELECT id, j, jsonb_extract_double(j,'$.k2')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+-----------------------------------+| id | j | jsonb_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|+------+---------------------------------------------------------------+-----------------------------------+19rowsinset(0.03 sec)
jsonb_extract_bool 获取bool类型字段，非bool类型返回NULL
mysql>SELECT id, j, jsonb_extract_bool(j,'$')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+------------------------------+| id | j | jsonb_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|+------+---------------------------------------------------------------+------------------------------+19rowsinset(0.01 sec)
mysql>SELECT id, j, jsonb_extract_bool(j,'$[1]')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+---------------------------------+| id | j | jsonb_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|+------+---------------------------------------------------------------+---------------------------------+19rowsinset(0.01 sec)
jsonb_extract_isnull 获取json null类型字段，null返回1，非null返回0
需要注意的是json null和SQLNULL不一样，SQLNULL表示某个字段的值不存在，而json null表示值存在但是是一个特殊值null
mysql>SELECT id, j, jsonb_extract_isnull(j,'$')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+--------------------------------+| id | j | jsonb_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|+------+---------------------------------------------------------------+--------------------------------+19rowsinset(0.03 sec)
用jsonb_exists_path检查json内的某个字段是否存在
mysql>SELECT id, j, jsonb_exists_path(j,'$')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+-----------------------------+| id | j | jsonb_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|+------+---------------------------------------------------------------+-----------------------------+19rowsinset(0.02 sec)
mysql>SELECT id, j, jsonb_exists_path(j,'$.k1')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+--------------------------------+| id | j | jsonb_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|+------+---------------------------------------------------------------+--------------------------------+19rowsinset(0.03 sec)
mysql>SELECT id, j, jsonb_exists_path(j,'$[2]')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+--------------------------------+| id | j | jsonb_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|+------+---------------------------------------------------------------+--------------------------------+19rowsinset(0.02 sec)
用jsonb_type获取json内的某个字段的类型
返回json path对应的json字段类型，如果不存在返回NULL
mysql>SELECT id, j, jsonb_type(j,'$')FROM test_jsonb ORDERBY id;+------+---------------------------------------------------------------+----------------------+| id | j | jsonb_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 |+------+---------------------------------------------------------------+----------------------+19rowsinset(0.02 sec)
mysql>select id, j, jsonb_type(j,'$.k1')from test_jsonb orderby id;+------+---------------------------------------------------------------+-------------------------+| id | j | jsonb_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 |+------+---------------------------------------------------------------+-------------------------+19rowsinset(0.03 sec)
本文转载自: https://blog.csdn.net/qq_46893497/article/details/129358714
版权归原作者 韩家小志 所有， 如有侵权，请联系我们删除。
版权归原作者 韩家小志 所有， 如有侵权，请联系我们删除。