MySQL cast()函数以及json列

在工作中遇到了json列，不清楚如何写SQL，查询了下相关的文档之后总结下，根据json列的值进行区分，列值指的是 json_type(json列)的结果

1、列值为NULL

create table t1(c1 int primary key, c2 json);

insert into t1 values(4, NULL);
select * from t1;

insert into t1 values(5, null);
select * from t1;

drop table t1;

2、列值为 time/date/datetime

create table t3(c1 varchar(30) not null, j json);

insert into t3 values ('time' ,cast(cast('10:10:10' as time) as json)), ('date' ,cast(cast('2010-10-10' as date) as json));
select j, json_type(j), j from t3;

insert into t3 values ('time' ,cast(cast('23:24:25' as time) as json));
select j, json_type(j), j from t3;

insert into t3 values ('date' ,cast(cast('2015-01-15' as date) as json));
select j, json_type(j), j from t3;

insert into t3 values ('datetime' ,cast(cast('2015-01-15 23:24:25' as datetime) as json));
select c1, j, json_type(j), j from t3;

3、列值为 signed/unsigned

create table t5(c1 varchar(30) not null, j json, c2 int);

insert into t5 values ('ee', cast(cast(7.50 as signed) as json), 0);
select json_type(j), j from t5;

insert into t5 values ('rr', cast(cast(-1.49 as signed) as json), 1);
select json_type(j), j from t5;

insert into t5 values ('dd', cast(cast(16 as signed) as json), 4);
select json_type(j), j from t5;

insert into t5 values ('ee', cast(cast(17 as unsigned) as json), 5);
select json_type(j), j from t5;

insert into t5 values ('ff', cast(cast(-3.8 as unsigned) as json), 6);
select json_type(j), j from t5;

insert into t5 values ('ee', cast(cast(-3.8 as signed) as json), 7);
select c1, json_type(j), j from t5;

4、列值为 object

create table t2(c1 varchar(30) not null, j json);

insert into t2 values ('sssssss', JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));
select json_type(j) from t2;

insert into t2 values ('sssssss', cast('{}' as json));
select json_type(j) from t2;

5、列值为 double/float

create table t6(c1 varchar(50) not null, j json);

insert into t6 values ('hh' ,cast(cast(4.6 as float) as json));
select json_type(j), j from t6;

insert into t6 values ('jj' ,cast(cast(5.19 as double) as json));
select json_type(j), j from t6;

insert into t6 values ('ii' ,cast(cast(100 as double) as json));
select json_type(j), j from t6;

insert into t6 values ('kk' ,cast(cast(DATE'2019-08-07' as double) as json));
select json_type(j), j from t6;

insert into t6 values ('ll' ,cast(cast(-1 as float) as json));
select c1, json_type(j), j from t6;

6、列值为 timestamp

create table t8(c1 varchar(30) not null, j json);

insert into t8 values ('timestamp', cast(TIMESTAMP'2015-01-15 23:24:25' as json));
select c1, json_type(j), j from t8;

select * from t8 where j->'$' = TIMESTAMP'2015-01-15 23:24:25';

7、列值为array

create table t9(c1 varchar(30) not null, j json);

INSERT INTO t9 VALUES ('eeeeeeee', CAST('[]' AS CHAR CHARACTER SET 'ascii'));
select c1, json_type(j), j from t9;

select * from t9 where j->'$' = JSON_ARRAY(1, 2, 3);

8、列值为blob

create table t10(c1 varchar(30) not null, j json);

INSERT INTO t10 VALUES ('rrrrr', CAST(x'cafebabe' AS JSON));
select c1, json_type(j), j from t10;

select * from t10 where j->'$' = x'cafebabe';

9、decimal

create table t12(c1 varchar(30) not null, j json);

insert into t12 VALUES ('wwww', cast(cast('12.5' as decimal) AS JSON));
select json_type(j), j from t12;

insert into t12 VALUES ('qqqq', cast(cast('12.5' as decimal(6,2)) AS JSON));
select json_type(j), j from t12;

insert into t12 VALUES ('eeee', cast(cast('12.5' as decimal(6,0)) AS JSON));
select c1, json_type(j), j from t12;

10、string

create table t11(c1 varchar(30) not null, j json);

insert into t11 VALUES ('wwww', '"scalar string"');
select json_type(j), j from t11;

select * from t11 where j->'$' = "scalar string";

11、bool

create table t7(c1 varchar(30) not null, j json);

insert into t7 values ('aa', 'true');
select json_type(j), j from t7;

insert into t7 values ('bb', 'false');
select c1, json_type(j), j from t7;

12、null

create table t14(c1 varchar(30) not null, j json);

insert into t14 values ('nn', 'null');
select c1, json_type(j), j from t14;

13、equivalent

create table tt(c1 int primary key, j json);

insert into tt values (1, '"2019-1-1"');
insert into tt values (2, '"2019-01-01"');
insert into tt values (3, cast(DATE'2019-1-1' as json));

select * from tt where j->'$' = '2019-1-1';
select * from tt where j->'$' = '2019-01-1';

select * from tt where j->'$' = '2019-01-01';
select * from tt where j->'$' = '2019-1-01';

select * from tt where j->'$' = DATE'2019-01-01';
select * from tt where j->'$' = DATE'2019-01-1';
select * from tt where j->'$' = DATE'2019-1-01';