Problem with SQL Statements Containing Strings in Curl Commands

dlonghi

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:

dlonghi

```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`

dlonghi

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 } } '```

dlonghi

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.

Prabhat

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

Prabhat

dlonghi

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"}}'```

dlonghi

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

dlonghi

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```

Prabhat

yeah that is how its implemented.