Limiting data in NEST expression

SQL++
,
1

Hello,

I am trying to pass a LIMIT and an offset to a NEST on document keys.

Let’s say I have the following “user_account::1” document:

{
  "interests": [
    "interest::7"
  ],
  "_id": 1,
  "_type": "user_account",
  "created_at": 1472741410
}

And various interests documents, like the following “interest::7” that I am joining in “user_account::1” document:

{
  "name": "Interest 7 for example",
  "_id": 7,
  "_type": "user_interest",
  "created_at": 1472741410
}

I have tried the following approach, however it did not gave me any result back:

SELECT interestsDetails FROM default
NEST default interestsDetails ON KEYS default.interests[0:20]

WHERE default._type = "user_account" AND
      default._id = 1
ORDER BY default.created_at DESC
      
LIMIT 1

Supposedly, it should get a range starting in OFFSET 0 and LIMIT 20.

In the other hand, the query without the range works perfectly, however I am not really able to OFFSET/LIMIT it.

SELECT interestsDetails FROM default
NEST default interestsDetails ON KEYS default.interests

WHERE default._type = "user_account" AND
      default._id = 1
ORDER BY default.created_at DESC
      
LIMIT 1

How can I approach a similar thing here?

Thanks

2

It looks like our array slice operator is too stringent. Try the following:

SELECT interestsDetails FROM default d
NEST default interestsDetails ON KEYS d.interests[ 0 : LEAST( 20, ARRAY_LENGTH( d.interests ) ) ]
;

If you are selecting a single user, the ORDER BY and LIMIT will have no effect. You should also look at the USE KEYS clause for selecting a single user by primary key.

3

Thanks a lot @geraldss.

I ended up having my query like:

SELECT interestsDetails FROM default d
USE KEYS "user_account::X"
NEST default interestsDetails ON KEYS d.favorite_interests[ 0 : LEAST( 20, ARRAY_LENGTH( d.favorite_interests ) ) ]

Thanks a lot!

4

@geraldss from what I’ve observed the 0 in the interests does not really work like an “offset”. I need to always keep it at least to 1 to get any result back.

Let’s say I do want to get the second result. In an usual query I would say OFFSET 1 LIMIT 1. Doing that here would give me no results.

What would be the correct way to use it as such filters?

5

This works for me. Can you try this example.

cbq> select [1, 2, 3][1:2];
{
    "requestID": "481b5d5c-42c4-426b-9ba7-672e325349c0",
    "signature": {
        "$1": "array"
    },
    "results": [
        {
            "$1": [
                2
            ]
        }
    ],
    "status": "success",
6

Forget it. Figured it out.

For giving me the results I wanted, I need to sum the offset plus the limit in the “limit” field, so I could get the result I want.

Thanks!