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):
{
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:
{
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.