Skip to main content
`Ctrl`+`K`

# 4.8. Prefix search with patricia trie

Groonga supports to create a table with patricia trie option. By specifying it, You can do prefix search.

And more, you can do suffix search against primary key by specifying additional option.

## 4.8.1. Prefix search by primary key

table_create command which uses TABLE_PAT_KEY for flags option supports prefix search by primary key.

Execution example:

    table_create --name PatPrefix --flags TABLE_PAT_KEY --key_type ShortText
    # [[0,1337566253.89858,0.000355720520019531],true]
    load --table PatPrefix
    [
    {"_key":"James"}
    {"_key":"Jason"}
    {"_key":"Jennifer"},
    {"_key":"Jeff"},
    {"_key":"John"},
    {"_key":"Joseph"},
    ]
    # [[0,1337566253.89858,0.000355720520019531],6]
    select --table PatPrefix --query _key:^Je
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         4,
    #         "Jeff"
    #       ],
    #       [
    #         3,
    #         "Jennifer"
    #       ]
    #     ]
    #   ]
    # ]

## 4.8.2. Suffix search by primary key

table_create command which uses TABLE_PAT_KEY and KEY_WITH_SIS for flags option supports prefix search and suffix search by primary key.

If you set KEY_WITH_SIS flag, suffix search records also are added when you add the data. So if you search simply, the automatically added records are hit in addition to the original records. In order to search only the original records, you need a plan.

For example, in order to make this distinction between the original records and automatically added records, add the original column indicating that it is the original record, and add original column is `true` to the search condition. For attention, use `--filter` option because `--query` option is not specify `Bool` type value intuitively.

Execution example:

    table_create --name PatSuffix --flags TABLE_PAT_KEY|KEY_WITH_SIS --key_type ShortText
    # [[0,1337566253.89858,0.000355720520019531],true]
    column_create --table PatSuffix --name original --type Bool
    # [[0,1337566253.89858,0.000355720520019531],true]
    load --table PatSuffix
    [
    {"_key":"ひろゆき","original":true},
    {"_key":"まろゆき","original":true},
    {"_key":"ひろあき","original":true},
    {"_key":"ゆきひろ","original":true}
    ]
    # [[0,1337566253.89858,0.000355720520019531],4]
    select --table PatSuffix --query _key:$ゆき
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         4
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "original",
    #           "Bool"
    #         ]
    #       ],
    #       [
    #         3,
    #         "ゆき",
    #         false
    #       ],
    #       [
    #         2,
    #         "ろゆき",
    #         false
    #       ],
    #       [
    #         5,
    #         "まろゆき",
    #         true
    #       ],
    #       [
    #         1,
    #         "ひろゆき",
    #         true
    #       ]
    #     ]
    #   ]
    # ]
    select --table PatSuffix --filter '_key @$ "ゆき" && original == true'
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "original",
    #           "Bool"
    #         ]
    #       ],
    #       [
    #         5,
    #         "まろゆき",
    #         true
    #       ],
    #       [
    #         1,
    #         "ひろゆき",
    #         true
    #       ]
    #     ]
    #   ]
    # ]