How to map MySQL query to elasticsearch in node.js?


(Tushar Mudgal) #1

I am facing an issue with querying. Precisely, I want to extract the results that match my query (astro-ph). How can I query this? It exists in <primary_category> col name in SQL. Column may contain (astro-ph, astro-ph.GC).

I am using elasticsearch.js as the npm library.

SELECT * FROM paper_metadata WHERE primary_category LIKE 'astro-ph%' ORDER BY DESC;

(Kanagat) #2

hi, @Tushar_Mudgal, i didn't use npm library of elasticsearch, but

first you should understand difference of field types: keyword and text .
If your field, for example, "prmary category" is text field type(by default), so you can use term query with sorting .
keyword and text field types appeared in version 5.5


(Tushar Mudgal) #3

Hi @nugusbayevkk,
Here is the example values in my data.
How can I convert this to Elastic Search query. I want to extract rows with primary_category -> astro-ph

primary_category -> astro-ph
primary_category -> astro-ph.EP
primary_category -> astro-ph.CO

(Kanagat) #4

@Tushar_Mudgal , i try to explain how does it work:

  1. when you insert data to elasticsearch
    for example word : "astro-ph"
    this word by default divide into 2 term: "astro" and "ph"
    to your index applied "standard-analyzer"

so you can use:

  1. term query to find documents with terms

curl -H "Content-type: application/json" localhost:9200/_search -d '{
"query" : { "term" : { "primary_category" : "astro" } } }'

and as result you get all documents that contains term "astro":

{"took":19,"timed_out":false,"_shards":{"total":30,"successful":30,"skipped":0,"failed":0},"hits":{"total":3,"max_score":0.2876821,"hits":[{"_index":"test","_type":"doc","_id":"ntlDt2YBOyk1S2R0H33v","_score":0.2876821,"_source":{
 "primary_category" : "hello astro-ph. and bye"
}},{"_index":"test","_type":"doc","_id":"m9lCt2YBOyk1S2R0_31C","_score":0.2876821,"_source":{
 "primary_category" : "astro-ph"
}},{"_index":"test","_type":"doc","_id":"ndlDt2YBOyk1S2R0H33S","_score":0.2876821,"_source":{
 "primary_category" : "astro-ph.EP"
  1. or use match query

curl -H "Content-type: application/json" localhost:9200/_search -d '{
"query" : {
"match" : {
"primary_category" : "astro-ph"
}
}
}'

and result:

{"took":5,"timed_out":false,"_shards":{"total":30,"successful":30,"skipped":0,"failed":0},"hits":{"total":3,"max_score":0.5753642,"hits":[{"_index":"test","_type":"doc","_id":"ntlDt2YBOyk1S2R0H33v","_score":0.5753642,"_source":{
"primary_category" : "hello astro-ph. and bye"
}},{"_index":"test","_type":"doc","_id":"m9lCt2YBOyk1S2R0_31C","_score":0.5753642,"_source":{
"primary_category" : "astro-ph"
}},{"_index":"test","_type":"doc","_id":"ndlDt2YBOyk1S2R0H33S","_score":0.2876821,"_source":{
"primary_category" : "astro-ph.EP"


(system) #5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.