Passing a JSON as query

[ogierpaul]

Good morning, I would like to query ElasticSearch using a query body defined in Postgres. I am not able to pass the query body as a JSON using the query parameter.

Architecture Framework

• 1 elastic search index
• 1 postgres table

Elastic Search index

I have one index containing all my data in ElasticSearch target-index With the mapping:

"mappings": {
    "properties": {
      "id": {
        "type": "integer"
      },
      "name": {
        "type": "text"
      },
      "city": {
        "type": "text"
      }
}
}

containing the data	id	name	city
1	Paul Ogier	New York, NY
2	Joe Scott	Boston, MA
3	Alice Sharp	Boston, MA

Postgres Data

One table	id	name	city	q_body
1	Paul	New York	{JSON query: see below
2	Joe	Boston	JSON Query
3	Alice	Boston	Json query

See the example of the JSON query for row 1: It's a multi-match query

{"query" :
  {"bool" : 
    {"should":
      [
        {"match": {"name": {"query": "Paul", "fuzziness": 1}}},
        {"match": {"city": {"query": "New York", "fuzziness": 1}}}
      ]
    }
   },
"explain" : true, "size" : 20}

Foreign Data Wrapper

CREATE FOREIGN TABLE es_fdw
    (
        id BIGINT,
        name varchar,
        query JSON,
        score NUMERIC
    )

Results. actual vs Expected:

Query using:

• With es_fdw as a foreign data wrapper to the target-index

WITH mypgquery AS (SELECT pg_id, q_body FROM mypgtable )
SELECT a.pg_id, id as es_id, name, score
    FROM es_fdw, a
    WHERE query = mypgquery.q_body LIMIT 10;

Expected result

pg_id	es_id	name	score
1	1	Paul Ogier	10
2	2	Json Scott	10
2	3	Alice Sharp	3
3	3	Alice Sharp	10
3	2	Json Scott	3

Actual result

ERROR: operator does not exist: json = json

I cannot pass a query parameter as json

[matthewfranglen]

Thanks for raising this issue. I'm thinking that if the query column has a json type then it will only accept json queries, otherwise if it has a text type it will accept lucene uri queries. Does that sound like a reasonable way to add this functionality?

[ogierpaul]

It seems reasonnable. This is what I have tried to do. I updated my question to clarify what are the expected results.

[matthewfranglen]

So the column cannot be json because there is no operator for performing json comparisons:

ERROR:  operator does not exist: json = json
LINE 1: select '1'::json = '1'::json;
                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

The query column works by populating the column with the value of the query so that when the rows are checked by postgres after being returned they are retained. I have an idea for how to fix this, but it will involve passing the query as a string.

[matthewfranglen]

Created https://github.com/matthewfranglen/postgres-elasticsearch-fdw/pull/20 to address this issue. Could you review it and see if it works for you?

[matthewfranglen]

Confirmed fixed by @ogierpaul in PR