Search with asp.net client

General Discussions - English
40

Can anyone please suggest, how to use below query as JSON ?
SELECT * FROM testjson WHERE REGEX(metadata.cpu.model, ‘Cortex A.*’);

Here is a example for same but it is using SQL but I want to use as a JSON
https://play.manticoresearch.com/json/

41 
➜  ~ mysql -v -P9306 -h0 -e "drop table t; create table t(metadata json); insert into t(metadata) values('{\"cpu\": {\"model\": \"Cortex ABC\"}}'); SELECT * FROM t WHERE REGEX(metadata.cpu.model, 'Cortex A.*');"

--------------
drop table t
--------------

--------------
create table t(metadata json)
--------------

--------------
insert into t(metadata) values('{"cpu": {"model": "Cortex ABC"}}')
--------------

--------------
SELECT * FROM t WHERE REGEX(metadata.cpu.model, 'Cortex A.*')
--------------

+---------------------+--------------------------------+
| id                  | metadata                       |
+---------------------+--------------------------------+
| 1515388920550916275 | {"cpu":{"model":"Cortex ABC"}} |
+---------------------+--------------------------------+
➜  ~ curl -sX POST http://localhost:9308/search  -d '
{
  "index":"t",
  "query": {
    "equals": { "cond": 1 }
  },
  "expressions": {
    "cond": "REGEX(metadata.cpu.model, '\''Cortex A.*'\'')"
  }
}'|jq .
{
  "took": 0,
  "timed_out": false,
  "hits": {
    "total": 1,
    "total_relation": "eq",
    "hits": [
      {
        "_id": "1515388920550916275",
        "_score": 1,
        "_source": {
          "metadata": {
            "cpu": {
              "model": "Cortex ABC"
            }
          },
          "cond": 1
        }
      }
    ]
  }
}
42

Okay thanks.
But Now I want to use equals condition with “Match” with “OR” condition (with different fields).

Suppose I have fields “name, description, sku and comments” and I want to use “Match” with “OR” condition for this fields and also want to use above expression at SAME time.

I want to search with “Cortex A” using match (with or) and equals condition at same time (in single request) with “Cortex A” keywords.

Can I get result in if match but not equals ?

Please suggest me with an example ?

43

Like infix_fields, can we declare exact fields ?
I want to exact match for only one field like “SKU” while for other fields (Name,ShortDescription,FullDescription) I want to use infix_fields

Because I want to exact search from “SKU” if only exact match
Suppose I have SKU with value “AP_MBP_PRO” and when I search with keyword “AP_MBP” I don’t want to check in SKU field when I perform match query.

Suggest me about this…

44

Can I search from multiple fields using expression ?
If yes then how ?

here is a example for single fields but I want to search from multiple fields

