SQL Return Malformed (ignored_field_values)

Elastic Stack Elasticsearch
1

Hopefully I am not missing anything stupid. But I am working on trying to return some data using the Elasticsearch SQL functions. So far no luck.

The data I am trying to get can be found in the raw data as well as when I use the _slq/Translate function and then run the JSON I can get the value. But the value is in the ignored_field_values part of the response.

When I run the query itself with the POST _sql, the value is returned as null.

Initial Post _SQL Code:

POST _sql
{"query":"SELECT parametrics.m03_04_05_DATEOFMANUFACTURE FROM libre_mds_applicator_alias WHERE headers.serialNumber ='E007A40008775073'  "}

Response :

{
  "columns" : [
    {
      "name" : "parametrics.m03_04_05_DATEOFMANUFACTURE",
      "type" : "datetime"
    }
  ],
  "rows" : [
    [
      null
    ]
  ]
}

_SQL/Translate code:

{
  "size" : 1000,
  "query" : {
    "term" : {
      "headers.serialNumber" : {
        "value" : "E007A40008775073",
        "boost" : 1.0
      }
    }
  },
  "_source" : false,
  "fields" : [
    {
      "field" : "parametrics.m03_04_05_DATEOFMANUFACTURE",
      "format" : "strict_date_optional_time_nanos"
    }
  ],
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ]
}

The response from that:

{
  "took" : 924,
  "timed_out" : false,
  "_shards" : {
    "total" : 34,
    "successful" : 34,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [
      {
        "_index" : "libre_mds_applicator-000007",
        "_type" : "_doc",
        "_id" : "E007A40008775073_8D6ACE2D6C8A59A402E1B78A8EAA1C",
        "_score" : null,
        "_ignored" : [
          "parametrics.m03_04_05_DATEOFMANUFACTURE",
          "parametrics.m03_45_46_OFFLOADINGTIMESTAMPUTC"
        ],
        "ignored_field_values" : {
          "parametrics.m03_04_05_DATEOFMANUFACTURE" : [
            "1/3/2023 12:48:04 PM"
          ]
        },
        "sort" : [
          5806571
        ]
      }
    ]
  }
}

As you can see, in the response JSON from the translated SQL, I can get the value, but it is in the ignored_field_values. Is there anything available in the SQL that would allow it to get that value?

2

Hi @T_Eckmann

Could you provider some docs to tests? The parametrics field is type object?

3

I don't have any Docs available right now.

The specific field in question is a type Date. And has the ignore_malformed setting set to true. So it can accept values that are not properly formatted as a Date.

            "m03_04_05_DATEOFMANUFACTURE" : {
              "type" : "date",
              "ignore_malformed" : true
            },
4

Here is the _Source data for the specific example I gave.

