How can I index multi string, date, ... in manticoresearch

[alexander-schranz]

I'm currently working on a search engine abstraction for PHP and try to add support for manticoresearch. I already flatten my complex JSON object into a flat JSON object which I already did for the Apache Solr Adapter. So my object look like this:

{
    "id": "23b30f01-d8fd-4dca-b36a-4710e360a965",
    "title": "New Blog",
    "header_image_media": 1,
    "article": "<article><h2>New Subtitle<\/h2><p>A html field with some content<\/p><\/article>",
    "blocks_text_title": [
        "Titel",
        "Titel 2",
        "Titel 4"
    ],
    "blocks_text_description": [
        "<p>Description<\/p>",
        "<p>Description 4<\/p>"
    ],
    "blocks_text_media": [
        3,
        4,
        3,
        4
    ],
    "blocks_embed_title": [
        "Video"
    ],
    "blocks_embed_media": [
        "https:\/\/www.youtube.com\/watch?v=iYM2zFP3Zn0"
    ],
    "footer_title": "New Footer",
    "created": 1643022000,
    "commentsCount": 2,
    "rating": 3.5,
    "comments_email": [
        "admin.nonesearchablefield@localhost",
        "example.nonesearchablefield@localhost"
    ],
    "comments_text": [
        "Awesome blog!",
        "Like this blog!"
    ],
    "tags": [
        "Tech",
        "UI"
    ],
    "categoryIds": [
        1,
        2
    ],
    "_source": "{\"title\":\"New Blog\",\"header\":{\"type\":\"image\",\"media\":1},\"article\":\"<article><h2>New Subtitle<\\\/h2><p>A html field with some content<\\\/p><\\\/article>\",\"blocks\":[{\"type\":\"text\",\"title\":\"Titel\",\"description\":\"<p>Description<\\\/p>\",\"media\":[3,4]},{\"type\":\"text\",\"title\":\"Titel 2\"},{\"type\":\"embed\",\"title\":\"Video\",\"media\":\"https:\\\/\\\/www.youtube.com\\\/watch?v=iYM2zFP3Zn0\"},{\"type\":\"text\",\"title\":\"Titel 4\",\"description\":\"<p>Description 4<\\\/p>\",\"media\":[3,4]}],\"footer\":{\"title\":\"New Footer\"},\"created\":\"2022-01-24T12:00:00+01:00\",\"commentsCount\":2,\"rating\":3.5,\"comments\":[{\"email\":\"admin.nonesearchablefield@localhost\",\"text\":\"Awesome blog!\"},{\"email\":\"example.nonesearchablefield@localhost\",\"text\":\"Like this blog!\"}],\"tags\":[\"Tech\",\"UI\"],\"categoryIds\":[1,2]}"
}

I could not yet found out what the correct way is to index list of strings / text or list of dates, list of floats, ... I only found out there is multi and multi64 type which is only for "integers". What is the correct way to index such kind of data.

Also every field can be filterable. The abstract Schema I'm using which I need to map to Manticore looks like this:

$fields = [
    'id' => new Field\IdentifierField('id'),
    'title' => new Field\TextField('title'),
    'header' => new Field\TypedField('header', 'type', [
        'image' => [
            'media' => new Field\IntegerField('media'),
        ],
        'video' => [
            'media' => new Field\TextField('media'),
        ],
    ]),
    'article' => new Field\TextField('article'),
    'blocks' => new Field\TypedField('blocks', 'type', [
        'text' => [
            'title' => new Field\TextField('title'),
            'description' => new Field\TextField('description'),
            'media' => new Field\IntegerField('media', multiple: true),
        ],
        'embed' => [
            'title' => new Field\TextField('title'),
            'media' => new Field\TextField('media'),
        ],
    ], multiple: true),
    'footer' => new Field\ObjectField('footer', [
        'title' => new Field\TextField('title'),
    ]),
    'created' => new Field\DateTimeField('created'),
    'commentsCount' => new Field\IntegerField('commentsCount'),
    'rating' => new Field\FloatField('rating'),
    'comments' => new Field\ObjectField('comments', [
        'email' => new Field\TextField('email'),
        'text' => new Field\TextField('title'),
    ], multiple: true),
    'tags' => new Field\TextField('tags', multiple: true),
    'categoryIds' => new Field\IntegerField('categoryIds', multiple: true),
];

All field types can be multiple. text type can be notsearchable, all other types like bool, int, float, date can be filterable or sortable.

Can somebody give a hint how to achieve first multi fields of other types then integer and how to make the different types filterable and sortable.

[sanikolaev]

I could not yet found out what the correct way is to index list of strings / text or list of dates, list of floats, ... I only found out there is multi and multi64 type which is only for "integers". What is the correct way to index such kind of data.

You have to use "json" attribute type for such data types.

how to make the different types filterable and sortable.

Here's a brief overview of what we have in Manticore:

