# How to take children filed while use sql query it's parent?

**URL:** https://discuss.elastic.co/t/how-to-take-children-filed-while-use-sql-query-its-parent/314251
**Category:** Elasticsearch
**Created:** [September 13, 2022, 7:03am UTC](https://discuss.elastic.co/t/how-to-take-children-filed-while-use-sql-query-its-parent/314251 "2022-09-13T07:03:10Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![robocon20x](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/robocon20x/32/99145_2.png) [@robocon20x](https://discuss.elastic.co/u/robocon20x)
#### Post date: [September 13, 2022, 7:03am UTC](https://discuss.elastic.co/t/how-to-take-children-filed-while-use-sql-query-its-parent/314251/1 "2022-09-13T07:03:10Z")

</div>

i everyone, i have an parent child index with format like this [parent\_child ES doc](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-has-child-query.html).

```auto
     "customer_segment": {
        "type": "join",
        "eager_global_ordinals": true,
        "relations": {
          "customer": "segment"
        }
      }

```

i find out that ES support query use both SQL and dsl follow this [link](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-rest-filtering.html). then i use this query

```auto
POST _sql?format=txt
{
"query": "select customer_id, segment_ids from parent_v3_customers_42348_v2",
  "filter": {
     "has_child": {
      "type": "segment",
      "query": {
        "match_all": {}},
      "max_children": 1,
      "min_children": 1,
      "inner_hits": {}
    }
  }
}

```

segment\_ids is filed of children doc. I know that it's father (customer) still have this field but it just null. My question is how can i retrieve this field by using SQL like above? Here is the result if i use only dsl

```auto
 "hits" : [
      {
        "_index" : "parent_child_v3_customers_42348_v2",
        "_type" : "_doc",
        "_id" : "42348:16843175",
        "_score" : 1.0,
        "_source" : {
          "customer_id" : "16843175"
        },
        "inner_hits" : {
          "segment" : {
            "hits" : {
              "total" : {
                "value" : 1,
                "relation" : "eq"
              },
              "max_score" : 1.0,
              "hits" : [
                {
                  "_index" : "parent_child_v3_customers_42348_v2",
                  "_type" : "_doc",
                  "_id" : "42348:16843175:1",
                  "_score" : 1.0,
                  "_routing" : "42348:16843175",
                  "_source" : {
                    "segment_array" : [
                      "887853",
                      "999999",
                      "719652",
                      "742011",
                      "123456",
                      "694817",
                      "872059",
                      "546241"
                    ],
                    "customer_parent" : "16843175",
                    "customer_segment" : {
                      "parent" : "42348:16843175",
                      "name" : "segment"
                    }
                  }
                }
              ]
            }
          }

```

Thanks

---

<div class="post-metadata">

### Author: ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)
#### Post date: [October 11, 2022, 7:03am UTC](https://discuss.elastic.co/t/how-to-take-children-filed-while-use-sql-query-its-parent/314251/2 "2022-10-11T07:03:29Z")

</div>

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