Problem with SQL Statements Containing Strings in Curl Commands

TLDR dlonghi had trouble querying the field type 'string' with curl, specifically when running _search API. Prabhat suggested creating the query in the browser and getting the curl command from the dev console, which worked for dlonghi.

Photo of dlonghi
dlonghi
Tue, 11 Jul 2023 19:47:07 UTC

Not able to send curl to _search API with an SQL statement containing a string. Number type works fine. Hello everyone, this may be more of a linux/bash/curl question than Openobserve's. I indexed 3 docs with curl as in the next message:

Photo of dlonghi
dlonghi
Tue, 11 Jul 2023 19:48:00 UTC

```curl -i -u ':Complexpass#123' -d ' [ { "_timestamp": "2023-07-11T16:20:01-03:00", "building": "B1", "some_text": "Blue building", "category": "B", "some_id": 111 } ] '``` ```curl -i -u ':Complexpass#123' -d ' [ { "_timestamp": "2023-07-11T16:20:02-03:00", "building": "R1", "some_text": "Red building", "category": "R", "some_id": 222 } ] '``` ```curl -i -u ':Complexpass#123' -d ' [ { "_timestamp": "2023-07-11T16:20:03-03:00", "building": "G1", "some_text": "Green building", "category": "G", "some_id": 333 } ] '``` Note: `2023-07-11T16:20:01-03:00 = 1689103201000000` `2023-07-11T16:20:02-03:00 = 1689103202000000` `2023-07-11T16:20:03-03:00 = 1689103203000000`

Photo of dlonghi
dlonghi
Tue, 11 Jul 2023 19:48:46 UTC

I am able to locate each one of then in the _search api with the commands below specifying numeric fields _timestamp and some_id: ```curl -sk -u ':Complexpass#123' -X POST -d ' { "query": { "sql": "SELECT * FROM \"somestream\" WHERE _timestamp=1689103201000000 AND some_id=111 ", "from": 0, "size": 10 } } '``` ```curl -sk -u ':Complexpass#123' -X POST -d ' { "query": { "sql": "SELECT * FROM \"somestream\" WHERE _timestamp=1689103202000000 AND some_id=222 ", "from": 0, "size": 10 } } '``` ```curl -sk -u ':Complexpass#123' -X POST -d ' { "query": { "sql": "SELECT * FROM \"somestream\" WHERE _timestamp=1689103203000000 AND some_id=333 ", "from": 0, "size": 10 } } '```

Photo of dlonghi
dlonghi
Tue, 11 Jul 2023 19:50:03 UTC

The issue is this: `AND building='B1'` ```curl -sk -u ':Complexpass#123' -X POST -d ' { "query": { "sql": "SELECT * FROM \"somestream\" WHERE _timestamp=1689103201000000 AND some_id=111 AND building='B1' ", "from": 0, "size": 10 } } '``` Can't figure out how to query the field building (type string) with curl. Tried several escape stuff.. Appreciate any light on this : ) The goal here is to check if the doc is already there before I try to ingest. Use case demands sync betweem sql database to search database.

Photo of Prabhat
Prabhat
Tue, 11 Jul 2023 23:10:54 UTC

A simple technique that you can follow to get the right curl command from the browser is to run the query in the browser and get the request as curl from the dev console

Photo of Prabhat
Prabhat
Tue, 11 Jul 2023 23:11:32 UTC

Photo of dlonghi
dlonghi
Wed, 12 Jul 2023 12:43:23 UTC

Thanks a lot for sharing this trick. Worked like a charm. ```curl -iku ':Complexpass#123' -X POST -d $'{"query":{"sql":"SELECT * FROM \\"somestream\\" WHERE _timestamp=1689103202000000 AND some_id=222 AND building=\'R1\' AND category=\'R\'","start_time":1689103202000000,"end_time":1689103202000001,"from":0,"size":150,"sql_mode":"full"}}'```

Photo of dlonghi
dlonghi
Wed, 12 Jul 2023 12:45:44 UTC

The document. ```{ _timestamp : 1689103202000000, building : R1, category : R, some_id : 222, some_text : Red building, }```

Photo of dlonghi
dlonghi
Wed, 12 Jul 2023 12:47:34 UTC

One thing that I noticed, if I match start_time and end_time to the same value of the document's _timestamp, I get 0 hit. Incrementing the end_time by 1, I get the 1 hit I'm looking for. 0 hits: ```"start_time":1689103202000000, "end_time":1689103202000000``` the 1 hit: ```"start_time":1689103202000000, "end_time":1689103202000001```

Photo of Prabhat
Prabhat
Wed, 12 Jul 2023 16:15:53 UTC

yeah that is how its implemented.