How to get the array index number for each unnested array element in N1QL

SQL++
,
1

I have not been able to find anything on this online or in the docs:

When you unnest an array in a N1QL query, how can you also get the element number for each unnested / flattened element in the array? In the case below for each specChallenge?

SELECT
d.accountId
,specChallenge.order
FROM data d
UNNEST OBJECT_NAMES(d.challengeArchive) as challenge
UNNEST d.challengeArchive.[challenge].challenges as specChallenge

2

UNNEST_POS(unnestAlias)

SELECT d.accountId, ca.name, sc.`order`, UNNEST_POS(sc) AS upos
FROM data AS d
UNNEST OBJECT_PAIRS(d.challengeArchive) AS ca
UNNEST ca.val.challenges AS sc;

users is object

FOR n:v IN users END
   iterates each field of users (n holds name, v holds value)

users is ARRAY

FOR pos:v IN users END
   iterates each element  of users (pos holds position, v holds value)

UNNEST can be done only on ARRAY, if need on object like above transform object into ARRAY using OBJECT_* functions.

3

It works!! How did you know that? I still can’t find it in the docs, is it undocumented?
Thanks you saved my day / days.
PS: Not needed for objects, there the OBJECT_PAIRS give me all I need.
Thanks again.

4

It missed in documentation. Track DOC-10324

FYI: N1QL functions query/func_registry.go at master · couchbase/query · GitHub

1 Like
5

There are a lot of undocumented functions. Is that on purpose, not stable or? Seems weird.

6

Every thing stable and you can use. Some might have alternatives (example plus() same as a+b, some synonyms)