# Bucket\_path for array key

**URL:** <https://discuss.elastic.co/t/bucket-path-for-array-key/323052>\
**Category:** Elasticsearch\
**Created:** [January 12, 2023, 6:44pm UTC](https://discuss.elastic.co/t/bucket-path-for-array-key/323052 "2023-01-12T18:44:45Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![rbrc](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rbrc/32/2937_2.png) [@rbrc](https://discuss.elastic.co/u/rbrc)\
**Post date:** [January 12, 2023, 6:44pm UTC](https://discuss.elastic.co/t/bucket-path-for-array-key/323052/1 "2023-01-12T18:44:45Z")

</div>

I'm doing some agg queries against indexes where each doc has a locationId, and using a terms agg on that locationId to bucket the results. The problem is I want the NAME for that location, so I'm also doing a filter agg on the location index to pull the location record and then a terms agg in there to bucket the NAME from the single doc that gets returned. This feels overly complicated but it does work. The only problem is that I want to sort the top level bucket by the location NAME and I can't figure out the bucket path to that array index.

Here's the query (javascript):

```auto
{
    size: 0,
    query: {
      bool: {
        filter: terms?.map((t) => ({
          query_string: {
            query: t,
          },
        })),
      },
    },
    aggs: {
      group_by_location_id: {
        terms: {
          size: 65536,
          field: `locationId.keyword`,
        },
        aggs: {
          location_name: {
            filter: {
              bool: {
                filter: {
                  query_string: {
                    query: `_type:Location`,
                  },
                },
              },
            },
            aggs: {
              name: {
                terms: { field: `name.keyword` },
              },
            },
          },
          leads: {
            filter: {
              bool: {
                filter: {
                  query_string: {
                    query: `becameLeadDate:[${fromDate} TO ${toDate}]`,
                  },
                },
              },
            },
          },
          mqls: {
            filter: {
              bool: {
                filter: [
                  {
                    query_string: {
                      query: `becameLeadDate:[${fromDate} TO ${toDate}]`,
                      analyze_wildcard: true,
                    },
                  },
                  {
                    query_string: {
                      query: `becameMqlDate:*`,
                      analyze_wildcard: true,
                    },
                  },
                ],
              },
            },
          },
          appointments: {
            filter: {
              bool: {
                filter: [
                  {
                    query_string: {
                      query: `becameLeadDate:[${fromDate} TO ${toDate}]`,
                      analyze_wildcard: true,
                    },
                  },
                  {
                    query_string: {
                      query: `appointmentMadeDate:*`,
                      analyze_wildcard: true,
                    },
                  },
                ],
              },
            },
          },
          patients: {
            filter: {
              bool: {
                filter: [
                  {
                    query_string: {
                      query: `becameLeadDate:[${fromDate} TO ${toDate}]`,
                      analyze_wildcard: true,
                    },
                  },
                  {
                    query_string: {
                      query: `becamePatientDate:*`,
                      analyze_wildcard: true,
                    },
                  },
                ],
              },
            },
          },
          production: {
            sum: { field: `ltv` },
          },
          budget: {
            filter: {
              bool: {
                filter: [
                  {
                    query_string: {
                      query: `yearMonth:[${fromDate} TO ${toDate}]`,
                    },
                  },
                  {
                    query_string: {
                      query: `enabled:true`,
                    },
                  },
                ],
              },
            },
            aggs: {
              budget: { sum: { field: `amount` } },
            },
          },
          sort: {
            bucket_sort: {
              size: 100,
              from: page * 100,
              sort: [{ [sort]: { order: sortDirection } }],
            },
          },
        },
      },
    },
  }

```

so my buckets look something like this:

```auto
        {
          key: '606d23db41342d001514dc01',
          doc_count: 6399,
          appointments: { doc_count: 19 },
          location_name: {
            doc_count: 1,
            name: {
              doc_count_error_upper_bound: 0,
              sum_other_doc_count: 0,
              buckets: [{ key: 'Location Name', doc_count: 1 }]
            }
          },
          production: { value: 4972953 },
          leads: { doc_count: 25 },
          patients: { doc_count: 14 },
          mqls: { doc_count: 25 },
          budget: { doc_count: 1, budget: { value: 8581 } }
        },

```

I want to sort on `location_name>name[0]` basically, but that syntax isn't correct. What's the correct syntax for indexing into that array? Alternately, is there a better way to PICK the name from the location index by the locationId which is the bucket key on the top level agg?

Thank you.

---

<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:** [February 9, 2023, 6:45pm UTC](https://discuss.elastic.co/t/bucket-path-for-array-key/323052/2 "2023-02-09T18:45:24Z")

</div>

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