Debian Codegraph @

mysql-test/suite/json/inc/json_fulltext.inc

raw
1 --echo #
2 --echo # Full Text Search on columns generated from JSON
3 --echo #
4
5 set names utf8;
6
7 create table messages
8 (
9 id int,
10 raw_message json,
11 sender varchar(50) generated always as (json_unquote( json_extract( raw_message, '$.sender' ) )) stored,
12 receiver varchar(50) generated always as (json_unquote( json_extract( raw_message, '$.receiver' ) )) stored,
13 subject text generated always as (json_extract( raw_message, '$.subject' )) stored,
14 received datetime generated always as (json_unquote( json_extract( raw_message, '$.received' ) )) stored,
15 body text generated always as (json_extract( raw_message, '$.body' )) stored,
16 fulltext key( subject ),
17 fulltext key( body ),
18 primary key( id ),
19 key( received, sender, receiver ),
20 key( sender, receiver, received )
21 );
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
86 analyze table messages;
87
88 select * from messages order by id;
89
90 ## FULLTEXT INDEX can't be created on JSON column type.
91 #--error ER_BAD_FT_COLUMN
92 #alter table messages add fulltext index (raw_message) ;
93
94 # Search the message subject and body
95 select id from messages
96 where match( body ) against ( 'thai' )
97 and match( subject ) against( 'lunch' )
98 order by id;
99 select id from messages
100 where match( body ) against ( 'deadline' )
101 and received > timestamp '2015-05-13 09:45:05'
102 order by id;
103
104 # Search query with condition involving FTS clause and JSON function
105 select id from messages
106 where match( body ) against ( 'thai' )
107 and match( subject ) against( 'lunch' )
108 and JSON_VALID(raw_message)
109 and ( JSON_TYPE(raw_message) = "OBJECT")
110 order by id;
111
112 select id from messages
113 where match( body ) against ( 'thai' )
114 and match( subject ) against( 'lunch' )
115 and ( JSON_TYPE(JSON_KEYS(raw_message)) != "ARRAY" or JSON_CONTAINS (raw_message,CAST('{"received": "2015-05-11 09:30:05"}' AS JSON) ) )
116 order by id;
117
118 select id from messages
119 where match( subject ) against( '+tea' in boolean mode)
120 and LENGTH(JSON_UNQUOTE(JSON_EXTRACT( raw_message, '$.sender' ))) = 5
121 order by id;
122
123 select id from messages
124 where match( body ) against( 'lunch' with query expansion)
125 and JSON_DEPTH(raw_message) != 0;
126
127 # update the message body
128 update messages
129 set raw_message = json_replace(raw_message, '$.body', 'On second thought, I want Chinese.')
130 where id=1;
131 select * from messages order by id;
132 select id from messages
133 where match( body ) against ( 'thai' )
134 and match( subject ) against( 'lunch' )
135 order by id;
136
137 drop table messages;
138