{
  "took" : 953,
  "timed_out" : false,
  "_shards" : {
    "total" : 34,
    "successful" : 34,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [
      {
        "_index" : "REDACTED",
        "_type" : "_doc",
        "_id" : "E007A40008775073_8D6ACE2D6C8A59A402E1B78A8EAA1C",
        "_score" : null,
        "_ignored" : [
          "parametrics.m03_04_05_DATEOFMANUFACTURE",
          "parametrics.m03_45_46_OFFLOADINGTIMESTAMPUTC"
        ],
        "_source" : {
          "headers" : {
            "businessValidation" : true,
            "documentIntegrity" : true,
            "fileName" : "REDACTED",
            "filePath" : """REDACTED""",
            "lineNo" : 3,
            "machine" : "TRAYDATA",
            "machineType" : "REDACTED",
            "timestamp" : "2023-01-03T12:54:40.93289-06:00",
            "line" : "LIB03",
            "wO_Number" : "REDACTED",
            "variant" : "REDACTED",
            "count" : "40",
            "time" : "230103_125440",
            "wo_Complete" : "0",
            "serialNumber" : "E007A40008775073",
            "rejectedRecord" : false,
            "traySerial" : "REDACTED",
            "sample" : null,
            "source" : "REDACTED"
          },
          "parametrics" : {
            "applicator_SAMPLE" : null,
            "m03_01_FIXTURE" : "1",
            "m03_01_NESTNUMBER" : "2",
            "m03_04_05_UID" : "E007A40008775073",
            "m03_04_05_DATEOFMANUFACTURE" : "1/3/2023 12:48:04 PM",
            "m03_04_05_OCR_SERIALNUMBER" : "",
            "m03_06_8_13_15_PN_PATCH" : "REDACTED",
            "m03_06_8_13_15_LC_PATCH1" : "P00277",
            "m03_06_8_13_15_DC_PATCH1" : "Jun 1 2024 12:00AM",
            "m03_06_8_13_15_LC_PATCH2" : "P00277",
            "m03_06_8_13_15_DC_PATCH2" : "Jun 1 2024 12:00AM",
            "m03_21_23_PATCHDISTANCEFRONTMIN" : "0.314",
            "m03_21_23_PATCHDISTANCEFRONTCUR" : "0.54",
            "m03_21_23_PATCHDISTANCEFRONTMAX" : "1.114",
            "m03_21_23_PATCHDISTANCEFRONT_UOM" : "mm",
            "m03_21_23_PATCHDISTANCESIDE1MIN" : "0.314",
            "m03_21_23_PATCHDISTANCESIDE1CUR" : "0.722",
            "m03_21_23_PATCHDISTANCESIDE1MAX" : "1.114",
            "m03_21_23_PATCHDISTANCESIDE1_UOM" : "mm",
            "m03_21_23_PATCHDISTANCESIDE2MIN" : "0.314",
            "m03_21_23_PATCHDISTANCESIDE2CUR" : "0.716",
            "m03_21_23_PATCHDISTANCESIDE2MAX" : "1.114",
            "m03_21_23_PATCHDISTANCESIDE2_UOM" : "mm",
            "m03_21_23_VISJOB" : "production_v2.00",
            "m03_26_27_PN_HOUSING" : "REDACTED",
            "m03_26_27_LC_HOUSING1" : "M10007001",
            "m03_26_27_LC_HOUSING2" : "M10007000",
            "m03_26_27_LC_HOUSING3" : "M10007034",
            "m03_26_27_LC_HOUSING4" : "M10007079",
            "m03_26_27_LC_HOUSING5" : "M10007067",
            "m03_26_27_LC_HOUSING6" : "",
            "m03_26_27_PN_SHEATH" : "REDACTED",
            "m03_26_27_LC_SHEATH1" : "M10007120",
            "m03_26_27_LC_SHEATH2" : "M10007063",
            "m03_26_27_LC_SHEATH3" : "M10007096",
            "m03_26_27_LC_SHEATH4" : "M10006662",
            "m03_26_27_LC_SHEATH5" : "M10007150",
            "m03_26_27_LC_SHEATH6" : "",
            "m03_26_27_PN_SHARPCARRIER" : "REDACTED",
            "m03_26_27_LC_SHARPCARRIER1" : "M10006942",
            "m03_26_27_LC_SHARPCARRIER2" : "M10006999",
            "m03_26_27_LC_SHARPCARRIER3" : "M10007051",
            "m03_26_27_LC_SHARPCARRIER4" : "M10007066",
            "m03_26_27_LC_SHARPCARRIER5" : "M10007093",
            "m03_26_27_LC_SHARPCARRIER6" : "",
            "m03_26_27_PN_SPRING" : "REDACTED",
            "m03_26_27_LC_SPRING1" : "2000240017",
            "m03_26_27_LC_SPRING2" : "2000240018",
            "m03_26_27_LC_SPRING3" : "",
            "m03_26_27_LC_SPRING4" : "",
            "m03_26_27_LC_SPRING5" : "",
            "m03_26_27_LC_SPRING6" : "",
            "m03_26_27_PN_PUCKCARRIER" : "REDACTED",
            "m03_26_27_LC_PUCKCARRIER1" : "M10006934",
            "m03_26_27_LC_PUCKCARRIER2" : "M10007137",
            "m03_26_27_LC_PUCKCARRIER3" : "",
            "m03_26_27_LC_PUCKCARRIER4" : "",
            "m03_26_27_LC_PUCKCARRIER5" : "",
            "m03_26_27_LC_PUCKCARRIER6" : "",
            "m01_09_VISJOB" : "production_v1.00",
            "m01_10_VISJOB" : "production_v1.00",
            "m01_38_VISJOB" : "prodBlue_v1.00",
            "m02_11_VISJOB" : "production_v1.00",
            "m03_34_35_TRANSPONDERORIENTATIONMIN" : "-3",
            "m03_34_35_TRANSPONDERORIENTATIONCUR" : "1.314",
            "m03_34_35_TRANSPONDERORIENTATIONMAX" : "3",
            "m03_34_35_TRANSPONDERORIENTATION_UOM" : "deg",
            "m03_34_35_TRANSPONDERHEIGHTCLIP1MIN" : "-0.2",
            "m03_34_35_TRANSPONDERHEIGHTCLIP1CUR" : "0.382",
            "m03_34_35_TRANSPONDERHEIGHTCLIP1MAX" : "0.75",
            "m03_34_34_TRANSPONDERHEIGHTCLIP1_UOM" : "mm",
            "m03_34_35_TRANSPONDERHEIGHTCLIP2MIN" : "-0.2",
            "m03_34_35_TRANSPONDERHEIGHTCLIP2CUR" : "0.354",
            "m03_34_35_TRANSPONDERHEIGHTCLIP2MAX" : "0.75",
            "m03_34_34_TRANSPONDERHEIGHTCLIP2_UOM" : "mm",
            "m03_34_35_TRANSPONDERHEIGHTCLIP3MIN" : "-0.2",
            "m03_34_35_TRANSPONDERHEIGHTCLIP3CUR" : "0.261",
            "m03_34_35_TRANSPONDERHEIGHTCLIP3MAX" : "0.75",
            "m03_34_34_TRANSPONDERHEIGHTCLIP3_UOM" : "mm",
            "m03_34_35_VISJOB" : "production_v3.00",
            "m03_37_38_PN_CAP" : "REDACTED",
            "m03_37_38_LC_CAP1" : "M10007065",
            "m03_37_38_LC_CAP2" : "M10007100",
            "m03_37_38_LC_CAP3" : "M10007154",
            "m03_37_38_LC_CAP4" : "M10007157",
            "m03_37_38_LC_CAP5" : "",
            "m03_39_42_INSERTIONFINALANGLE" : "0.1025953",
            "m03_39_42_INSERTIONFINALANGLE_UOM" : "deg",
            "m03_40_43_VISJOB" : "production_v1.00",
            "m03_45_46_OFFLOADINGTIMESTAMPUTC" : "1/3/2023 12:53:28 PM",
            "rejectedRecord" : false
          },
          "currentDatetime" : "2023-01-03T13:03:45.4646682-06:00"
        },
        "sort" : [
          5806571
        ]
      }
    ]
  }
}
5

Now I was able to reproduce. In fact the use of "ignore_malformed" affects your query.
I believe that the best option is to work with valid date formats, in which case you would have to reindex the index to correct the dates.

6

Re-indexing is not really an option. These indices are really large.

My question is why is it that the SQL function is not able to return that value since it is visible when I pull the _source : true data, as well as is part of the JSON return, but is just in ignored field values.

Perhaps there is an enhancement that can be done to the sql API/ fields API when requesting data in an ignored field that it can still return the value??

7

It must probably be some limitation of the SQL API. I also suggest that you open an issue in git, this can help the elastic team to understand the problem.

8

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