4.10. Let’s create micro-blog¶

Let’s create micro-blog with full text search by Groonga. Micro-blog is one of the broadcast medium in the forms of blog. It is mainly used to post small messages like a Twitter.

4.10.1. Create a table¶

Let’s create table.

table_create --name Users --flags TABLE_HASH_KEY --key_type ShortText
table_create --name Comments --flags TABLE_HASH_KEY --key_type ShortText
table_create --name HashTags --flags TABLE_HASH_KEY --key_type ShortText
table_create --name Bigram --flags TABLE_PAT_KEY --key_type ShortText --default_tokenizer TokenBigram --normalizer NormalizerAuto
table_create --name GeoIndex --flags TABLE_PAT_KEY --key_type WGS84GeoPoint

column_create --table Users --name name --flags COLUMN_SCALAR --type ShortText
column_create --table Users --name follower --flags COLUMN_VECTOR --type Users
column_create --table Users --name favorites --flags COLUMN_VECTOR --type Comments
column_create --table Users --name location --flags COLUMN_SCALAR --type WGS84GeoPoint
column_create --table Users --name location_str --flags COLUMN_SCALAR --type ShortText
column_create --table Users --name description --flags COLUMN_SCALAR --type ShortText
column_create --table Users --name followee --flags COLUMN_INDEX --type Users --source follower

column_create --table Comments --name comment --flags COLUMN_SCALAR --type ShortText
column_create --table Comments --name last_modified --flags COLUMN_SCALAR --type Time
column_create --table Comments --name replied_to --flags COLUMN_SCALAR --type Comments
column_create --table Comments --name replied_users --flags COLUMN_VECTOR --type Users
column_create --table Comments --name hash_tags --flags COLUMN_VECTOR --type HashTags
column_create --table Comments --name location --flags COLUMN_SCALAR --type WGS84GeoPoint
column_create --table Comments --name posted_by --flags COLUMN_SCALAR --type Users
column_create --table Comments --name favorited_by --flags COLUMN_INDEX --type Users --source favorites

column_create --table HashTags --name hash_index --flags COLUMN_INDEX --type Comments --source hash_tags

column_create --table Bigram --name users_index --flags COLUMN_INDEX|WITH_POSITION|WITH_SECTION --type Users --source name,location_str,description
column_create --table Bigram --name comment_index --flags COLUMN_INDEX|WITH_POSITION --type Comments --source comment

column_create --table GeoIndex --name users_location --type Users --flags COLUMN_INDEX --source location
column_create --table GeoIndex --name comments_location --type Comments --flags COLUMN_INDEX --source location

4.10.1.1. Users table¶

This is the table which stores user information. It stores name of user, profile, list of follower and so on.

_key: User ID
name: User name
follower: List of following users
favorites: List of favorite comments
location: Current location of user (geolocation)
location_str: Current location of user (string)
description: User profile
followee: Indexes for follower column in Users table. With this indexes, you can search users who follows the person.

4.10.1.2. Comments table¶

This is the table which stores comments and its metadata. It stores content of comment, posted date, comment which reply to, and so on.

_key: Comment ID
comment: Content of comment
last_modified: Posted date
replied_to: Comment which you reply to someone
replied_users: List of users who you reply to
hash_tags: List of hash tags about comment
location: Posted place (for geolocation)
posted_by: Person who write comment
favorited_by: Indexes for favorites column in Users table. With this indexes, you can search the person who mark comment as favorite one.

4.10.1.3. HashTags table¶

This is the table which stores hash tags for comments.

_key: Hash tag
hash_index: Indexes for Comments.hash_tags. With this indexes, you can search list of comments with specified hash tags.

4.10.1.4. Bigram table¶

This is the table which stores indexes for full text search by user information or comments.

_key: Word
users_index: Indexes of user information. This column contains indexes of user name (Users.name), current location (Users.location_str), profile (Users.description).
comment_index: Indexes about content of comments (Comments.comment).

4.10.1.5. GeoIndex table¶

This is the table which stores indexes of location column to search geo location effectively.

users_location: Indexes of location column for Users table
comments_location: Indexes of location column for Comments table

4.10.2. Loading data¶

Then, load example data.

