Oracle NoSQL - how to find all the rows in a MAP where the key starts With a value

Question

I have a question about the MAP data type. Say I have a column labels ( labels MAP(RECORD(value STRING, contentType STRING)) in myTable, which the “labels” column is MAP data type and the value is a RECORD data type .

I want to query the table which returns all the rows that the key of the "labels" "startsWith" particular value ("xxx.*"),

I've tried this but I am wondering if there is a better way to do

Select labels.keys($key >='xxx') as keys, 
       labels.values($key >='xxx') as values 
from myTable where labels.keys() >=any ('xxx') 

Answer 1

You can try

select * from myTableName t 
where exists t.labels.keys(starts_with($key, 'xxx'));

or

select f.labels.keys(regex_like($key,'xxx.*')) as keys,
f.labels.values(regex_like($key,'xxx.*')) as values
from myTable f

I also suggest changing from MAP to ARRAY, which can support path filter to get the matched entries. In the previous examples, the order between the values and keys is not guaranteed

select labels[regex_like($element.label ,‘xxx.*’)] from myTable