Querying nested objects

[OmarDacca]

hello,

I have an index called session_details, here is an example of a document:

{
    "clientIdAsString" : "100",
    "sessionStart" : "2021-12-14T17:14:49.000Z",
    "user_id" : "0Bl301Me7x080r5o2K8m9D86Z",
    "wz_session" : "r8820h69A2583A0uj01So1934",
    "domainsGroup" : "wave-electronics.com",
    "converted" : false,
    "sessionEnd" : "2021-12-14T17:16:06.000Z",
    "session_details" : [
        {
            "date_time" : "2021-12-14T17:15:11.000Z",
            "type" : "goal",
            "ok" : true,
            "event_key" : "customer_login"
        },
        {
            "error_message" : "error message xyz",
            "date_time" : "2021-12-14T17:15:01.000Z",
            "type" : "goal",
            "ok" : false,
            "event_key" : "customer_login"
        },
        {
            "date_time" : "2021-12-14T17:15:11.000Z",
            "ok" : true,
            "event_key" : "add_to_cart"
        }
    ]
}

here is the mappings:

{
    "properties" : {
        "clientIdAsString" : {
          "type" : "keyword"
        },
        "converted" : {
          "type" : "boolean"
        },
        "domainsGroup" : {
          "type" : "keyword"
        },
        "sessionEnd" : {
          "type" : "date"
        },
        "sessionStart" : {
          "type" : "date"
        },
        "session_details" : {
          "type" : "nested",
          "properties" : {
            "date_time" : {
              "type" : "date"
            },
            "error_message" : {
              "type" : "keyword"
            },
            "event_key" : {
              "type" : "keyword"
            },
            "ok" : {
              "type" : "boolean"
            }
          }
        },
        "user_id" : {
          "type" : "keyword"
        },
        "wz_session" : {
          "type" : "keyword"
        }
    }
}

the query that I'm trying to do is like this:
all the wz_session(s) - cardinality or value_count - it doesn't matters, where:

1. session_details.event_key = 'customer_login' and,
2. session_details.ok = true (only)

in other words, all the logins that succeeded and never failed.
in the above example, the result should be empty, since we have a failed and successful login.

is that possible ? if no is it possible with a different mappings ?

thanks

[stevenD (JHON SMITH)]

The nested query searches nested field objects as if they were indexed as separate documents . If an object matches the search, the nested query returns the root parent document. To use the nested query, your index must include a nested field mapping...

[OmarDacca]

that is clear.
the question is, is there a way to return the root parent document if more than nested document met the conditions ?

I'm almost very sure the answer is negative but want to make sure and know if there any other option

[Hendrik_Muhs]

It seems to me that the usage of nested is only useful to query for every document that have at least one customer_login event key. How many session have that key? With other words how much data can you reduce by that query?

To answer whether it was a clean login - so no errors - you need the full document. For this you
need some sort of scripting, e.g. a scripted terms agg. I can also think of a runtime field.

@OmarDacca

is that possible ? if no is it possible with a different mappings ?

It is possible, however if you can make adjustments on the ingest side a better solution is possible. The above solution requires a full scan on all documents which have customer_login for every query execution (except for some inner shard level cache hits). If you don't have a lot of login sessions that might be still ok.

If you are looking for a more performant approach I suggest an ingest pipeline that checks your session document on ingest and e.g. sets a field on the outer(not nested) document level. You could e.g. count the number of errors and map it to an int. With that field you only need to query for all documents where errors equals 0.