load --table Users
[
  {
    "_key": "alice",
    "name": "Alice",
    "follower": ["bob"],
    "favorites": [],
    "location": "152489000x-255829000",
    "location_str": "Boston, Massachusetts",
    "description": "Groonga developer"
  },
  {
    "_key": "bob",
    "name": "Bob",
    "follower": ["alice","charlie"],
    "favorites": ["alice:1","charlie:1"],
    "location": "146249000x-266228000",
    "location_str": "Brooklyn, New York City",
    "description": ""
  },
  {
    "_key": "charlie",
    "name": "Charlie",
    "follower": ["alice","bob"],
    "favorites": ["alice:1","bob:1"],
    "location": "146607190x-267021260",
    "location_str": "Newark, New Jersey",
    "description": "Hmm,Hmm"
  }
]

load --table Comments
[
  {
    "_key": "alice:1",
    "comment": "I've created micro-blog!",
    "last_modified": "2010/03/17 12:05:00",
    "posted_by": "alice",
  },
  {
    "_key": "bob:1",
    "comment": "First post. test,test...",
    "last_modified": "2010/03/17 12:00:00",
    "posted_by": "bob",
  },
  {
    "_key": "alice:2",
    "comment": "@bob Welcome!!!",
    "last_modified": "2010/03/17 12:05:00",
    "replied_to": "bob:1",
    "replied_users": ["bob"],
    "posted_by": "alice",
  },
  {
    "_key": "bob:2",
    "comment": "@alice Thanks!",
    "last_modified": "2010/03/17 13:00:00",
    "replied_to": "alice:2",
    "replied_users": ["alice"],
    "posted_by": "bob",
  },
  {
    "_key": "bob:3",
    "comment": "I've just used 'Try-Groonga' now! #groonga",
    "last_modified": "2010/03/17 14:00:00",
    "hash_tags": ["groonga"],
    "location": "146566000x-266422000",
    "posted_by": "bob",
  },
  {
    "_key": "bob:4",
    "comment": "I'm come at city of New York for development camp! #groonga #travel",
    "last_modified": "2010/03/17 14:05:00",
    "hash_tags": ["groonga", "travel"],
    "location": "146566000x-266422000",
    "posted_by": "bob",
  },
  {
    "_key": "charlie:1",
    "comment": "@alice @bob I've tried to register!",
    "last_modified": "2010/03/17 15:00:00",
    "replied_users": ["alice", "bob"],
    "location": "146607190x-267021260",
    "posted_by": "charlie",
  }
  {
    "_key": "charlie:2",
    "comment": "I'm at the Museum of Modern Art in NY now!",
    "last_modified": "2010/03/17 15:05:00",
    "location": "146741340x-266319590",
    "posted_by": "charlie",
  }
]

follower column and favorites column in Users table and replied_users column in Comments table are vector column, so specify the value as an array.

location column in Users table, location column in Comments table use GeoPoint type. This type accepts “[latitude]x[longitude]”.

last_modified column in Comments table use Time type.

There are two way to specify the value. First, specify epoch (seconds since Jan, 1, 1970 AM 00:00:00) directly. In this case, you can specify micro seconds as fractional part. The value is converted from factional part to the time which is micro seconds based one when data is loaded. The second, specify the timestamp as string in following format: “(YEAR)/(MONTH)/(DAY) (HOUR):(MINUTE):(SECOND)”. In this way, the string is casted to proper micro seconds when data is loaded.

4.10.3. Search¶

Let’s search micro-blog.

4.10.3.1. Search users by keyword¶

In this section, we search micro-blog against multiple column by keyword. See match_columns parameter to search multiple column at once.

Let’s search user from micro-blog’s user name, location, description entries.

Execution example:

select --table Users --match_columns name,location_str,description --query "New York" --output_columns _key,name
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         1
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "name",
#           "ShortText"
#         ]
#       ],
#       [
#         "bob",
#         "Bob"
#       ]
#     ]
#   ]
# ]

By using “New York” as searching keyword for user, “Bob” who lives in “New York” is listed in search result.

4.10.3.2. Search users by geolocation data (GeoPoint)¶

In this section, we search users by column data which use type of GeoPoint. See Various search conditions about GeoPoint column.

Following example searches users who live in within 20km from specified location.

Execution example:

select --table Users --filter 'geo_in_circle(location,"146710080x-266315480",20000)' --output_columns _key,name
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         2
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "name",
#           "ShortText"
#         ]
#       ],
#       [
#         "charlie",
#         "Charlie"
#       ],
#       [
#         "bob",
#         "Bob"
#       ]
#     ]
#   ]
# ]

