Fulltextsearch Problem JSON Column Elasticsearch

Hello there, I’ve a 9Gb index stored in Elasticsearch, I’ve managed to connect dremio to Elasticsearch and I’m trying to take advantage of the fulltext search features.

However since I’ve some Json Columns it is not working, in theory I’m trying to use a query like the following:

SELECT *, RCSA.personnes.personne.personneMorale.administration as admin

FROM spiral.“rcs-a_immat”.“RCS-A_IMMAT” RCSA

where contains(“rcs-a_immat”.“RCS-A_IMMAT”.personnes.personne[0].personneMorale.administration:(“PEYRE”))

However the problem is the Json column, specifically"rcs-a_immat".“RCS-A_IMMAT”.personnes.personne[0].personneMorale.administration that contains an array and is not recognized, I could use the query with the like operator but this way it takes too long time (guess it is making a full table scan).

In comparison in elasticsearch i can use a match request using “rcs-a_immat”.“RCS-A_IMMAT”.personnes.personne.personneMorale.administration: directly and works correctly.

Your help would be greatly appreciated.

Freddy Ayala

You may need to unnest the array in order to search it. Can you try that?