Filter by min nested value

[IsaacInstrumental]

I have a bunch of documents of the form:

[
    {
        title: string,
        comments: [
            comment_body: string,
            created_date: date,
        ],
    },
    ...
]

How can I get the posts whose first comment is after a specific date? I've searched for a solution and read the documentation, and I've found a bunch of things about aggregations and querying nested values, but I haven't found a way to put everything together.

[xavierfacq]

Hi,

Have a look to this doc: https://www.elastic.co/guide/en/elasticsearch/reference/5.5/search-request-sort.html#nested-sorting

hope this help

[IsaacInstrumental]

Thank you for responding.

I did find that page about nested sorting in the documentation, but it doesn't solve the problem of filtering to posts whose first comment is after a specific date. Currently I have only discovered how to filter to posts that have any comment after a specific date, sorted by the earliest comment's date. However, that includes posts whose earliest comment is after the specified date, which is not what I want.

[xavierfacq]

Hi,

And if you do a boolean query like this:

must have nested comments after "the date"
must NOT have nested comment BEFORE "the date"

order by nested comment date

Does it solve the problem ?

[IsaacInstrumental]

That worked! Can't believe I didn't think of that. Thanks for your help

For others who are looking for this in the future, here's an example query:

{
	query: {
		bool: {
			must: [
				{
					nested: {
						path: 'comments',
						query: {
							bool: {
								must: [
									{
										range: {
											'comments.created_date': {
												gte: '2017-07-24T15:57:25.145Z',
											},
										},
									},
								],
								must_not: [
									{
										range: {
											'comments.created_date': {
												lt: '2017-07-24T15:57:25.145Z',
											},
										},
									},
								],
							},
						},
					},
				},
			],
		},
	},
	size: 10000,
	sort: {
		'comments.created_date': {
			order: 'desc',
			mode: 'max',
			nested_path: 'comments',
		},
	},
}

[xavierfacq]

@IsaacInstrumental

That worked!

good to hear that ! You can mark as Resolved