{
  "index":"t",
  "query": {
    "equals": { "cond": 1 }
  },
  "expressions": {
    "cond": "REGEX(metadata.cpu.model, '\''Cortex A.*'\'')"
  }
45

I want to search with “Cortex A” using match (with or) and equals condition at same time (in single request) with “Cortex A” keywords.

Can I get result in if match but not equals ?

Please read this Manticore Search Manual: Searching > Filters

Like infix_fields, can we declare exact fields ?

Yes. Read this Manticore Search Manual: Creating a table > NLP and tokenization > Wildcard searching settings

Can I search from multiple fields using expression ?
If yes then how ?

  "expressions": {
    "cond": "REGEX(metadata.cpu.model, '\''Cortex A.*'\'') OR REGEX(metadata.cpu.model2, '\''Cortex A.*'\'') "
  }
46

Ok thank for your reply.
It is working fine for expression, but

I am getting wrong result when I request for search using match_phrase and expression at same time

I want to search with “apple” keywords from my fields datatype with text “sku,productattributecombinationsku,manufacturerpartnumber” and datatype with JSON “name.en”

And I want to OR condition for all fields including text and json datatype. So what is wrong with my search request ?
I also try with must in place of should but I get same result for both must and should condition.
Also I tried with your given links for filter and I tried same.

var query = {"bool":{"should":[{"match_phrase": { "sku,productattributecombinationsku,manufacturerpartnumber": { "query": "apple", "operator":"or" }},"equals":{"cond": 1}}]}}

var expression = {"cond": "REGEX(name.en, '(?i)apple*')"}

var searchRequest = new SearchRequest(index: "products1", query: query, expressions: expressions);

47

Please provide an example in the same format as mine above, so it’s clear

  • how you created the table
  • what data you inserted into it
  • what query you made which failed
  • what it returned
48

Here Is My Table

create table products1 (ProductId text,Sku text,Name json,ShortDescription json,FullDescription text,Price float,Published bool,ManufacturerPartNumber text,VendorId integer,CreatedOnUtc bigint,CategoryIds multi,ManufacturerIds multi,ProductTagIds multi,SpecificationOptionIds multi,ProductAttributeCombinationSku text,Manufacturers json,Categories json) min_infix_len = '3' expand_keywords = '1'

In this table Name is JSON datatype while FullDescription is text datatype

Here is my data in JSON format

[
  {
    "insert": {
      "index": "products1",
      "id": 1,
      "doc": {
        "ProductId": "1",
        "Sku": "COMP_CUST_UPDATE_1",
        "FullDescription": "<p>Fight back against cluttered workspaces with the stylish IBM zBC12 All-in-One desktop PC, featuring powerful computing resources and a stunning 20.1-inch widescreen display with stunning XBRITE-HiColor LCD technology.</p>",
        "Price": 1200.0000,
        "Published": true,
        "ManufacturerPartNumber": "",
        "CreatedOnUtc": 638145534882226590,
        "VendorId": 0,
        "CategoryIds": [
          2,
          1
        ],
        "ManufacturerIds": [
          6
        ],
        "ProductTagIds": [
          1,
          2
        ],
        "SpecificationOptionIds": [],
        "ProductAttributeCombinationSku": "Build_PAC_1",
        "Name": {
          "en": "Apple iMac",
          "hi": "बिल्ड-अपना-कंप्यूटर"
        },
        "ShortDescription": {
          "en": "Build it Update by schedule task",
          "hi": "Build it Update by schedule task"
        },
        "Manufacturers": {
          "en": [
            "Refurbished Computer"
          ],
          "hi": [
            "नवीनीकृत कंप्यूटर"
          ]
        },
        "Categories": {
          "en": [
            "Desktops",
            "Computers"
          ],
          "hi": [
            "Desktops",
            "Computers"
          ]
        }
      }
    }
  },
  {
    "insert": {
      "index": "products1",
      "id": 2,
      "doc": {
        "ProductId": "2",
        "Sku": "DS_VA3_PC",
        "FullDescription": "<p>Apple Macbook pro system is put through an extensive stress test, ensuring you experience zero bottlenecks and get the maximum performance from your hardware.</p>",
        "Price": 1259.0000,
        "Published": true,
        "ManufacturerPartNumber": "",
        "CreatedOnUtc": 638145534885417350,
        "VendorId": 0,
        "CategoryIds": [
          2
        ],
        "ManufacturerIds": [],
        "ProductTagIds": [
          1,
          2,
          3
        ],
        "SpecificationOptionIds": [],
        "ProductAttributeCombinationSku": "",
        "Name": {
          "en": "Digital Storm VANQUISH 3 Custom Performance PC",
          "hi": "Digital Storm VANQUISH 3 Custom Performance PC"
        },
        "ShortDescription": {
          "en": "Digital Storm Vanquish 3 Desktop PC",
          "hi": "Digital Storm Vanquish 3 Desktop PC"
        },
        "Manufacturers": {},
        "Categories": {
          "en": [
            "Desktops"
          ],
          "hi": [
            "Desktops"
          ]
        }
      }
    }
  }
]

You can see there is “Apple” word in first record in “Name” column while in second record it is in “FullDescription” column.

Now I want to search with “Apple” keyword and want to both record as a result, So what should be my search request ?

Currently I am getting single record but I want to both record as a result and My search request is mentioned in my previous question.
Here Search with asp.net client - #46 by Kamlesh

49

I checked this but there is not in details and no proper example.
I want to exact search with only one field while I want to search from other fields as infix search.
So how can I set exact for single fields/column ?

50

Smth like this: where match('@field1 smth @field2 *smth*')

Currently I am getting single record but I want to both record as a result and My search request is mentioned in my previous question.
Here Search with asp.net client - #46 by Kamlesh

If you want me to help you, please provide a full example which I can just paste to the terminal which will reproduce your issue, it should: create a table, populate it with data and attempt to search in it. All using mysql -P9306 -h0 or curl ... commands. Sorry I don’t have time to gather it up it from pieces from multiple comments or write a program in C#.

51

Okay thank you.

I tried with SQL, it is working fine but how to use this as JSON with OR operator?
Help me for this please.

52 
mysql -P9306 -h0 -e "drop table if exists t; create table t(field1 text, field2 text) min_infix_len='2'; insert into t(field1, field2) values('smth', 'abcsmthabc')"
curl -sX POST http://localhost:9308/search  -d '
{
  "index": "t",
  "query": {
    "bool": {
      "must": [
        { "match" : { "field1" : "smth" } },
        { "match" : { "field2" : "*smth*" } }
      ]
    }
  }
}'|jq .

{
  "took": 0,
  "timed_out": false,
  "hits": {
    "total": 1,
    "total_relation": "eq",
    "hits": [
      {
        "_id": "1515398726598787073",
        "_score": 2428,
        "_source": {
          "field1": "smth",
          "field2": "abcsmthabc"
        }
      }
    ]
  }
}
1 Like
53

There is any flag for stop auto spell corrections ?

I read this but not found anything
https://manual.manticoresearch.com/Searching/Spell_correction#Spell-correction

54

Unable to search with special character
I can sync special character but unable to search using that

Can you please suggest how to search with special character ?

here I tried with below example
CREATE TABLE testjson(name string indexed attribute)

and inserted data as below and inserted successfully

insert into testjson values(1, 'kamlesh')
insert into testjson values(2, '@#$%^&*')

Now I am search using this but get 0 result.
SELECT * FROM testjson WHERE REGEX(name, '(?i)@#$%*')

55

seems wrong regex - at https://regex101.com/ either (?i)@#$%* or @#$%* do not matches source string @#$%^&*

1 Like
56

I want to use if else condition in my search request query.
How to use ? please give an example

Here is my JSON object.

{
  "name": {
    "en": "build-your-own-computer",
    "hi": "बिल्ड-अपना-कंप्यूटर",
    "standard": ""
  }
}

Now I want to use if condition for above JSON.

var name = "";
If(!string.IsNullOrEmpty(name.standard))
{
   name = name.standard
}
else
{
   name = name.en
}

please help me for this for how to use in my search request using JSON or SQL

57

Hello team,
Is it possible or not ?

58

In MySQL, I did this and get result as below
but how to use this query for manticore ?

CREATE TABLE [products1](
	[Id] [int] NOT NULL,
	[standard] [varchar](500) NOT NULL,
	[en] [varchar](500) NULL,
	[hi] [varchar](500) NULL);

INSERT INTO products1(Id, standard, en, hi)
VALUES (1, 'build your own computer nikon', 'build your own computer', null);

INSERT INTO products1(Id, standard, en, hi)
VALUES (2, 'Nikon D5500 DSLR - Black', '', null);

INSERT INTO products1(Id, standard, en, hi)
VALUES (3, 'Nikon D5500 DSLR - Red', '', null);

Using below query I am able to search from products using MySQL

SELECT *
FROM Products1
WHERE COALESCE(en,[standard]) like ‘%nikon%’

OR

SELECT *
FROM  products1
WHERE IF(en is null, standard, en) LIKE '%nikon%'

here is result

2 Nikon D5500 DSLR NULL NULL
3 Nikon D5500 DSLR - Black NULL NULL

I want something like that
SELECT * FROM products1 If en != ‘’ then en like ‘%nikon%’ else standard like ‘%nikon%’

59

It is done. Thanks team manticore.