ERROR: COUNT for /index1 failed: Expecting value: line 1 column 1 (char 0)

[matthewfranglen]

Hello,

I was trying to perform the query from Postgres to elasticsearch using "query" as shown above. But it was throwing me an error saying...

postgres=# select * from articles_es where query = '_id:1';
ERROR:  COUNT for /index1 failed: Expecting value: line 1 column 1 (char 0)

Here are the details of how I created the foreign table (providing info on a high level):

CREATE FOREIGN TABLE articles_es
    (
        id TEXT,
        query TEXT,
        ....
    )
SERVER multicorn_es
OPTIONS
    (
        host 'elasticsearch',
        port '9200',
        index 'article-index',
        type 'article',
        rowid_column 'id',
        query_column 'query',
        query_dsl 'false',
        refresh 'false',
        complete_returning 'false',
        timeout '20',
        username 'elastic',
        password 'elastic'
    )
;

Any suggestions, please.

[matthewfranglen]

@chakradhar1199

[matthewfranglen]

The response that is being received by the client is not json. You can see the actual response if you run the underlying client and try to make the same request.

from elasticsearch import Elasticsearch
from elasticsearch import VERSION as ELASTICSEARCH_VERSION 

# adjust these settings for your setup
client = Elasticsearch([{"host": "elasticsearch", "port": 9200}, http_auth=("elastic", "elastic"), timeout=20)

# I think you're using elasticsearch 7 and that the actual index name is index1 based on the error message
response = client.count(index="index1", q="_id:1")
print(response)

[matthewfranglen]

It would help a lot to have the raw response or error message that this produces. You should be able to run the code above on the server that has postgres running.

[ghost]

Hi Matthew,

By making use of the code which you shared, it didn't show up any error for me. Here is the output I got:

{'count': 1, '_shards': {'total': 1, 'successful': 1, 'skipped': 0, 'failed': 0}}

[matthewfranglen]

That's very odd then. I would suggest carefully checking your FDW table configuration. The python code above is equivalent to what is being performed by the FDW. You can see the invocation of count here and the arguments referenced on that line come from here.

[ghost]

Thanks, Matthew, Issue resolved.

As you mentioned, it's because of table configuration only. When I have set the query_dsl 'true' and tried with the below query, it showed up error.

select * from articles_es where query = '_id:1';

But for same settings, below query worked:

select * from articles_es where query = '{"query": {"match": {"_id": 1 }}}';

Actually, it's already explained in the "README.md" regarding this, but I didn't observe that. Sorry to pull you on to this issue.

[matthewfranglen]

No problem. Glad you have resolved your issue.