Incorrectly convert nested query about sql4es HOT 6 CLOSED

Thanks for your thorough explanation of the problem! I understand the issue and my guess right now is that it is a bug in the driver which I will have to verify. The last example in your post is exactly what it should be and ill look into it (might be a bit tricky to get it done though so will take some time)

from sql4es.

Thanks for your reply. Here is another thought that could be with this problem.
What if someone try to find rows that have poid is A (from one of events) and oid is B (from another of events) just like current seql4es's query.

It should not be select name from my_case where (events.poid='A' **or** events.oid='B');, because this query mean to find all rows that have poid is A OR another row that has oid is B.

Briefly, there is three options that might implement in query.

1. Find rows that have at least a event that both poid is A and oid is B. (this is I want).
2. Find rows that have events which both poid is A and oid is B in all events. (just like current query).
3. Find rows that have event which either poid is A or oid is B.

Here is my thought but I still don't know how theses diffent purpose query should classify in query.

from sql4es.

I do not quite follow your reasoning here but i guess that is because I do not know what your data looks like. I'm guessing you have multiple nested events and you want to find documents in which one of these events has poid='A' and another event within the document has oid='B'...
Maybe you can use a query like:

 ... where (events.poid='A' AND NOT events.oid='B') AND (events.oid='B' AND NOT events.poid='A')

An alternative could be to use a multi-field in which you index events both as a nested type and as objects. Not sure if that really works but at least provides different ways to query those events.

I made a fix for your original issue which I believe solves it. You can download the latest release (0.8.2.2) and try it. Multiple queries on nested types are now put within a single nested query as shown below:

{
  "post_filter" : {
    "nested" : {
      "query" : {
        "bool" : {
          "must" : [ {
            "range" : {
              "nestedDoc.intNum" : {
                "from" : 5,
                "to" : null,
                "include_lower" : false,
                "include_upper" : true
              }
            }
          }, {
            "match" : {
              "nestedDoc.text" : {
                "query" : "NestedDoc number 5",
                "type" : "phrase"
              }
            }
          } ]
        }
      },
      "path" : "nestedDoc"
    }
  },
  "sort" : [ {
    "_doc" : {
      "order" : "asc"
    }
  } ]
}

Which is the explanation of

SELECT * FROM myType WHERE nestedDoc.intNum > 5 AND nestedDoc.text = 'NestedDoc number 5'

There are probably some queries in which both top level fields as well as nested objects are queried in such a way the driver doesn't translate it correctly...

from sql4es.

Thanks! Original problem is solved.
The new question was how sql4es express these two case.
Here is data structure I made.

{
    myType : "A",
    NestedDoc : [
        { text: "Intro", intNum: 1},
        { text: "Chapter 1", intNum: 10},
        { text: "Chapter 2", intNum: 20},
    ]
}

{
    myType : "B",
    NestedDoc : [
        { text: "New Intro", intNum: 1},
        { text: "New Chapter 1", intNum: 11},
        { text: "New Chapter 2", intNum: 21},
    ]
}

Case 1.
Find matching two condition in a nested doc. (Currently fixed query).

SELECT * FROM myType WHERE nestedDoc.intNum = 1 AND nestedDoc.text = 'Intro' // will return A doc
SELECT * FROM myType WHERE nestedDoc.intNum = 1 AND nestedDoc.text = 'Chapter 1' // will return null

Case 2.
Find matching one of condition in all nestedDoc (

SELECT * FROM myType WHERE nestedDoc.intNum = 1 OR nestedDoc.text = 'Intro' // will return A, B doc
SELECT * FROM myType WHERE nestedDoc.intNum = 11 OR nestedDoc.text = 'NON' // will return B doc

Case 3.
Find matching two condition in all nestedDoc.

SELECT * FROM myType WHERE nestedDoc.intNum = 1 (OR?) nestedDoc.text = 'Chapter 2' // will return A doc
SELECT * FROM myType WHERE nestedDoc.intNum = 11 (OR?) nestedDoc.text = 'Intro' // will return null

Case 1, 2 works with current version. Case 3 does not work even with es json query?
I'm new to ES so might be wrong. but just want to make sure of this.

from sql4es.

I do not think your third case is possible using this mapping. I think you can use an object mapping (which is the default) for this type of query. Using that mapping ES will flatten all documents within an array which means that it looses which information was in what nested document. Also see this page. You can index the same nested objects multiple times with different mappings to handle the different use cases you have.

from sql4es.

Everything are solved with kind explanation :)

from sql4es.