Multiple UNNEST on N1QL having multiple joins

SQL++
,
1

Hi All,

I have the following document

 [
  {
    "document": {
      "request": "Primary",
      "results": {
        "bit1": [
          10.959,
          11.802,
          11.194,
          15.374
        ],
        "bit2": [
          19.403,
          20.118,
          20.589
        ],
        "bit3": [
          1602597900000,
          1602597960000,
          1602598020000
        ]
      },
      "load_time": "1602684260",
      "docType": "docType"
    }
  },
  {
    "document": {
      "request": "Primary",
      "results": {
        "bit1": [
          12.959,
          10.802,
          09.194
        ],
        "bit2": [
          11.003,
          11.988,
          12.089
        ],
        "bit3": [
          1602597900000,
          1602597960000,
          1602598020000
        ]
      },
      "load_time": "1602684263",
      "docType": "docType"
    }
  }

I am trying to unnest the document and I am using the following query but this query is doing multi joins.

SELECT
bit1,
bit2,
bit3
FROM
document DC
UNNEST DC.results.bit1 as bitr1
UNNEST DC.results.bit2 as bitr2
UNNEST DC.results.bit3 as bitr3
WHERE
DC.docType=‘docType’

This query works perfectly but I see null when I try to convert bit3 from MILLIS TO STR.

SELECT
DC.results.bit1 as bitr1,
DC.results.bit2 as bitr2,
MILLIS_TO_STR(TO_NUMBER(DC.results.bit3)) as bitr3
FROM
document DC
WHERE
DC.docType=‘docType’

NOTE:
bit1,bit2, and bit3 are coming as a string so in my second query, I am converting it to numbers and then to DateTime datatype.

1 Like
2

UNNEST means self JOIN of original document with UNNEST array.
bit3 is aaray TO_NUMBER needs string or number.

SELECT
DC.results.bit1 as bitr1,
DC.results.bit2 as bitr2,
ARRAY MILLIS_TO_STR(TO_NUMBER(v)) FOR v IN  DC.results.bit2 END AS  bitr3
FROM
document DC
WHERE
DC.docType=‘docType’
1 Like
3

@vsr1 Thanks for the response, the solution partially resolved the issue for me. When I try the above solution I am still seeing the result in Array format, I am not able to flatten it get the result in table format.

image
image1979×483 30 KB

What I am expecting is the following result, where it is flattened.

bitr1           ||          bitr2           ||              bitr3
10.959          ||          19.403          ||        2020-10-13T14:05:00Z
11.802          ||         20.118           ||        2020-10-13T14:06:00Z
11.194          ||         22.381           ||        2020-10-13T14:07:00Z
1 Like
4

If you need that you must use UNNEST

SELECT
bitr1,
 bitr2,
MILLIS_TO_STR(TO_NUMBER(bitr3))  AS  bitr3
FROM document  AS d
UNNEST d.results.bit1 AS bitr1
UNNEST d.results.bit2 AS bitr2
UNNEST d.results.bit3 AS bitr3
WHERE  d.docType="docType"
5

@vsr1 Yes, this is exactly what I did in the beginning before raising this issue in the community. I am getting the result in table format but it’s doing the cross join. The output looks as below.

bitr1           ||          bitr2           ||              bitr3
10.959          ||          19.403          ||        2020-10-13T14:05:00Z
10.959          ||          19.403          ||        2020-10-13T14:06:00Z
10.959          ||          19.403          ||        2020-10-13T14:07:00Z
11.802          ||          19.403          ||        2020-10-13T14:05:00Z
11.802          ||          19.403          ||        2020-10-13T14:06:00Z
11.802          ||          19.403          ||        2020-10-13T14:07:00Z
11.194          ||          19.403          ||        2020-10-13T14:05:00Z 
11.194          ||          19.403          ||        2020-10-13T14:06:00Z 
11.194          ||          19.403          ||        2020-10-13T14:07:00Z 
10.959          ||          20.118          ||        2020-10-13T14:05:00Z
10.959          ||          20.118          ||        2020-10-13T14:06:00Z
10.959          ||          20.118          ||        2020-10-13T14:07:00Z
11.802          ||          20.118          ||        2020-10-13T14:05:00Z
11.802          ||          20.118          ||        2020-10-13T14:06:00Z
11.802          ||          20.118          ||        2020-10-13T14:07:00Z
    ....        ||           ....           ||                     ....
6

It will be cross JOIN. If you don’t want cross JOIN w use WHERE clause join or filter

7

Thanks, I am new to couchbase so I don’t know how to solve it on couchbase, it would be helpful if you can provide me a syntax or taking the above query as an example.

8

If you have multiple arrays and do self join you get cross JOIN. In this case no join key.

Only thing you could do is eliminate duplicates.

SELECT DISTINCT
bitr1,
 bitr2,
MILLIS_TO_STR(TO_NUMBER(bitr3))  AS  bitr3
FROM document  AS d
UNNEST d.results.bit1 AS bitr1
UNNEST d.results.bit2 AS bitr2
UNNEST d.results.bit3 AS bitr3
WHERE  d.docType="docType"

If you want data matched by position.

SELECT u.*
    FROM document  AS d
    UNNEST  (ARRAY {bitr1, bitr2, "bitr3": MILLIS_TO_STR(TO_NUMBER(bitr3)) }
             FOR bitr1, bitr2, bitr3 IN d.results.bit1, d.results.bit2, d.results.bit3 END) AS u
    WHERE  d.docType="docType" ;

If you want change data model to use ARRAY of objects vs multiple arrays.

9

is there any way to have these same in spring data couchbase jpa ?

10

With @Query in spring data, your n1ql statement can be anything.