# Jdbc\_static local\_lookups sql query with in operator

**URL:** <https://discuss.elastic.co/t/jdbc-static-local-lookups-sql-query-with-in-operator/169472>\
**Category:** Logstash\
**Created:** [February 21, 2019, 5:55pm UTC](https://discuss.elastic.co/t/jdbc-static-local-lookups-sql-query-with-in-operator/169472 "2019-02-21T17:55:45Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![pablocoacci](https://avatars.discourse-cdn.com/v4/letter/p/f04885/32.png) [@pablocoacci](https://discuss.elastic.co/u/pablocoacci)\
**Post date:** [February 21, 2019, 5:55pm UTC](https://discuss.elastic.co/t/jdbc-static-local-lookups-sql-query-with-in-operator/169472/1 "2019-02-21T17:55:45Z")

</div>

Hi. How are you?  
We are trying to execute the filter "Jdbc\_static" to enrich the events with static information present in a database.  
The field that we want to use is an arrays strings and we need to perform a query with "IN" operator so that it brings all the names of the list.  
The problem is that logstash throws a warning indicating that this field can not be found in the local\_lookups, therefore, this field is not created.

This is an example of the input event. The field with the problem is "RequestedHotelIds".  
{  
"HasError": true,  
"User": "127.0.0.1",  
"EventDate": "2019-02-02T02:27:08-03:00",  
"RequestedHotelIds": [  
"90000250",  
"90000352",  
"90004521"  
],  
"ExecutionTime": 209,  
"RequestedCitiesIds": null,  
"ErrorDescription": "asdasd"  
}

This is the Logstash script configuration:  
input {  
kafka {  
bootstrap\_servers =\> "10.75.85.204:9092"  
topics =\> "test2101"  
codec =\> "json"  
}  
}

filter {  
mutate {  
split =\> { "RequestedHotelIds" =\> ","}  
}

jdbc\_static {  
loaders =\> [  
{  
id=\> "remote-hotels"   
query =\> "SELECT hc.Code as Code, c.Nombre as Nombre from Clientes c inner join HotelCodes hc on hc.id\_cliente = c.id where c.tipocliente = 'H'"  
local\_table =\> "hotels"  
}  
]  
local\_db\_objects =\> [  
{  
name =\> "hotels"  
index\_columns =\> ["Code"]  
columns =\> [  
["Code", "varchar(10)"],  
["Nombre", "varchar(100)"]  
]  
}  
]  
local\_lookups =\> [  
{  
query =\> "select Nombre from hotels where Code in :idparamhotels"  
parameters =\> {idparamhotels =\> "[RequestedHotelIds]"}  
target =\> "hotelsnames"  
}  
]

```
staging_directory => "/tmp/logstash/jdbc_static/import_data"
loader_schedule => "*/30 * * * *"
jdbc_user => "testDB"
jdbc_password => "pws1234"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_driver_library => "/usr/share/logstash/pathDB/sqljdbc_6.0/enu/jre8/sqljdbc42.jar"
jdbc_connection_string => "jdbc:sqlserver://55.55.55.55;database=testDB;user=usertest;password=pws1234"

```

}  
}

output {  
elasticsearch {  
hosts =\> ["15.41.965.204:9200"]  
codec =\> "json"  
index =\> "logstash-withnames"  
}  
}

the warning it throws is  
[WARN] 2019-02-21 12:39:10.031 [[main]\>worker0] lookup - Parameter field not found in event {:lookup\_id=\>"lookup-1", :invalid\_parameters=\>["[RequestedHotelIds]"]}

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 21, 2019, 5:55pm UTC](https://discuss.elastic.co/t/jdbc-static-local-lookups-sql-query-with-in-operator/169472/2 "2019-03-21T17:55:51Z")

</div>

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