Debian Codegraph @

mysql-test/suite/json/r/json_fulltext_innodb.result

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