N1QL query to find all elements from a field which is array type

SQL++
1

Consider the below query

SELECT tags
FROM content
WHERE ANY tag IN tags SATISFIES ( ANY ae IN SUFFIXES(LOWER(tag)) SATISFIES ae
LIKE “bird%” END) END
AND type = “PUBLICIMAGE”;

I am using the below Index for the above query
CREATE INDEX UserAsset_image_tags_PUBLICIMAGE ON content(DISTINCT ARRAY
(DISTINCT ARRAY ae FOR ae IN SUFFIXES(LOWER(tag)) END)
FOR tag IN tags END)
WHERE type=“PUBLICIMAGE”;

Here “tags” is array type.“tags” may contain multiple tag names.When I search the keyword “bird” it should return all the tags name with “bird” i.e bird, bird1, bird2 etc from all the tags in the document.Is it possible using N1QL query?

1 Like
2 
SELECT  ARRAY tag FOR tag IN tags WHEN CONTAINS(LOWER(tag), "bird") END AS tags
FROM content
WHERE ANY tag IN tags SATISFIES ( ANY ae IN SUFFIXES(LOWER(tag)) SATISFIES ae
LIKE "bird%" END) END
AND type = "PUBLICIMAGE";


OR

SELECT tag
FROM content AS c
UNNEST c.tags AS tag
WHERE ANY ae IN SUFFIXES(LOWER(tag)) SATISFIES ae LIKE "bird%" END
AND c.type = "PUBLICIMAGE";
3

Hi
Thanks for your response

I have used the first query refered by you, with little modification like below

SELECT RAW ARRAY tag FOR tag IN tags WHEN CONTAINS(LOWER(tag), “bird”) END AS tags
FROM content
WHERE ANY tag IN tags SATISFIES ( ANY ae IN SUFFIXES(LOWER(tag)) SATISFIES ae
LIKE “bird%” END) END
AND type = “PUBLICIMAGE” ;

and the result comes like

[
[
“bird”,
“water bird”,
“shorebird”,
“birds”
],
[
“bird”,
“water bird”,
“shorebird”,
“seabird”,
“songbird”,
“birds”
],
[
“bird”,
“blackbird”,
“birds”
]
]

can I club the above result in to one array or an object?
Expected result :
[
[
“bird”,
“water bird”,
“shorebird”,
“birds”,
“seabird”,
“songbird”,
“blackbird”,
]
]

4 
SELECT  RAW ARRAY_FLATTEN(ARRAY_AGG(ARRAY tag FOR tag IN tags WHEN CONTAINS(LOWER(tag), "bird") END),2)
FROM content
WHERE ANY tag IN tags SATISFIES ( ANY ae IN SUFFIXES(LOWER(tag)) SATISFIES ae
LIKE "bird%" END) END
AND type = "PUBLICIMAGE";
5

Thanks for your valuable reply.

Your suggested query working fine and keeping all the element in one array.But I am getting many duplicate entry here.and unable to prevent duplicate values in the array.Is there a way by which I can prevent duplicate entry in the array?
and the Expected result :
[
[
“bird”,
“water bird”,
“shorebird”,
“birds”,
“seabird”,
“songbird”,
“blackbird”,
]
]

6

I fulfilled my requirement with the following query

SELECT RAW ARRAY_DISTINCT ( ARRAY_FLATTEN(ARRAY_AGG(ARRAY tag for tag IN tags WHEN CONTAINS(LOWER(tag),“bird”)END),2))
FROM content
WHERE ANY tag IN tags SATISFIES ( ANY ae IN SUFFIXES(LOWER(tag)) SATISFIES ae
LIKE “bird%” END) END
AND type = “PUBLICIMAGE” ;

Thanks