Compound analytics index usage on joins

[TonyCastan]

Hello,
I have the following analytics indexes:

create index observationsSingleItemsCodingIdx
on hdmpDevFhirLatest._default.ObservationsSingleItems (subject.reference:string, (unnest code.coding select code:string))
exclude unknown key;

create index observationsSingleItemsSubjectRefIdx
on hdmpDevFhirLatest._default.ObservationsSingleItems (subject.reference:string);

And the following query:

select pat.*
from
(select meta(p).id as pid, p.identifier[0].value as NISS, (p.name[0].given[0] || ’ ’ ||p.name[0].family) as Name
from hdmpDevFhirLatest._default.Patients p
where p.managingOrganization.reference=‘Organization/303’) as pat
join hdmpDevFhirLatest._default.ObservationsSingleItems si on any cod in si.code.coding satisfies to_string(‘Patient/’ || pat.pid) /*+ indexnl */ = si.subject.reference and ‘009B’ = cod.code;

In the access plan I see is using the index “observationsSingleItemsSubjectRefIdx” in the join. What should I do to make use of the compound index that includes the filtering by code “observationsSingleItemsCodingIdx”?

Thank you for your help!

[ali.alsuliman]

Hi @TonyCastan,
Using a composite-atomic array index to satisfy a join predicate like the one in your case is not currently supported.