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
139
140
141
142
143
144
145
146
147
148
149
150
151
|
set default_storage_engine=innodb;
#
# Full Text Search on columns generated from JSON
#
set names utf8;
Warnings:
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
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;
Table Op Msg_type Msg_text
test.messages analyze status OK
select * from messages order by id;
id raw_message sender receiver subject received body
1 {"body": "How about lunch around 11:30? Thai food sounds good to me.", "sender": "fred", "subject": "lunch today?", "received": "2015-05-11 09:30:05", "receiver": "alice"} fred alice "lunch today?" 2015-05-11 09:30:05 "How about lunch around 11:30? Thai food sounds good to me."
2 {"body": "Great! No big deadline today.", "sender": "alice", "subject": "re: lunch today?", "received": "2015-05-11 09:45:05", "receiver": "fred"} alice fred "re: lunch today?" 2015-05-11 09:45:05 "Great! No big deadline today."
3 {"body": "I have some yummy scones.", "sender": "fred", "subject": "tea at 4:00?", "received": "2015-05-11 14:30:05", "receiver": "alice"} fred alice "tea at 4:00?" 2015-05-11 14:30:05 "I have some yummy scones."
4 {"body": "Yes indeed.", "sender": "alice", "subject": "re: tea at 4:00?", "received": "2015-05-11 14:45:05", "receiver": "fred"} alice fred "re: tea at 4:00?" 2015-05-11 14:45:05 "Yes indeed."
5 {"body": "Chinese for lunch?", "sender": "fred", "subject": "lunch today?", "received": "2015-05-18 09:30:05", "receiver": "alice"} fred alice "lunch today?" 2015-05-18 09:30:05 "Chinese for lunch?"
6 {"body": "Sorry. I have a tight deadline I have to meet.", "sender": "alice", "subject": "re: lunch today?", "received": "2015-05-18 09:45:05", "receiver": "fred"} alice fred "re: lunch today?" 2015-05-18 09:45:05 "Sorry. I have a tight deadline I have to meet."
select id from messages
where match( body ) against ( 'thai' )
and match( subject ) against( 'lunch' )
order by id;
id
1
select id from messages
where match( body ) against ( 'deadline' )
and received > timestamp '2015-05-13 09:45:05'
order by id;
id
6
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;
id
1
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;
id
1
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;
id
4
select id from messages
where match( body ) against( 'lunch' with query expansion)
and JSON_DEPTH(raw_message) != 0;
id
1
5
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;
id raw_message sender receiver subject received body
1 {"body": "On second thought, I want Chinese.", "sender": "fred", "subject": "lunch today?", "received": "2015-05-11 09:30:05", "receiver": "alice"} fred alice "lunch today?" 2015-05-11 09:30:05 "On second thought, I want Chinese."
2 {"body": "Great! No big deadline today.", "sender": "alice", "subject": "re: lunch today?", "received": "2015-05-11 09:45:05", "receiver": "fred"} alice fred "re: lunch today?" 2015-05-11 09:45:05 "Great! No big deadline today."
3 {"body": "I have some yummy scones.", "sender": "fred", "subject": "tea at 4:00?", "received": "2015-05-11 14:30:05", "receiver": "alice"} fred alice "tea at 4:00?" 2015-05-11 14:30:05 "I have some yummy scones."
4 {"body": "Yes indeed.", "sender": "alice", "subject": "re: tea at 4:00?", "received": "2015-05-11 14:45:05", "receiver": "fred"} alice fred "re: tea at 4:00?" 2015-05-11 14:45:05 "Yes indeed."
5 {"body": "Chinese for lunch?", "sender": "fred", "subject": "lunch today?", "received": "2015-05-18 09:30:05", "receiver": "alice"} fred alice "lunch today?" 2015-05-18 09:30:05 "Chinese for lunch?"
6 {"body": "Sorry. I have a tight deadline I have to meet.", "sender": "alice", "subject": "re: lunch today?", "received": "2015-05-18 09:45:05", "receiver": "fred"} alice fred "re: lunch today?" 2015-05-18 09:45:05 "Sorry. I have a tight deadline I have to meet."
select id from messages
where match( body ) against ( 'thai' )
and match( subject ) against( 'lunch' )
order by id;
id
drop table messages;
|
