i am new to couch and a did not find my requirement in the tutorial. I try to create a dupe check. So i tried it with my sql knowledge
select array_agg(matnr) as matnr from article where `meta`.mandant = "bt" and form = "Artikel" group by matnr having array_length(array_agg(matnr)) > 1
This is getting my dupes
[
{
"matnr": [
"128507",
"128507"
]
}
]
After that i tried to create a subquery with that.
SELECT main.'meta`.unid, main.matnr
FROM stammdaten main
WHERE (((main.`meta`.`mandant`) = "bt") and (main.`form` = "Artikel"))
and main.matnr in (
select array_agg(matnr) from stammdaten where `meta`.mandant = "bt" and form = "Artikel" group by matnr letting matnrs = array_agg(matnr) having array_length(matnrs) > 1)
And after that i got nothing. I know the problem is that the array_agg is using an object with an array within but i do not know how to access it.
ARRAY_AGG() gives ARRAY. If used in SELECT projection it is OBJECT of ARRAY. If used in Subquery result then it is ARRAY of OBJECT of ARRAY. It will not match for IN clause.
SELECT m.`meta`.unid, m.matnr
FROM stammdaten AS m
WHERE m.`meta`.`mandant` = "bt" AND m.`form` = "Artikel"
AND m.matnr IN (SELECT RAW matnr
FROM stammdaten
WHERE `meta`.mandant = "bt" AND form = "Artikel"
GROUP BY matnr
HAVING COUNT(1) > 1);
OR
With single query representation different (each matnr, it gives unids array for duplicates). Avoid scan twice
SELECT m.matnr, ARRAY_AGG(m.`meta`.unid) AS unids
FROM stammdaten AS m
WHERE m.`meta`.mandant = "bt" AND m.form = "Artikel"
GROUP BY m.matnr
HAVING COUNT(1) > 1);
If you need the other representation
SELECT m1.matnr, unid
FROM (SELECT m.matnr, ARRAY_AGG(m.`meta`.unid) AS unids
FROM stammdaten AS m
WHERE m.`meta`.mandant = "bt" AND m.form = "Artikel"
GROUP BY m.matnr
HAVING COUNT(1) > 1) AS m1
UNNEST m1.unids AS unid;