Unable to use field named "_Data" in Sql query anymore

Elastic Stack Elasticsearch
1

Hi,

I have a document looking like this, copied from the _source property of a Elastic query result:

    {
          "AbstractId" : "7f424fa0-f30e-477d-8245-d3b4e97e0538",
          "Category" : "GUI",
          "Created" : "2018-10-30T14:49:59.936",
          "MessageId" : "33ced315-7af6-40b9-9f23-d372756c74db",
          "StrongTypeName" : "Milestone",
          "_Data" : {
            "PurposeCode" : null,
            "Type" : "ABC",
            "Created" : null,
            "ShipmentId" : "abcd1234"
          }

Using the the same document in the same index before the upgrade to 6.5.1, I could refer to the _Data. without any problem using Sql, for example:
select _Data.ShipmentId, count(_Data.Type) as cnt from index_shipments where _Data.Type.keyword = 'ICP' group by _Data.ShipmentId, _Data.Type.keyword

Now, it seems that the propery "_Data" is not available when using Sql:
"Unknown column [_Data.Type.keyword], did you mean any of [Category.keyword, StrongTypeName.keyword, AbstractId.keyword, MessageId.keyword]"

If I use "select * from ...." for that document, I get this back, the "_Data" property is not returned in the result at all (which probably explains the error message):

        {
	"columns": [
		{
			"name": "AbstractId",
			"type": "text"
		},
		{
			"name": "Category",
			"type": "text"
		},
		{
			"name": "Created",
			"type": "date"
		},
		{
			"name": "MessageId",
			"type": "text"
		},
		{
			"name": "StrongTypeName",
			"type": "text"
		}
	],
	"rows": [
		[
			"7f424fa0-f30e-477d-8245-d3b4e97e0538",
			"GUI",
			"2018-10-30T14:49:59.936Z",
			"33ced315-7af6-40b9-9f23-d372756c74db",
			"Milestone"
		]
	]
}

Could this be an issue introduced in the latest release or is it me doing something wrong here?

Regards
/Magnus

2

Hi @Magnus_Andersson1,
What is the mapping of _Data field? Please, provide the full mapping definition.

3

Here is the mapping:

 {
  "mapping": {
    "_doc": {
      "properties": {
        "AbstractId": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "Category": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "Created": {
          "type": "date"
        },
        "MessageId": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "StrongTypeName": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "_Data": {
          "properties": {
            "ShipmentId": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "Type": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        }
      }
    }
  }
}
4

Hey @Magnus_Andersson1,
Indeed, a bug. Created issue here and we'll look into it. Thanks for reporting.

5

Great, thanks!
/Magnus

6

@Magnus_Andersson1 the bug is fixed. Will be available in 6.6.0.

7

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.