Nested arrays with FIRST

SQL++
1

Hello,
I am trying to use FIRST in a select, in a doubly nested arrays context. Documents have typically the form:

{
“one”: [
{
“two”: [
{
“three”: 0
},
{
“three”: 1
}
]
},
{
“two”: [
{
“three”: 2
},
{
“three”: 3
}
]
}
]
}

one and two are both arrays, two is nested in one.
I would like to retrieve the first value of three and the documents ids of the documents which respect some condition (say >1) on any of their three values exactly in the one->two->three path (no other place in the document where other three fields might appear). I want no more than one row per document (hence no UNNEST), and the simplest efficient select possible.

Creating the index does not seem to be an issue, I do as follows:

CREATE INDEX myindex ON test(DISTINCT ARRAY(DISTINCT ARRAY(v1.three) FOR v1 IN v0.two END) FOR v0 IN one END)

Selecting, without use of UNNEST, and with the FIRST keyword is a challenge for me. I am probably missing something on the N1QL syntax. The WHERE clause making use of the index is quite easy:

WHERE ANY v0 IN one SATISFIES ANY v1 IN v0.two SATISFIES v1.three>1 END END

It’s the beginning of the select which is unclear to me. I’d like something like (’…’ replaced by proper N1QL syntax):

SELECT FIRST v1.three FOR v1 IN … WHEN v1.three>1 END, meta().id FROM test USE INDEX (myindex) WHERE ANY v0 IN one SATISFIES ANY v1 IN v0.two SATISFIES v1.three>1 END END

Trying something silly like this below actually returns the proper response, but is does not contain the value of the three field.

SELECT FIRST v1.three FOR v1 IN (ANY v0 IN one SATISFIES 1=1 END) WHEN v1.three>1 END, meta().id FROM test USE INDEX (myindex) WHERE ANY v0 IN one SATISFIES ANY v1 IN v0.two SATISFIES v1.three>1 END END

Can somebody let me know how to achieve this?
Thanks a lot!

2

Based on your requirement it looks UNNEST does better performance. It should use Unnest available in the Index and query shouldn’t do Unnest (check following requirements)

  • Use ALL Index

  • Make sure index variable and query unnest aliases are same

  • Check EXPLAIN. It should not conation any Unnest (IndexScan,Filter,Projection, covered index)

    CREATE INDEX myindex ON test (ALL ARRAY (ALL ARRAY v1.three FOR v1 IN v0.two END)
    FOR v0 IN one END);
    SELECT v1.three
    FROM test AS t
    UNNEST t.one AS v0
    UNNEST v0.two AS v1
    WHERE v1.three > 1;

If you want use FIRST,

CREATE INDEX `myindex` ON `test` (DISTINCT ARRAY (DISTINCT ARRAY  v1.`three`  FOR v1 IN v0.`two` END) 
                                                                              FOR v0 IN `one` END);

    

     SELECT  FIRST (FIRST v1.three FOR v1 IN v0.two WHEN v1.three > 1 END)  FOR v0 IN t.one END
     FROM test AS t
     WHERE ANY v0 IN t.one SATISFIES (ANY v1 IN v0.two SATISFIES v1. three > 1 END) END;

FYI: In your example v1 is defined in ANY clause and scope is limited to ANY. Not available in projection,
Projection can access FROM aliases (implicit or explicit), LET, LETTING variables. Array constructs variables can be used same level or child expression and can’t be used in parent expression.

1 Like
3

Very nice, big thanks for your help!!!