How do you pass query as parameter for ARRAY_SYMDIFF function

Couchbase Server
1

Hi ,

I have type of documents where I need to spit out the difference between the documents and only output the fields which are changed . How do I make use of array_diff function ? I would love to have select query as arguments but its throwing error and I just need to figure out why ?
SELECT ARRAY_SYMDIFF(select favoriteGenres from couchmusic4-mini where type = “userprofile”) as diff_array

[
{
“code”: 3000,
“msg”: “syntax error - at select”,
“query”: “SELECT ARRAY_SYMDIFF(select favoriteGenres from couchmusic4-mini where type = “userprofile”) as diff_array”
}
]

I really would appreciate your quick reply on this
K

2

Add another () around subquery and ARRAY_SYMDIFF needs at least 2 arguments

Should give u distinct once

SELECT
            DISTINCT favoriteGenres 
from  `couchmusic4-mini`  
where type = “userprofile”
3

Thank you @vsr1 . My issue is that I want to compare the output ( multiuple JSON doc) from query output -

select favoriteGenres from couchmusic4-mini where type = “userprofile”. returns the favorite genres for all ids. Favoritegenre is array . I want to compare all the returned arrays to each other . Is it even possible ?

4

Hi @nsprollc.com

WITH is available in CB 6.5 and above (WITH query will not take account value is already compared, n*n)

WITH  fvs AS ( SELECT META().id,  favoriteGenres AS fg
               FROM  `couchmusic4-mini`
               WHERE type = "userprofile")
SELECT  f1.id, f2.id AS f2id, ARRAY_SYMDIFF(f1.fg,f2.fg)
FROM fvs AS f1 UNNEST fvs AS f2
WHERE f1.id != f2.id;

OR

SELECT ARRAY_FLATTEN(ARRAY  (ARRAY {f1.id, "id2":f2.id, "diff": ARRAY_SYMDIFF(f1.fg,f2.fg)  FOR pos2:f2 IN fvs WHEN pos2 > pos1 END) FOR pos1:f1 IN fvs END),1) AS symdiff
LET fvs =  ( SELECT META().id,  favoriteGenres AS fg
             FROM  `couchmusic4-mini`
             WHERE type = "userprofile");
5

Hi @vsr1

Thank you for your reply. but i do get following error on this . I dont think I should get the error .

WITH fvs AS ( SELECT META().id, favoriteGenres AS fg
FROM couchmusic4-mini
WHERE type = “userprofile”)
SELECT f1.id, f2.id, ARRAY_SYMDIFF(f1.fg,f2.fg)
FROM fvs AS f1 UNNEST fvs AS f2
WHERE f1.id != f2.id;

I do get syntax error on this query :slight_smile:

[
{
“code”: 3000,
"msg": “Duplicate result alias id.”,
“query”: “WITH fvs AS ( SELECT META().id, favoriteGenres AS fg\n FROM couchmusic4-mini\n WHERE type = “userprofile”)\nSELECT f1.id, f2.id, ARRAY_SYMDIFF(f1.fg,f2.fg)\nFROM fvs AS f1 UNNEST fvs AS f2\nWHERE f1.id != f2.id;”
}
]

6 
WITH  fvs AS ( SELECT META().id,  favoriteGenres AS fg
               FROM  `couchmusic4-mini`
               WHERE type = "userprofile")
SELECT  f1.id, f2.id AS f2id, ARRAY_SYMDIFF(f1.fg,f2.fg)
FROM fvs AS f1 UNNEST fvs AS f2
WHERE f1.id != f2.id;
7

Thank you . that works but the results are union of both arrays rather than differnece.

8

WHat should be pos1 and pos2 in this query : SELECT ARRAY_FLATTEN(ARRAY (ARRAY {f1.id, “id2”:f2.id, “diff”: ARRAY_SYMDIFF(f1.fg,f2.fg) FOR pos2:f2 IN fvs WHEN pos2 > pos1 END) FOR pos1:f1 IN fvs END),1) AS symdiff
LET fvs = ( SELECT META().id, favoriteGenres AS fg
FROM couchmusic4-mini
WHERE type = “userprofile”);

9

FOR pos2:f2 IN fvs
f2 value of fvs, pos2 is index position of f2 in fvs

10

checkout ARRAY_EXCEPT() also. It will not symmetry

11

Thank you @vsr1. You know that helped.