• fields:
  • full-text fields:
    • can be stored (by default, e.g. create table t(f text)), then you can get the original value and full-text filter by it
    • can be just "indexed" (e.g. create table t(f text indexed)), then the original value is not saved
    • can be stored only (e.g. create table t(f text stored)), then you can't filter by them. Otherwise you can filter by them using MATCH() via SQL or "match" etc. in JSON.
    • full-text fields are sortable only through full-text ranking
  • non-full-text fields AKA "attributes":
    • are always filterable
    • are always sortable
    • the original values are always saved
  • you can have a string attribute and a full-text field under the same name (e.g. create table t(f text attribute)), then you can filter and sort by it both through the full-text operators (e.g. MATCH()) and just = for exact match.

[alexander-schranz]

@sanikolaev First really thank you for your response here. I'm using the manticoresearch-php package

You have to use "json" attribute type for such data types.

Is the JSON field not unstructured and not indexed? As example the tags:

    "tags": [
        "Tech",
        "UI"
    ],

Currently in some other engines (e.g. Apache Solr) I created it this way to duplicate the field one is for the full text search and one is for filters:

    "tags": [
        "Tech",
        "UI"
    ],
    "tags.raw": [
        "Tech",
        "UI"
    ],

That I have tags for full text search and tags.raw for the filters. So I have here two questions.

A. Would I need to flatten the tags to be a string field here.

Like e.g.:

    "tags": "Tech UI",
    "tags.raw": [
        "Tech",
        "UI"
    ],

If yes what would be the best seperator should I go with "tags": "Tech, UI" for the full text search field. Or is this handled internal by manticore to convert indexed text fields automatically and I don't need to take care of it by maybe additoinal options e.g. multiple?

B. How can I filter by tags.raw when it is a unstructured json type (e.g. blog overview give me all blogs with tag UI), how would that look like in via the Boolquery?

For a better overview currently I have the following queries I need to implement:

• Full Text Search over all searchable fields (only text fields can be searchable but it can be they are a array of texts like tags or block_text_title)
• Filter Equal e.g. tags.raw = "UI"
• Filter Not Equal e.g. tags.raw != "UI"
• Filter Multi Equal e.g. tags.raw = "UI" AND tags.raw = "Tech"
• Filter GreaterThan e.g. rating > 2.5
• Filter GreaterThanEqual e.g. rating >= 2.5
• Filter LessThan e.g. rating < 2.5
• Filter LessThanEqual e.g. rating <= 2.5
• Filter LIMIT / OFFSET e.g. OFFSET 10 LIMIT 5
• Sorting: SORT BY e.g. SORT BY rating DESC

[barryhunter]

You can use 'ANY()' for matching any value in list.

Filter Equal, SELECT *, ANY(tag = 'UI' FOR tag IN tags.raw) as filter FROM index WHERE filter=1
Filter Not Equal, SELECT *, ANY(tag = 'UI' FOR tag IN tags.raw) as filter FROM index WHERE filter=0
Filter Multi Equal, SELECT *, ANY(tag = 'UI' FOR tag IN tags.raw)+ANY(tag = 'Tech' FOR tag IN tags.raw) as filter FROM index WHERE filter=2

[alexander-schranz]

Thx @barryhunter do you maybe know how to achieve this kind of filtering via the client package: https://github.com/manticoresoftware/manticoresearch-php/blob/master/docs/query.md. As there seems nothing in the docs about ANY there?

[barryhunter]

Oh, sorry, forgot you said using the new-fangled PHP client (I still use the old one, although mostly moved to using mysql-client directly)

Seems you can technically run 'low level' SQL queries
https://github.com/manticoresoftware/manticoresearch-php/blob/master/docs/sql.md
But does mean you need to form the SQL command yourself.

Or maybe can
https://github.com/manticoresoftware/manticoresearch-php/blob/master/docs/searchclass.md
I think the 'setSource' is the same as 'SELECT' in SQL, but not sure if allows defining virtual attributes or not.

[sanikolaev]

There are script_fields and expressions in the JSON interface and it should be possible to use them for ANY() with the low-level client.

[barryhunter]

Just to expand on what already said, attributes are definitely the way for 'filtering' and 'sorting' - JSON allows for even multi-value strings.

But there isn't a concept of a multi searchable text field. Full text fields are single value.
... but yes, could 'join' the values into a single string for full text indexing and searching purposes. Can even use a specific character, configured as a phrase boundary
https://manual.manticoresearch.com/Creating_a_table/NLP_and_tokenization/Low-level_tokenization#phrase_boundary
... that will allow some searching possibilities within a single 'value'. (ie require a match within a single value, rather than matching all values)

[alexander-schranz]

Will have a look at the phrase_boundary think that join make the most sense here, just want to make sure I'm not doing something wrong here as the Abstraction is planned to use by a few CMS systems.

And think split a field which is both filterable and searchable into two seperate fields to store them both most efficient is fine and shoult not be to much work here.