Nested query for selecting multiple level nested columns


(Shash) #1

Trying to select multiple nested elements. Is there any way I can achieve the multiple level nested elements.

{
  "abcs": {
    "mappings": {
      "xyz": {
        "_all": {
          "enabled": false
        },
        "properties": {
          "A": {
            "type": "nested",
            "properties": {
              "ACCNO": {
                "type": "string",
                "index": "not_analyzed",
                "fielddata": {
                  "loading": "eager_global_ordinals"
                }
              },
              "B": {
                "type": "nested",
                "properties": {
                  "BRAND": {
                    "type": "string",
                    "index": "not_analyzed",
                    "fielddata": {
                      "loading": "eager_global_ordinals"
                    }
                  },
                  "C": {
                    "type": "nested",
                    "properties": {
                      "INSERTEDAT": {
                        "type": "date",
                        "format": "strict_date_optional_time||epoch_millis"
                      },
                      "PRTCODE": {
                        "type": "string",
                        "index": "not_analyzed",
                        "fielddata": {
                          "loading": "eager_global_ordinals"
                        }
                      },
                      "UPDATEDAT": {
                        "type": "date",
                        "format": "strict_date_optional_time||epoch_millis"
                      }
                    }
                  },
                  "CATEGORYCODE": {
                    "type": "string",
                    "index": "not_analyzed",
                    "fielddata": {
                      "loading": "eager_global_ordinals"
                    }
                  },
                  "CITCODE": {
                    "type": "string",
                    "index": "not_analyzed",
                    "fielddata": {
                      "loading": "eager_global_ordinals"
                    }
                  },
                  "D": {
                    "type": "nested",
                    "properties": {
                      "CATCODE": {
                        "type": "string",
                        "index": "not_analyzed",
                        "fielddata": {
                          "loading": "eager_global_ordinals"
                        }
                      },
                      "CATNAME": {
                        "type": "string",
                        "index": "not_analyzed",
                        "fielddata": {
                          "loading": "eager_global_ordinals"
                        }
                      },
                      "INSERTEDAT": {
                        "type": "date",
                        "format": "strict_date_optional_time||epoch_millis"
                      },
                      "UPDATEDAT": {
                        "type": "date",
                        "format": "strict_date_optional_time||epoch_millis"
                      }
                    }
                  },
                  "GROUPCODE": {
                    "type": "string",
                    "index": "not_analyzed",
                    "fielddata": {
                      "loading": "eager_global_ordinals"
                    }
                  },
                  "SUBCATEGORYCODE": {
                    "type": "string",
                    "index": "not_analyzed",
                    "fielddata": {
                      "loading": "eager_global_ordinals"
                    }
                  },
                  "UPDATEDAT": {
                    "type": "date",
                    "format": "strict_date_optional_time||epoch_millis"
                  }
                }
              }
            }
          },
          "VSTUS": {
            "type": "double"
          },
          "VTPE": {
            "type": "string",
            "index": "not_analyzed",
            "fielddata": {
              "loading": "eager_global_ordinals"
            }
          }
        }
      }
    }
  }
}

This is my mapping. I want fetch the documents of A, B, C and D from a single nested query without aggregation and all.

Here is my required sample sql query for understanding.

Select A.ACCNO, A.B.BRAND, A.B.C.PRTCODE, VSTUS from xyz

Can you please help me on this ?


(system) #2