[BUG] Can't index document for geo_shape field using logstash jdbc input

[tatecn]

Describe the bug
Can't index document for geo_shape field using logstash jdbc input.

Detailed error message:
[2024-12-06T19:40:33,450][WARN ][logstash.outputs.opensearch][main][f935c916c86023cf18e395f59b3cdba568f48e610b7d2c85120e72db7784cbd1] Could not index event to OpenSearch. {:status=>400, :action=>["index", {:_id=>"1814625742831620096", :_index=>"geo_shape_bug_demo", :routing=>nil}, {"jobGeoBounds"=>"{"coordinates":[[[-123.63249969482422,38.86429977416992],[-123.63249969482422,36.892974853515625],[-121.20817565917969,36.892974853515625],[-121.20817565917969,38.86429977416992],[-123.63249969482422,38.86429977416992]]],"type":"polygon"}", "jdNo"=>"1814625742831620096", "@Version"=>"1", "@timestamp"=>2024-12-07T03:40:32.999588910Z}], :response=>{"index"=>{"_index"=>"geo_shape_bug_demo", "_id"=>"1814625742831620096", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse field [jobGeoBounds] of type [geo_shape]", "caused_by"=>{"type"=>"parse_exception", "reason"=>"expected word but found: '{'"}}}}}
{
"jobGeoBounds" => "{"coordinates":[[[-123.63249969482422,38.86429977416992],[-123.63249969482422,36.892974853515625],[-121.20817565917969,36.892974853515625],[-121.20817565917969,38.86429977416992],[-123.63249969482422,38.86429977416992]]],"type":"polygon"}",
"jdNo" => "1814625742831620096",
"@Version" => "1",
"@timestamp" => 2024-12-07T03:40:32.999588910Z
}

To Reproduce

1. Download logstash with plugins and unzip it to /data/logstash-8.9.0/.
   https://artifacts.opensearch.org/logstash/logstash-oss-with-opensearch-output-plugin-8.9.0-linux-x64.tar.gz
2. Download Mysql jdbc driver and put it to /data/logstash-8.9.0/lib/mysql-connector-j-8.0.33.jar
3. Create a test table and data in Mysql.

CREATE TABLE geo_shape_bug_demo (
id bigint(20) NOT NULL AUTO_INCREMENT,
jd_no varchar(32) NOT NULL,
job_geo_bounds varchar(500) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY idx_jd_no (jd_no)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

insert into geo_shape_bug_demo (jd_no,job_geo_bounds) values ('1814625742831620096','{"coordinates":[[[-123.63249969482422,38.86429977416992],[-123.63249969482422,36.892974853515625],[-121.20817565917969,36.892974853515625],[-121.20817565917969,38.86429977416992],[-123.63249969482422,38.86429977416992]]],"type":"polygon"}');

4. Create a test index in OpenSearch.
   PUT /geo_shape_bug_demo" -d '{"mappings":{"properties":{"jdNo":{"type":"keyword"},"jobGeoBounds":{"type":"geo_shape"}}}}'

5. Create a test conf

input {
jdbc {
jdbc_driver_library => "/data/logstash-8.9.0/lib/mysql-connector-j-8.0.33.jar"
jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://"
jdbc_user => ""
jdbc_password => "***"
statement => "SELECT jd_no AS jdNo,job_geo_bounds AS jobGeoBounds FROM geo_shape_bug_demo WHERE jd_no='1814625742831620096'"
clean_run => "true"
jdbc_default_timezone => "America/Los_Angeles"
lowercase_column_names => "false"
}
}

output {
opensearch {
hosts => ""
user => ""
password => "***"
index => "geo_shape_bug_demo"
document_id => "%{jdNo}"
ssl_certificate_verification => false
ecs_compatibility => disabled
}
stdout { codec => "rubydebug"}
}

7. Start logstash in debug level then will see error in console.

Expected behavior
Both the Curl PUT request and input with the stdin codec set to json can index successfully. Works as them.

curl -X PUT --user *** -H "Content-Type: application/json" "***/geo_shape_bug_demo/_doc/1814625742831620096" -d '{"jobGeoBounds":{"coordinates":[[[-123.63249969482422,38.86429977416992],[-123.63249969482422,36.892974853515625],[-121.20817565917969,36.892974853515625],[-121.20817565917969,38.86429977416992],[-123.63249969482422,38.86429977416992]]],"type":"polygon"},"jdNo":"1814625742831620096"}'

input {
stdin {
codec => json
}
}

Plugins
jdbc input

Screenshots
no

Host/Environment (please complete the following information):
AWS OpenSearch service version is 2.11

• OS: [aws ec2 ami Linux Debian]
• Version [12]

Additional context
Geo JSON is from OpenSearch index which was indexed using JAVA SDK.
I’m not sure if this is a bug in the JDBC plugin, as the printed JSON appears correct. Additionally, the Curl PUT request works fine, suggesting it might not be an issue on the OpenSearch side. Any suggestions would be greatly appreciated.
Thank you.

[tatecn]

I fixed it using json filter like below.

filter {
json {
source => "jobGeoBounds"
target => "jobGeoBounds"
}
}

[dblock]

If something can be improved in the documentation please PR @tatecn!

[tatecn]

If something can be improved in the documentation please PR @tatecn!

ok, I will try after finish my current work. @dblock