1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
|
--echo #
--echo # Full Text Search on columns generated from JSON
--echo #
set names utf8;
create table messages
(
id int,
raw_message json,
sender varchar(50) generated always as (json_unquote( json_extract( raw_message, '$.sender' ) )) stored,
receiver varchar(50) generated always as (json_unquote( json_extract( raw_message, '$.receiver' ) )) stored,
subject text generated always as (json_extract( raw_message, '$.subject' )) stored,
received datetime generated always as (json_unquote( json_extract( raw_message, '$.received' ) )) stored,
body text generated always as (json_extract( raw_message, '$.body' )) stored,
fulltext key( subject ),
fulltext key( body ),
primary key( id ),
key( received, sender, receiver ),
key( sender, receiver, received )
);
insert into messages(id, raw_message) values
(
1,
'{
"sender": "fred",
"receiver": "alice",
"subject": "lunch today?",
"received": "2015-05-11 09:30:05",
"body": "How about lunch around 11:30? Thai food sounds good to me."
}'
),
(
2,
'{
"sender": "alice",
"receiver": "fred",
"subject": "re: lunch today?",
"received": "2015-05-11 09:45:05",
"body": "Great! No big deadline today."
}'
),
(
3,
'{
"sender": "fred",
"receiver": "alice",
"subject": "tea at 4:00?",
"received": "2015-05-11 14:30:05",
"body": "I have some yummy scones."
}'
),
(
4,
'{
"sender": "alice",
"receiver": "fred",
"subject": "re: tea at 4:00?",
"received": "2015-05-11 14:45:05",
"body": "Yes indeed."
}'
),
(
5,
'{
"sender": "fred",
"receiver": "alice",
"subject": "lunch today?",
"received": "2015-05-18 09:30:05",
"body": "Chinese for lunch?"
}'
),
(
6,
'{
"sender": "alice",
"receiver": "fred",
"subject": "re: lunch today?",
"received": "2015-05-18 09:45:05",
"body": "Sorry. I have a tight deadline I have to meet."
}'
)
;
analyze table messages;
select * from messages order by id;
## FULLTEXT INDEX can't be created on JSON column type.
#--error ER_BAD_FT_COLUMN
#alter table messages add fulltext index (raw_message) ;
# Search the message subject and body
select id from messages
where match( body ) against ( 'thai' )
and match( subject ) against( 'lunch' )
order by id;
select id from messages
where match( body ) against ( 'deadline' )
and received > timestamp '2015-05-13 09:45:05'
order by id;
# Search query with condition involving FTS clause and JSON function
select id from messages
where match( body ) against ( 'thai' )
and match( subject ) against( 'lunch' )
and JSON_VALID(raw_message)
and ( JSON_TYPE(raw_message) = "OBJECT")
order by id;
select id from messages
where match( body ) against ( 'thai' )
and match( subject ) against( 'lunch' )
and ( JSON_TYPE(JSON_KEYS(raw_message)) != "ARRAY" or JSON_CONTAINS (raw_message,CAST('{"received": "2015-05-11 09:30:05"}' AS JSON) ) )
order by id;
select id from messages
where match( subject ) against( '+tea' in boolean mode)
and LENGTH(JSON_UNQUOTE(JSON_EXTRACT( raw_message, '$.sender' ))) = 5
order by id;
select id from messages
where match( body ) against( 'lunch' with query expansion)
and JSON_DEPTH(raw_message) != 0;
# update the message body
update messages
set raw_message = json_replace(raw_message, '$.body', 'On second thought, I want Chinese.')
where id=1;
select * from messages order by id;
select id from messages
where match( body ) against ( 'thai' )
and match( subject ) against( 'lunch' )
order by id;
drop table messages;
|
