UNNEST with LET with Array functions (solved using: WITH, AS)

Couchbase Server
1

I have the following query:

select results

            let 

            bottom = (
                select identity, topic, title, SUBSTR(body,0,50) as body, date from mybucket
                use index(`idx_topic_dateDESC_t`)
                where 
                topic = 'test' and t = 'post' 
                and
                date <= '3099-10-15T04:46:12.2802304Z'
                order by date desc limit 2
            ),

            top = (
                select identity, topic, title, SUBSTR(body,0,50) as body, date from mybucket
                use index(`idx_topic_dateASC_t`)
                where 
                topic = 'test' and t = 'post' 
                and
                date >= '3099-10-15T04:46:12.2802304Z'
                order by date asc limit 2
            ),
            
            results = ARRAY_CONCAT( ARRAY_REVERSE(top), bottom );

Which returns this result:

[
    {
      "results": [
        {
          "body": "THE TRAGEDY OF HAMLET, PRINCE OF DENMARK THE TRAGE",
          "date": "3099-10-21T01:08:24.4444928Z",
          "identity": "192.168.1.195",
          "title": "THE TRAGEDY OF HAMLET, PRINCE OF DENMARK",
          "topic": "test"
        },
        {
          "body": "THE TRAGEDY OF HAMLET, PRINCE OF DENMARK THE TRAGE",
          "date": "3099-10-17T07:06:08.5177344Z",
          "identity": "192.168.1.195",
          "title": "THE TRAGEDY OF HAMLET, PRINCE OF DENMARK",
          "topic": "test"
        },
        {
          "body": "But never the offence. To bear all smooth and even",
          "date": "3099-10-14T16:58:55.2989952Z",
          "identity": "192.168.1.195",
          "title": "Th' oppressor's wrong, the proud man's contumely,",
          "topic": "test"
        },
        {
          "body": "THE TRAGEDY OF HAMLET, PRINCE OF DENMARK THE TRAGE",
          "date": "3099-10-10T14:00:20.8736384Z",
          "identity": "192.168.1.195",
          "title": "THE TRAGEDY OF HAMLET, PRINCE OF DENMARK",
          "topic": "test"
        }
      ]
    }
  ]

Is it possible to unnest it with n1ql to get this result:

[
    {
        "body": "THE TRAGEDY OF HAMLET, PRINCE OF DENMARK THE TRAGE",
        "date": "3099-10-21T01:08:24.4444928Z",
        "identity": "192.168.1.195",
        "title": "THE TRAGEDY OF HAMLET, PRINCE OF DENMARK",
        "topic": "test"
    },
    {
        "body": "THE TRAGEDY OF HAMLET, PRINCE OF DENMARK THE TRAGE",
        "date": "3099-10-17T07:06:08.5177344Z",
        "identity": "192.168.1.195",
        "title": "THE TRAGEDY OF HAMLET, PRINCE OF DENMARK",
        "topic": "test"
    },
    {
        "body": "But never the offence. To bear all smooth and even",
        "date": "3099-10-14T16:58:55.2989952Z",
        "identity": "192.168.1.195",
        "title": "Th' oppressor's wrong, the proud man's contumely,",
        "topic": "test"
    },
    {
        "body": "THE TRAGEDY OF HAMLET, PRINCE OF DENMARK THE TRAGE",
        "date": "3099-10-10T14:00:20.8736384Z",
        "identity": "192.168.1.195",
        "title": "THE TRAGEDY OF HAMLET, PRINCE OF DENMARK",
        "topic": "test"
    }
]
2 
WITH bottom AS (
                select identity, topic, title, SUBSTR(body,0,50) as body, date from mybucket
                use index(`idx_topic_dateDESC_t`)
                where
                topic = 'test' and t = 'post'
                and
                date <= '3099-10-15T04:46:12.2802304Z'
                order by date desc limit 2
            ),

     top AS (
                select identity, topic, title, SUBSTR(body,0,50) as body, date from mybucket
                use index(`idx_topic_dateASC_t`)
                where
                topic = 'test' and t = 'post'
                and
                date >= '3099-10-15T04:46:12.2802304Z'
                order by date asc limit 2
            )
SELECT RAW ub
FROM 1 AS d
UNNEST ARRAY_CONCAT( ARRAY_REVERSE(top), bottom ) AS ub;

Also see if it okay to UNION of two queries.

1 Like
3

@enko ,

Avoid with UNNEST

WITH bottom AS (
                select identity, topic, title, SUBSTR(body,0,50) as body, date from mybucket
                use index(`idx_topic_dateDESC_t`)
                where
                topic = 'test' and t = 'post'
                and
                date <= '3099-10-15T04:46:12.2802304Z'
                order by date desc limit 2
            ),

     top AS (
                select identity, topic, title, SUBSTR(body,0,50) as body, date from mybucket
                use index(`idx_topic_dateASC_t`)
                where
                topic = 'test' and t = 'post'
                and
                date >= '3099-10-15T04:46:12.2802304Z'
                order by date asc limit 2
            )
SELECT RAW ub
FROM ARRAY_CONCAT( ARRAY_REVERSE(top), bottom ) AS ub;
1 Like
4

@vsr1 thank you.
Union appears to disregard order mixing results at random.
This is very helpful… I really appreciate it. Learning new things.

5

@enko , ARRAY_REVERSE(top) makes UNION difficult to do.

1 Like