Obtain One Array from N Arrays (possibly with distinct)

SQL++
1

Hello, I am evaluating using Couchbase Server Version: 4.5.0 Enterprise Edition for a new application and we are testing some function that would be very useful for our project.
For example we have similar data for a single subject coming from different sources:

[
{
“I472_ESLB”: {
“Bnfcancellato”: false,
“Rows”: [
{
“Bnfcancellato”: false,
“Cognome”: “DAMASCHINO”,
“Importuid”: “2d8571521b26494bae27a47ec96e1617”,
“Origine”: “CNS”,
“Tiposoggetto”: “P”
},
{
“Bnfcancellato”: false,
“Cognome”: “DAMASCINO”,
“Importuid”: “2d8571521b26494bae27a47ec96e1617”,
“Origine”: “CNS”,
“Tiposoggetto”: “P”
},
{
“Bnfcancellato”: false,
“Cognome”: “DAMASCHINO”,
“Importuid”: “388679c0d1ff45a0a63ccb3c485a1b74”,
“Origine”: “ANA”,
“Periodi”: [
{
“Codicefamiglia”: “3524087”,
}
],
“Tiposoggetto”: “P”
},

  ... {N more elements}

  ],
  "VkeyArea": "SOG"
}

}
]

if I make an index for optimizing a search for the field Cognome

CREATE INDEX sog_cognome_idx ON I472_ESLB (DISTINCT ARRAY SUFFIXES(LOWER(k.Cognome)) FOR k IN Rows END) WHERE VkeyArea=“SOG”

it will contain:

[
{
“$1”: [
[
“damaschino”,
“amaschino”,
“maschino”,
“aschino”,
“schino”,
“chino”,
“hino”,
“ino”,
“no”,
“o”
],
[
“damascino”,
“amascino”,
“mascino”,
“ascino”,
“scino”,
“cino”,
“ino”,
“no”,
“o”
],
[
“damaschino”,
“amaschino”,
“maschino”,
“aschino”,
“schino”,
“chino”,
“hino”,
“ino”,
“no”,
“o”
]
]
}
]

as you can see it contains redundant data and it has a structure that i can’t use with a simple select

SELECT * FROM I472_ESLB WHERE ANY i IN SUFFIXES(LOWER((ARRAY k.Cognome FOR k IN Rows END))) SATISFIES i LIKE “damasci%” END AND VkeyArea=“SOG”

in practise instead of the array of arrays generated from the index for the selects queries with like operator it would be better to obtain a single array with all the elements, possibily with distinct, like this:

[
{
“$1”: [
“damaschino”,
“amaschino”,
“maschino”,
“aschino”,
“schino”,
“chino”,
“hino”,
“ino”,
“no”,
“o”,
“damascino”,
“amascino”,
“mascino”,
“ascino”,
“scino”,
“cino”

 ]

}
]

so i need a function to merge all the variable N arrays in a single array with cleaning of duplicate entries for the purpose of using it as an index for my search with like operator. Is it possibile?
Thanks

2

Yes, it is all supported. See ARRAY_CONCAT, ARRAY_FLATTEN, and ARRAY_DISTINCT. We are happy to help you get the right queries and indexes for your evaluation. BTW, could you use 4.5.1 EE?

Note that SUFFIXES is only needed if you need partial word matches. For full word matching, there are other options.

1 Like
3

Thanks for the support Gerald, ARRAY_FLATTEN was the command we were searching for.
Unfortunately we couldn’t found the documentation in the language reference page but only an example of use in the update command.
Is there any place where we can found a complete list of the functions and commands?

4

Hi @prasad @keshav_m, we need to update the list of all functions, perhaps with Couchbase version for each function.

1 Like