SUFFIXES on SubDocument

SQL++
1

Hi all,

I’m wondering if someone could see my mistake I’m doing while creating a SUFFIX Index.

I simplify the Doc to make the conversation a bit easier.

Let’s assume the following Doc.

{
  title: 'xxxx München',
  name: 'ccvvff München bbfdfg'
  location: {
      city: 'München'
      area: 'Bayern'
 }

}

So I created two indexes already for title and name, which are working as expected.
No I tried to create as well a SUFFIX Index on location.city but this one is not used.

Here is my definition.

CREATE INDEX `guj_suffixes_locationCity` ON `guj`(distinct (array `city_array` for `city_array` in suffixes(lower(`location.city`)) end)) using GSI;

Now if I run the query:
SELECT distinct(location.city) from guj WHERE ANY city_array IN SUFFIXES(LOWER(location.city)) SATISFIES city_array LIKE ‘münchen%’ END;

the index isn’t used.

Explain:
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “PrimaryScan”,
“index”: “#primary”,
“keyspace”: “guj”,
“namespace”: “default”,
“using”: “gsi”
},
{
“#operator”: “Fetch”,
“keyspace”: “guj”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “any city_array in suffixes(lower(((guj.location).city))) satisfies (city_array like “münchen%”) end”
},
{
“#operator”: “InitialProject”,
“distinct”: true,
“result_terms”: [
{
“expr”: “((guj.location).city)”
}
]
},
{
“#operator”: “Distinct”
},
{
“#operator”: “FinalProject”
}
]
}
},
{
“#operator”: “Distinct”
}
]
},
“text”: “SELECT distinct(location.city) from guj WHERE ANY city_array IN SUFFIXES(LOWER(location.city)) SATISFIES city_array LIKE ‘münchen%’ END;”
}

On both of the first level fields, the index is working fine. So what’s my mistake here? I guess I have to define the SUFFIX index for SubDocuments differently, right?

Best
Kirsten

2

Hi @schulte

In create Index it needs to be
`location`.`city`

Inside back-ticks everything consider as IDENTIFIER/field it will not consider nested object. For nested objects needs separate back-ticks (or omit if no special characters present) for each field

Doc1 {"location.city" : "SJC"}
Doc2  {"location":{"city":"SJC"}}
`location.city` = "SJC" returns Doc1 not Doc2
`location`.`city` = "SJC" returns Doc2 not Doc1
3

Hi @vsr1,

ah yes sure.
I wasn’t able to see the wood for the trees.

Thank you, that obviously works.