Gecko [ www.jdcpatala.org ]

Name	Size	Permission
json_agg.result	34.15 KB	-rw-r--r--
json_client_innodb.result	1.29 KB	-rw-r--r--
json_client_ndb.result	1.43 KB	-rw-r--r--
json_conversions.result	1.57 MB	-rw-r--r--
json_ddl_innodb.result	2.86 KB	-rw-r--r--
json_ddl_ndb.result	3.54 KB	-rw-r--r--
json_fulltext_innodb.result	7 KB	-rw-r--r--
json_functions_innodb.result	260.05 KB	-rw-r--r--
json_functions_ndb.result	271.62 KB	-rw-r--r--
json_gcol_innodb.result	10.8 KB	-rw-r--r--
json_group_concat_innodb.resul...	3.02 KB	-rw-r--r--
json_group_concat_ndb.result	3.09 KB	-rw-r--r--
json_innodb.result	8.52 KB	-rw-r--r--
json_insert_innodb.result	71.19 KB	-rw-r--r--
json_insert_ndb.result	71.59 KB	-rw-r--r--
json_no_table.result	174.17 KB	-rw-r--r--

Code Editor : json_fulltext_innodb.result

set default_storage_engine=innodb;
#
# Full Text Search on columns generated from JSON
#
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 )
);
show create table messages;
Table	Create Table
messages	CREATE TABLE `messages` (
  `id` int(11) NOT NULL,
  `raw_message` json DEFAULT NULL,
  `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,
  PRIMARY KEY (`id`),
  KEY `received` (`received`,`sender`,`receiver`),
  KEY `sender` (`sender`,`receiver`,`received`),
  FULLTEXT KEY `subject` (`subject`),
  FULLTEXT KEY `body` (`body`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
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."
alter table messages add fulltext index (raw_message) ;
ERROR HY000: Column 'raw_message' cannot be part of FULLTEXT index
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;

${this.title}

Code Editor : json_fulltext_innodb.result