It shows that “Bob” and “Charlie” lives in within 20 km from station of “Grand Central Terminal”.

4.10.3.3. Search users who follows specific user¶

In this section, we do reverse resolution of reference relationships which is described at Tag search and reverse resolution of reference relationships.

Following examples shows reverse resolution about follower column of Users table.

Execution example:

select --table Users --query follower:@bob --output_columns _key,name
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         2
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "name",
#           "ShortText"
#         ]
#       ],
#       [
#         "alice",
#         "Alice"
#       ],
#       [
#         "charlie",
#         "Charlie"
#       ]
#     ]
#   ]
# ]

It shows that “Alice” and “Charlie” follows “Bob”.

4.10.3.4. Search comments by using the value of GeoPoint type¶

In this section, we search comments which are written within specific location.

Then, we also use drill down which is described at Drilldown. Following example shows how to drill down against search results. As a result, we get the value of count which is grouped by user, and hash tags respectively.

Execution example:

select --table Comments --filter 'geo_in_circle(location,"146867000x-266280000",20000)' --output_columns posted_by.name,comment --drilldown hash_tags,posted_by
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         4
#       ],
#       [
#         [
#           "posted_by.name",
#           "ShortText"
#         ],
#         [
#           "comment",
#           "ShortText"
#         ]
#       ],
#       [
#         "Charlie",
#         "I'm at the Museum of Modern Art in NY now!"
#       ],
#       [
#         "Bob",
#         "I've just used 'Try-Groonga' now! #groonga"
#       ],
#       [
#         "Bob",
#         "I'm come at city of New York for development camp! #groonga #travel"
#       ],
#       [
#         "Charlie",
#         "@alice @bob I've tried to register!"
#       ]
#     ],
#     [
#       [
#         2
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "_nsubrecs",
#           "Int32"
#         ]
#       ],
#       [
#         "groonga",
#         2
#       ],
#       [
#         "travel",
#         1
#       ]
#     ],
#     [
#       [
#         2
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "_nsubrecs",
#           "Int32"
#         ]
#       ],
#       [
#         "charlie",
#         2
#       ],
#       [
#         "bob",
#         2
#       ]
#     ]
#   ]
# ]

Above query searches comments which are posted within 20 km from Central Park in city of New York.

As specified range is 20 km, all comments with location are collected. You know that search results contain 2 #groonga hash tags and one #travel hash tag, and bob and charlie posted 2 comments.

4.10.3.5. Search comments by keyword¶

In this section, we search comments which contains specific keyword. And more, Let’s calculate the value of _score which is described at Various search conditions.

Execution example:

select --table Comments --query comment:@Now --output_columns comment,_score
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         2
#       ],
#       [
#         [
#           "comment",
#           "ShortText"
#         ],
#         [
#           "_score",
#           "Int32"
#         ]
#       ],
#       [
#         "I've just used 'Try-Groonga' now! #groonga",
#         1
#       ],
#       [
#         "I'm at the Museum of Modern Art in NY now!",
#         1
#       ]
#     ]
#   ]
# ]

By using ‘Now’ as a keyword, above query returns 2 comments. It also contains count of ‘Now’ as the value of _score.

4.10.3.6. Search comments by keyword and geolocation¶

In this section, we search comments by specific keyword and geolocation. By using –query and –filter option, following query returns records which are matched to both conditions.

Execution example:

select --table Comments --query comment:@New --filter 'geo_in_circle(location,"146867000x-266280000",20000)' --output_columns posted_by.name,comment --drilldown hash_tags,posted_by
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         1
#       ],
#       [
#         [
#           "posted_by.name",
#           "ShortText"
#         ],
#         [
#           "comment",
#           "ShortText"
#         ]
#       ],
#       [
#         "Bob",
#         "I'm come at city of New York for development camp! #groonga #travel"
#       ]
#     ],
#     [
#       [
#         2
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "_nsubrecs",
#           "Int32"
#         ]
#       ],
#       [
#         "groonga",
#         1
#       ],
#       [
#         "travel",
#         1
#       ]
#     ],
#     [
#       [
#         1
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "_nsubrecs",
#           "Int32"
#         ]
#       ],
#       [
#         "bob",
#         1
#       ]
#     ]
#   ]
# ]

It returns 1 comment which meets both condition. It also returns result of drilldown. There is 1 comment which is commented by Bob.

