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;