Query without using unnest

SQL++
,
1

I found a long time ago a solution, but i cant remember it and find it in the forum.
I want a query that give me every element of a jsonarray of a document without using unnest.
For example if i have
{
“a” : “b”
“B”: “c”:
“d”: [{obj1},{obj2}, {obj3}
]
}
I want as a result “d” like this
[
{obj1},{obj2},{obj3}
]
If i use raw i get
[[
{obj1},{obj2},{obj3}
]]
With unnest i get a correct answer but i dont want to use it.
If i remember correctly, the solution that i remember used Array_flatten with a subquery maybe

2 
SELECT RAW u
FROM <collection> AS t
UNNEST t.d AS u
WHERE ....
3

UNNEST is intended exactly for this; is there a reason you don’t want to use it?

I presume you want your results array to only contain the elements of “d” from multiple documents:

cbq> WITH src AS ([
                   {"d":[{"o":1},{"o":2}]}
                  ,{"d":[{"o":3},{"o":4}]}
                 ])
   2 SELECT RAW t
   3 FROM (SELECT RAW array_flatten(array_agg(d),1) FROM src)[0] t
   4 ;
{
    "requestID": "33f9b239-5508-4881-897a-1166f23040a2",
    "signature": {
        "*": "*"
    },
    "results": [
    {
        "o": 1
    },
    {
        "o": 2
    },
    {
        "o": 3
    },
    {
        "o": 4
    }
    ],
...

but it is cumbersome and uses more resources than UNNEST will (in most typical cases).

HTH.

4

Thanks for your answer, but I already knew this solution, i was looking for the solution of @dh

5

Thanks a lot, I was looking for this solution. I missed the [0] at the end to make it works xD
The reason is that I thought that with this approach the query would be more efficient because it doesnt use a join, but if you tell me that with unnest it is more efficient i will continue to use it

6

I do want to highlight that there are cases when UNNEST isn’t ideal and this alternative solution may be better. A primary example is if you have a very source large object (and often the array elements are small relative to it). The implicit join may be inefficient when the payload is over-large so selecting just the field may be more efficient in those cases.

If the array is the primary contributor to the document size and/or you have numerous documents, amassing them all into memory (the ARRAY_AGG) may be problematic / inefficient. In cases like this, the ability for UNNEST’s results to be streamed through the remainder of the plan is advantageous.

In a nutshell I recommend you stick to UNNEST unless you have a specific case where UNNEST isn’t working out for you.

HTH.

1 Like
7

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.