4.6. Tag search and reverse resolution of reference relationships

As you know, Groonga supports to store array in column which refers other table. In fact, you can do tag search by using array data which refers other table.

Tag search is very fast because Groonga use inverted index as data structure.

4.6.2. Reverse resolution of reference relationships

Groonga supports indexes for reverse resolution among tables. Tag search is one of concrete examples.

For example, you can search friendships by reverse resolution in social networking site.

Following example shows how to create User table which stores user information, username column which stores user name, friends column which stores list of user’s friends in array, index_friends column as indexed column.

Execution example:

table_create --name User --flags TABLE_HASH_KEY --key_type ShortText
# [[0, 1337566253.89858, 0.000355720520019531], true]
column_create --table User --name username --flags COLUMN_SCALAR --type ShortText
# [[0, 1337566253.89858, 0.000355720520019531], true]
column_create --table User --name friends --flags COLUMN_VECTOR --type User
# [[0, 1337566253.89858, 0.000355720520019531], true]
column_create --table User --name index_friends --flags COLUMN_INDEX --type User --source friends
# [[0, 1337566253.89858, 0.000355720520019531], true]
load --table User
[
{"_key":"ken","username":"健作","friends":["taro","jiro","tomo","moritapo"]}
{"_key":"moritapo","username":"森田","friends":["ken","tomo"]}
{"_key":"taro","username":"ぐるんが太郎","friends":["jiro","tomo"]}
{"_key":"jiro","username":"ぐるんが次郎","friends":["taro","tomo"]}
{"_key":"tomo","username":"トモちゃん","friends":["ken","hana"]}
{"_key":"hana","username":"花子","friends":["ken","taro","jiro","moritapo","tomo"]}
]
# [[0, 1337566253.89858, 0.000355720520019531], 6]

Let’s show list of users who contains specified user in friend list.

Execution example:

select --table User --query friends:@tomo --output_columns _key,username
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         5
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "username",
#           "ShortText"
#         ]
#       ],
#       [
#         "ken",
#         "健作"
#       ],
#       [
#         "taro",
#         "ぐるんが太郎"
#       ],
#       [
#         "jiro",
#         "ぐるんが次郎"
#       ],
#       [
#         "moritapo",
#         "森田"
#       ],
#       [
#         "hana",
#         "花子"
#       ]
#     ]
#   ]
# ]
select --table User --query friends:@jiro --output_columns _key,username
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         3
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "username",
#           "ShortText"
#         ]
#       ],
#       [
#         "ken",
#         "健作"
#       ],
#       [
#         "taro",
#         "ぐるんが太郎"
#       ],
#       [
#         "hana",
#         "花子"
#       ]
#     ]
#   ]
# ]

Then drilldown the count which shows user is listed as friend.

Execution example:

select --table User --limit 0 --drilldown friends
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         6
#       ],
#       [
#         [
#           "_id",
#           "UInt32"
#         ],
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "friends",
#           "User"
#         ],
#         [
#           "index_friends",
#           "UInt32"
#         ],
#         [
#           "username",
#           "ShortText"
#         ]
#       ]
#     ],
#     [
#       [
#         6
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "_nsubrecs",
#           "Int32"
#         ]
#       ],
#       [
#         "taro",
#         3
#       ],
#       [
#         "jiro",
#         3
#       ],
#       [
#         "tomo",
#         5
#       ],
#       [
#         "moritapo",
#         2
#       ],
#       [
#         "ken",
#         3
#       ],
#       [
#         "hana",
#         1
#       ]
#     ]
#   ]
# ]

As you can see, it shows the results which follows reverse resolution of reference relationship.

4.6.3. Geo location search with index

Groonga supports to add indexes to the column which stores geo location information. Groonga is very fast because it use such indexes against the column which contains geo location information to search enormous number of records.

Execution example:

table_create --name GeoSite --flags TABLE_HASH_KEY --key_type ShortText
# [[0, 1337566253.89858, 0.000355720520019531], true]
column_create --table GeoSite --name location --type WGS84GeoPoint
# [[0, 1337566253.89858, 0.000355720520019531], true]
table_create --name GeoIndex --flags TABLE_PAT_KEY --key_type WGS84GeoPoint
# [[0, 1337566253.89858, 0.000355720520019531], true]
column_create --table GeoIndex --name index_point --type GeoSite --flags COLUMN_INDEX --source location
# [[0, 1337566253.89858, 0.000355720520019531], true]
load --table GeoSite
[
 {"_key":"http://example.org/","location":"128452975x503157902"},
 {"_key":"http://example.net/","location":"128487316x502920929"}
]
# [[0, 1337566253.89858, 0.000355720520019531], 2]
select --table GeoSite --filter 'geo_in_circle(location, "128515259x503187188", 5000)' --output_columns _key,location
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         1
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "location",
#           "WGS84GeoPoint"
#         ]
#       ],
#       [
#         "http://example.org/",
#         "128452975x503157902"
#       ]
#     ]
#   ]
# ]

These indexes are also used when sorting the records with geo location search.

Execution example:

select --table GeoSite --filter 'geo_in_circle(location, "128515259x503187188", 50000)' --output_columns _key,location,_score --sort_keys '-geo_distance(location, "128515259x503187188")' --scorer '_score = geo_distance(location, "128515259x503187188")'
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         2
#       ],
#       [
#         [
#           "_key",
#           "ShortText"
#         ],
#         [
#           "location",
#           "WGS84GeoPoint"
#         ],
#         [
#           "_score",
#           "Int32"
#         ]
#       ],
#       [
#         "http://example.org/",
#         "128452975x503157902",
#         2054
#       ],
#       [
#         "http://example.net/",
#         "128487316x502920929",
#         6720
#       ]
#     ]
#   ]
# ]