Wildcards in index names and JOINs are not completely supported. This works in simple queries only. Try this:
SELECT e.name AS employeeName,
e.projects.name AS projectName
FROM `employees_nested_exact_index_name_here` AS e
WHERE e.projects.name LIKE '%security%'
Do you know if your data has arrays of objects within a record? For example,
array of objects: "projects": [{"name":"project1"}, {"name":"project2"}]
and
array of values: "projects": {"name":["project1","project2"]}
@yuryf-bq’s query in Index patterns for Querying nested collection - #6 by yuryf-bq seems to work with 2.6. However, there is a bug regarding arrays in objects that is critical. You need to ensure that you do not query fields where values may be arrays while waiting for nested to be fully implemented in V2.
Could you provide mappings and/or sample data for more context?