4.10.3.7. Search comments by hash tags¶

In this section, we search comments which contains specific hash tags. Let’s use reverse resolution of reference relationships.

Execution example:

select --table Comments --query hash_tags:@groonga --output_columns posted_by.name,comment --drilldown posted_by
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         2
#       ],
#       [
#         [
#           "posted_by.name",
#           "ShortText"
#         ],
#         [
#           "comment",
#           "ShortText"
#         ]
#       ],
#       [
#         "Bob",
#         "I've just used 'Try-Groonga' now! #groonga"
#       ],
#       [
#         "Bob",
#         "I'm come at city of New York for development camp! #groonga #travel"
#       ]
#     ],
#     [
#       [
#         1
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "_nsubrecs",
#           "Int32"
#         ]
#       ],
#       [
#         "bob",
#         2
#       ]
#     ]
#   ]
# ]

Above query returns 2 comments which contains #groonga hash tag. It also returns result of drilldown grouped by person who posted it. It shows that there are 2 comments. Bob commented it.

4.10.3.8. Search comments by user id¶

In this section, we search comments which are posted by specific user.

Execution example:

select --table Comments --query posted_by:bob --output_columns comment --drilldown hash_tags
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         4
#       ],
#       [
#         [
#           "comment",
#           "ShortText"
#         ]
#       ],
#       [
#         "First post. test,test..."
#       ],
#       [
#         "@alice Thanks!"
#       ],
#       [
#         "I've just used 'Try-Groonga' now! #groonga"
#       ],
#       [
#         "I'm come at city of New York for development camp! #groonga #travel"
#       ]
#     ],
#     [
#       [
#         2
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "_nsubrecs",
#           "Int32"
#         ]
#       ],
#       [
#         "groonga",
#         2
#       ],
#       [
#         "travel",
#         1
#       ]
#     ]
#   ]
# ]

Above query returns 4 comments which are posted by Bob. It also returns result of drilldown by hash tags. There are 2 comments which contains #groonga, and 1 comment which contains #travel as hash tag.

4.10.3.9. Search user’s favorite comments¶

In this section, we search user’s favorite comments.

Execution example:

select --table Users --query _key:bob --output_columns favorites.posted_by,favorites.comment
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         1
#       ],
#       [
#         [
#           "favorites.posted_by",
#           "Users"
#         ],
#         [
#           "favorites.comment",
#           "ShortText"
#         ]
#       ],
#       [
#         [
#           "alice",
#           "charlie"
#         ],
#         [
#           "I've created micro-blog!",
#           "@alice @bob I've tried to register!"
#         ]
#       ]
#     ]
#   ]
# ]

Above query returns Bob’s favorite comments.

4.10.3.10. Search comments by posted time¶

In this section, we search comments by posted time. See type of Time in Various data types.

Let’s search comments that posted time are older than specified time.

Execution example:

select Comments --filter 'last_modified<=1268802000' --output_columns posted_by.name,comment,last_modified --drilldown hash_tags,posted_by
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         5
#       ],
#       [
#         [
#           "posted_by.name",
#           "ShortText"
#         ],
#         [
#           "comment",
#           "ShortText"
#         ],
#         [
#           "last_modified",
#           "Time"
#         ]
#       ],
#       [
#         "Alice",
#         "I've created micro-blog!",
#         1268795100.0
#       ],
#       [
#         "Bob",
#         "First post. test,test...",
#         1268794800.0
#       ],
#       [
#         "Alice",
#         "@bob Welcome!!!",
#         1268795100.0
#       ],
#       [
#         "Bob",
#         "@alice Thanks!",
#         1268798400.0
#       ],
#       [
#         "Bob",
#         "I've just used 'Try-Groonga' now! #groonga",
#         1268802000.0
#       ]
#     ],
#     [
#       [
#         1
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "_nsubrecs",
#           "Int32"
#         ]
#       ],
#       [
#         "groonga",
#         1
#       ]
#     ],
#     [
#       [
#         2
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "_nsubrecs",
#           "Int32"
#         ]
#       ],
#       [
#         "alice",
#         2
#       ],
#       [
#         "bob",
#         3
#       ]
#     ]
#   ]
# ]

Above query returns 5 comments which are older than 2010/03/17 14:00:00. It also returns result of drilldown by posted person. There are 2 comments by Alice, 3 comments by Bob.

4.9. Additional information about lexicon for full text search

4.11. Query expansion