manticoresearch
manticoresearch copied to clipboard
Published
20 hours ago •
manticoresoftware
manticoresoftware
Scripting Fields And Facet over HTTP/JSON
Simple search query with scripting fields
{
"index": "some_index",
"script_fields": {
"param_7": {
"script": {
"inline": "IN ( params_json['7'], 67 )"
}
}
},
"sort": [
{
"dtp": "desc"
}
],
"limit": 50,
"offset": 0,
"options": {
"max_matches": 50000
},
"query": {
"bool": {
"must": [
{
"match_phrase": {
"title,text": ""
}
},
{
"equals": {
"param_7": 1
}
},
{
"equals": {
"st": 1
}
},
{
"in": {
"cats": [
279
]
}
}
]
}
}
}
Result is OK
Add facets aggregation
{
"index": "some_index",
"script_fields": {
"param_7": {
"script": {
"inline": "IN ( params_json['7'], 67 )"
}
}
},
"sort": [
{
"dtp": "desc"
}
],
"limit": 50,
"aggs": {
"district_id": {
"terms": {
"field": "district_id"
}
},
"cats": {
"terms": {
"field": "cat"
}
}
},
"options": {
"max_matches": 50000
},
"query": {
"bool": {
"must": [
{
"match_phrase": {
"title,text": ""
}
},
{
"equals": {
"param_7": 1
}
},
{
"equals": {
"st": 1
}
},
{
"in": {
"cats": [
279
]
}
}
]
}
}
}
Answer is HTTP/1.1 500 Internal Server Error
{"error":"index board_ad_index: unknown column: param_7"}
Index config
source some_index_config : main_config
{
sql_attr_uint = city_id
sql_attr_uint = district_id
sql_attr_uint = cat
sql_attr_uint = dtp
sql_attr_uint = st
sql_attr_multi = uint params from field
sql_attr_multi = uint districts from field
sql_attr_multi = uint cats from field
sql_attr_json = params_json
}
Example data in index
+---------+---------+-------------+------+------------+------+----------------+-----------+---------+---------------------------------------------+
| id | city_id | district_id | cat | dtp | st | params | districts | cats | params_json |
+---------+---------+-------------+------+------------+------+----------------+-----------+---------+---------------------------------------------+
| 1398544 | 2732 | 0 | 279 | 1662113007 | 1 | 68,70,74,79,92 | 18 | 278,279 | {"8":68,"9":[70],"10":[74],"11":79,"13":92} |
| 1398588 | 2732 | 0 | 279 | 1660829424 | 1 | 69,84,89,90 | 1,8 | 278,279 | {"8":69,"11":84,"12":89,"13":90} |
+---------+---------+-------------+------+------------+------+----------------+-----------+---------+---------------------------------------------+
searchd -v
Manticore 5.0.0 b4cb7da02@220518 release (columnar 1.15.4 2fef34e@220522) (secondary 1.15.4 2fef34e@220522)
I can't reproduce it like this:
snikolaev@dev:~$ mysql -P9306 -h0 -v -e "drop table if exists some_index; create table some_index(title text indexed, \`text\` text indexed, city_id int, district_id int, cat int, dtp int, st int, params multi, districts multi, cats multi, params_json json); desc some_index; insert into some_index(id,title,text,city_id,district_id,cat,dtp,st,params,districts,cats,params_json) values(1398544,'','',2732,0,279,1662113007,1,(68,70,74,79,92),(18),(278,279),'{\"8\":68,\"9\":[70],\"10\":[74],\"11\":79,\"13\":92}'),(1398588,'','',2732,0,279,1660829424,1,(69,84,89,90),(1,8),(278,279),'{\"8\":69,\"11\":84,\"12\":89,\"13\":90}'); select * from some_index;"
--------------
drop table if exists some_index
--------------
--------------
create table some_index(title text indexed, `text` text indexed, city_id int, district_id int, cat int, dtp int, st int, params multi, districts multi, cats multi, params_json json)
--------------
--------------
desc some_index
--------------
+-------------+--------+------------+
| Field | Type | Properties |
+-------------+--------+------------+
| id | bigint | |
| title | text | indexed |
| text | text | indexed |
| city_id | uint | |
| district_id | uint | |
| cat | uint | |
| dtp | uint | |
| st | uint | |
| params_json | json | |
| params | mva | |
| districts | mva | |
| cats | mva | |
+-------------+--------+------------+
--------------
insert into some_index(id,title,text,city_id,district_id,cat,dtp,st,params,districts,cats,params_json) values(1398544,'','',2732,0,279,1662113007,1,(68,70,74,79,92),(18),(278,279),'{"8":68,"9":[70],"10":[74],"11":79,"13":92}'),(1398588,'','',2732,0,279,1660829424,1,(69,84,89,90),(1,8),(278,279),'{"8":69,"11":84,"12":89,"13":90}')
--------------
--------------
select * from some_index
--------------
+---------+---------+-------------+------+------------+------+---------------------------------------------+----------------+-----------+---------+
| id | city_id | district_id | cat | dtp | st | params_json | params | districts | cats |
+---------+---------+-------------+------+------------+------+---------------------------------------------+----------------+-----------+---------+
| 1398544 | 2732 | 0 | 279 | 1662113007 | 1 | {"8":68,"9":[70],"10":[74],"11":79,"13":92} | 68,70,74,79,92 | 18 | 278,279 |
| 1398588 | 2732 | 0 | 279 | 1660829424 | 1 | {"8":69,"11":84,"12":89,"13":90} | 69,84,89,90 | 1,8 | 278,279 |
+---------+---------+-------------+------+------------+------+---------------------------------------------+----------------+-----------+---------+
snikolaev@dev:~$ curl -sX POST http://localhost:9308/search -d '{
> "index": "some_index",
> "script_fields": {
> "param_7": {
> "script": {
> "inline": "IN ( params_json['\''8'\''], 69 )"
> }
> }
> },
> "sort": [
> {
> "dtp": "desc"
> }
> ],
> "limit": 50,
> "aggs": {
> "district_id": {
> "terms": {
> "field": "district_id"
> }
> },
> "cats": {
> "terms": {
> "field": "cat"
> }
> }
> },
> "options": {
> "max_matches": 50000
> },
> "query": {
> "bool": {
> "must": [
> {
> "match_phrase": {
> "title,text": ""
> }
> },
> {
> "equals": {
> "param_7": 1
> }
> },
> {
> "equals": {
> "st": 1
> }
> },
> {
> "in": {
> "cats": [
> 279
> ]
> }
> }
> ]
> }
> }
> }'|jq .
{
"took": 86,
"timed_out": false,
"hits": {
"total": 1,
"total_relation": "eq",
"hits": [
{
"_id": "1398588",
"_score": 1,
"_source": {
"city_id": 2732,
"district_id": 0,
"cat": 279,
"dtp": 1660829424,
"st": 1,
"params_json": {
"8": 69,
"11": 84,
"12": 89,
"13": 90
},
"params": [
69,
84,
89,
90
],
"districts": [
1,
8
],
"cats": [
278,
279
],
"param_7": 1
}
}
]
},
"aggregations": {
"district_id": {
"buckets": [
{
"key": 0,
"doc_count": 1
}
]
},
"cats": {
"buckets": [
{
"key": 279,
"doc_count": 1
}
]
}
}
}
Please modify this example so it starts reproducing.
We can't reproduce in real time mode, cause this is plain index Structure
+----------------+--------+----------------+
| Field | Type | Properties |
+----------------+--------+----------------+
| id | bigint | |
| title | text | indexed stored |
| text | text | indexed stored |
| city_id | uint | |
| district_id | uint | |
| districts | mva | |
| cat | uint | |
| cats | mva | |
| dtp | uint | |
| st | uint | |
| params | mva | |
| params_json | json | |
+----------------+--------+----------------+
Real data with cat 279
+---------+---------+-------------+-----------+------+---------+------------+------+----------------+---------------------------------------------+
| id | city_id | district_id | districts | cat | cats | dtp | st | params | params_json |
+---------+---------+-------------+-----------+------+---------+------------+------+----------------+---------------------------------------------+
| 1398544 | 2732 | 0 | 18 | 279 | 278,279 | 1662113007 | 1 | 68,70,74,79,92 | {"8":68,"9":[70],"10":[74],"11":79,"13":92} |
| 1398588 | 2732 | 0 | 1,8 | 279 | 278,279 | 1660829424 | 1 | 69,84,89,90 | {"8":69,"11":84,"12":89,"13":90} |
| 1398663 | 2732 | 0 | 3 | 279 | 278,279 | 1662965920 | 1 | 67,68,76,78 | {"7":67,"8":68,"10":[76],"11":78} |
| 1398726 | 2732 | 0 | 18 | 279 | 278,279 | 1662437362 | 1 | | [] |
| 1389634 | 2732 | 0 | 0 | 279 | 278,279 | 1662909120 | 1 | 69,74,79,89 | {"8":69,"10":[74],"11":79,"12":89} |
| 1392970 | 2732 | 0 | 0 | 279 | 278,279 | 1663006657 | 1 | 67,68,78,88,90 | {"7":67,"8":68,"11":78,"12":88,"13":90} |
| 1394728 | 2732 | 0 | 28 | 279 | 278,279 | 1662986237 | 1 | 68,86,91 | {"8":68,"11":86,"13":91} |
| 1396482 | 2732 | 0 | 39 | 279 | 278,279 | 1661464751 | 1 | 70,72,74,89,92 | {"9":[70,72],"10":[74],"12":89,"13":92} |
+---------+---------+-------------+-----------+------+---------+------------+------+----------------+---------------------------------------------+
Hm.... I find problem, but request make from php client without any escaping unescaped not work
{"script":{"inline":"IN ( params_json['7'], 67 )"}}}
escaped is work
{"script":{"inline":"IN ( params_json['\''7'\''], 67 )"}}}
Question How escape expression in/for official php client, cause if i set in client escaped
$search->expression('param_7',"IN ( params_json['\''7'\''], 67 )")
official php client also escape backslashes
"script_fields":{"param_7":{"script":{"inline":"IN ( params_json['\\''7'\\''], 67 )"}}}
➤ Nick Sergeev commented:
We're still unable to reproduce the issue. In our tests the following example works fine without escaping.
$search->expression('param_7',"IN ( params_json['7'], 67 )")
Can you please specify which version of the PHP client you use?
We figured out escaping in the telegram channel, everything works without escaping. We should close the issue. Another problem. From my point of view, this is strange and not correct. After the get() execution, in which the compile() of the request occurs, all expressions are transferred from the parameters to the request body, which implicitly changes the state of the Search object and does not allow its reuse. But we bypassed this "feature"