Overview# I am currently working on updating the search application for the Cultural Japan project, and I needed to perform aggregation on multilingual data. This article is a memo of the investigation results regarding the methods.
Data# For the data, we assume a case where the agential (indicating a person) field has values for id, ja, and en.
{ } " ] a g { } e n " " " t j e i i a n d a " " " l : : : " : " " " 葛 K c [ 飾 a h 北 t n 斎 s a " u m , s e h : i 葛 k 飾 a 北 , 斎 " H o k u s a i " ,
For the above data, we want to perform filtering by id while displaying the ja or en value according to the language setting.
Ideally, we would like to obtain the following data as the aggregation result.
(When ja is specified)
{ } " ] b u { } c k " " " e k i d t e d o s y " c " " : _ : : c " o [ " c u 葛 h n 飾 n t 北 a " 斎 m : " e , : 1 葛 飾 北 斎 " ,
(When en is specified)
{ } " ] b u { } c k " " " e k i d t e d o s y " c " " : _ : : c " o [ " c u K h n a n t t a " s m : u e s : 1 h 葛 i 飾 k 北 a 斎 , " , H o k u s a i " ,
Method 1: Using Nested Aggregation# Following the article below, let’s try nested aggregation.
https://discuss.elastic.co/t/aggregeations-with-different-keys-and-values-label-and-id/274218
D P { } P { } G { } E U U E L T " } T " ] T " } " ] " } E m a q , _ , a T t a " } t g { } t u " } s " g " } E e p p e e e e b o a g a s p r " } s n " " " s r o " ] u g s g " } " } t t i o a t t j e i t y o f r e " e n , a e n p g " " } / i a n d / " l i { } c n : n e " g " } " } s g e e t p _ a " " " _ : " l e t t s p g i , l t s r n y r " } " } " } d l : : : s : t " } " i { i t a s d " } a " } " t t p o i , j , e o " e { e n : a a e t " " t b t : i i e p d " a " n " c : " " " a { r e " " } l l d h : : e " e e " e a " e " t " t " t / 葛 K c r " s p q [ " " " " r f l r f { s l : r : y : y : y 1 [ 飾 a h c : t a u " } : : : { { m i " m i " " t p p p 北 t n h e t e b s e : s e : : " i { e { e { e 斎 s a [ d h r o " ] { { " " l " l n e " " " " u m " " y o f a : d { : d { { e s : : : , s e : : " l i { } g " " s " h : : " l e { : { : t : " " " i 葛 { " : t " } n e k k k k 飾 a { e t t " " d { e e e a 北 g { r e " i a a " y y y , 斎 e " r a a g g , w w w " n : m g l e e o o o H t " e " n n r r r o i [ : n t t d d d k a t i i " " " u l { i a a s " a l l a , l . . i . i j " i d a , d " " " : " c h n a m e : 葛 飾 北 斎 "
In this case, the following result is returned.
{ } " " " } " } " } t t _ , h , a o i s " " " " i " } " " ] g " } o m h t s s f t t , m h g a k e a o u k a s o " " a i { } r g " " } " } " d r t c i i " t v r x t e e d l , i _ d a c p l a a e _ s " " " " " } g n o a " " " ] d " " " ] : o s l e p e : l l l s " _ _ _ _ _ a t c b d s b " d s b u " " s e d " u a c i t i s s " ] t i _ e o u u { } o u u { } 9 t s d " { e t o : n y d c o a i a c l c m c : c m c , " : : f " : " i r d p " o u g { } o l o " _ _ k " " _ _ k " " u : o e [ e e r r e n " u c o e k d { c o e k d : { 5 l : { : n " x " : e c n " " " s n : o t t e o o t t e o , " 0 " " " e t j e i " : t u h s y c u h s y c f 0 1 : : " " i a n d " { n e " " _ n e " " _ a : , , : : 1 : a " " " : { t r c t r c l 0 " " : l : _ _ : : o _ _ : : o s 5 " . " _ , 0 " : : : { e d u e d u e , e 0 t d . { 1 r o [ " n r o [ " n , q , e o 0 : " " " , r c 葛 t r c c t " s c , 葛 K c o _ 飾 " o _ h " t " [ 飾 a h r c 北 r c n " , 北 t n _ o 斎 : _ o a : , 斎 s a u u " u u m " u m p n , 1 p n e 1 , s e p t p t : h : e " e " 葛 i 葛 r r 飾 k 飾 _ : _ : 北 a 北 b b 斎 , 斎 o 0 o 0 " " u , u , , H n n o d d k " " u s : : a i 0 0 " , , ,
Both label and id are returned in aggregations.agential, but this seems like a redundant result.
Method 2: String Concatenation# Let’s try the method raised in the question of the following article.
https://stackoverflow.com/questions/70545830/aggregation-that-returns-the-users-name-and-id
I prepared an fc-agential field and inserted a value that concatenates id, ja, and en values using $$$ as a delimiter.
D P { } P { } G { } E U U E L T " } T " ] " ] T " } " ] " } E m a , f q , _ , a T t a " } t g { } c " t u " } s " g " } E e p p e e - c e e b o a g a s p r " } " } s n " " " a h s r o " ] u g s g " } t t i o a , f t t j e i g n t y o f r e " e t e n p g " } c " / i a n d e a / " l i { } c n : n e " s g e e p - t _ a " " " n m _ : " l e t t r f t s r n r " } " } " } a y d l : : : t e s : t " } " i { i m i " t t o i , j , e g p o " i : e { e t : a a s e : i i p d " a " n " e e c : " " " a 葛 a { r e " l l " l e a e " t " t " t n " / 葛 K c l 飾 r " r a [ " " : d { s l r : y : y : y t : 1 [ 飾 a h " 北 c : m g : " " " t p p p i 北 t n : 斎 h " e { : : : i { e { e { e a " 斎 s a $ [ : n { e " " " l k " u m [ $ t " { { s : : : " e , s e $ { i f " : y h : 葛 a c : " " " w i 葛 飾 l - k k k { o k 飾 北 . a { e e e r a 北 斎 i g y y y d , 斎 $ d e w w w " " $ " n o o o H $ : t r r r o K i d d d k a " a " " " u t c l s s h " a u n i s a " h m , i e k : a 葛 , 飾 北 H 斎 o " k u s a i "
In this case, the following result is returned.
{ } " " " } " } " } t t _ , h , a o i s " " " " i " } " " ] g " } o m h t s s f t t , m h g a k e a o u k a s o " " a i { } r g " " " ] " d r t c i i " t v r x t e e d s b _ d a c p l a a e _ s " " " " " } g n o u u { } : o s l e p e : l l l s " _ _ _ _ _ a t c m c u " " s e d " u a c i t i s s " ] t i _ _ k " " 9 t s d " { e t o : n y d c o a i a c o e k d 6 " : : f " : " i r d p " o u g { } o l o t t e o 4 u : o e [ e e r r e n " u h s y c , : { 5 l : { : n " x " : e c n " " " s n e " " _ , " 0 " " " e t j e i " : t r c f 0 1 : : " " i a n d _ _ : : o a : , , : : 1 : a " " " : { e d u l 0 " " : l r o [ " n s 5 " . " _ , 0 " : : : { r c c t e , e 0 t d . { o _ h " , q , e o 0 : " " " r c n " s c , 葛 K c _ o a : t " [ 飾 a h u u m " , 北 t n p n e 1 , 斎 s a p t : " u m e " 葛 , s e r 飾 h : _ : 北 i 葛 b 斎 k 飾 o 0 $ a 北 u , $ , 斎 n $ " d 葛 H " 飾 o 北 k : 斎 u $ s 0 $ a , $ i K " a , t s u s h i k a , H o k u s a i " ,
Since nested is not used, the query and results are simpler, but the data needs to be modified after retrieval.
Method 3: A Nested Top-Hits Aggregation?# Let’s try the method raised as an answer in the following article.
https://stackoverflow.com/questions/70545830/aggregation-that-returns-the-users-name-and-id
D P { } P { } G { } E U U E L T " } T " ] T " } " ] " } E m a q , _ , a T t a " } t g { } t u " } s " g " } E e p p e e e e b o a g a s p r " } s n " " " s r o " ] u g s g " } " } t t i o a t t j e i t y o f r e " e t , a e n p g " } / i a n d / " l i { } c n : n e " g " } s g e e p _ a " " " _ : " l e t t r f g d t s r n r " } " } " } d l : : : s : t " } " i { i m i s o " } " t t o i , j , e o " e { e t : a a s e " c t : i i p d " a " n " c : " " " a { r e " l l " l : " o " e a e " t " t " t / 葛 K c r " r a [ " " : d : p s { s l r : y : y : y 1 [ 飾 a h c : m g : " { _ i " " t p p p 北 t n h " e { : { h z : : i { e { e { e 斎 s a [ : n { i e e " " " " u m t " t " { { s : : : , s e { i a s : " h : a g " : " " " i 葛 l e : 1 k k k k 飾 . n { e e e a 北 i t { y y y , 斎 d i w w w " " a o o o H : l r r r o . d d d k " j " " " u c a s h " a n i a " m , e : 葛 飾 北 斎 "
In this case, the following result is returned.
{ } " " " } " } " } t t _ , h , a o i s " " " " i " } " " ] g " } o m h t s s f t t , m h g a k e a o u k a s o " " a i { } r g " " " ] " d r t c i i " t v r x t e e d s b _ d a c p l a a e _ s " " " " " } g n o u u { } : o s l e p e : l l l s " _ _ _ _ _ a t c m c u " " s e d " u a c i t i s s " ] t i _ _ k " " " } 7 t s d " { e t o : n y d c o a i a c o e k d d , " : : f " : " i r d p " o u g { } o l o t t e o o " } u : o e [ e e r r e n " u h s y c c h : { 5 l : { : n " x " : e c n " " " s n e " " _ " i " } " " ] , " 0 " " " e t j e i " : t r c t t , m h f 0 1 : : " " i a n d _ _ : : o : s o " " a i { } a : , , : : 1 : a " " " : { e d u " t v r x t l 0 " " : l r o [ " n { a a e _ s " " " " " } s 5 " . " _ , 0 " : : : { r c 葛 t : l l l s " _ _ _ _ _ e , e 0 t d . { o _ 飾 " " u a c i t i s s " ] , q , e o 0 : " " " r c 北 { e t o : n y d c o a " s c , 葛 K c _ o 斎 : : " i r d p " o u g { } t " [ 飾 a h u u " o e [ e e r r e " , 北 t n p n , 1 { : n " x " : e c n " " " , 斎 s a p t , " " " e t j e i " u m e " 1 : : " " i a n d , s e r , : : 1 : a " " " h : _ : 0 " " : l i 葛 b " . " _ , 0 " : : : k 飾 o 0 e 0 t d . { a 北 u , q , e o 0 : " " " , 斎 n " s c , 葛 K c " d t " [ 飾 a h H " " , 北 t n o , 斎 s a k : " u m u , s e s 0 h : a , i 葛 i k 飾 " a 北 , , 斎 " H o k u s a i " ,
Since aggregations.agential includes an example result, you can extract the relationship between id and ja from there. Compared to Method 2, the advantage is that there is no need to prepare a field like fc-agential, but this result also seems redundant.
Summary# I summarized the investigation results on aggregations with different keys and values (labels and IDs) in Elasticsearch. I hope this serves as a helpful reference.
Also, I believe there may be better methods beyond the three presented above. If anyone knows of any, I would appreciate it if you could share them.