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
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 ?
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");
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
[
{
“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;”
}
]
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;
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”);