Subquery with array_agg

SQL++
1

Hi

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.

I appreciate every help

2

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;
3

Thank you! The first one is perfect and the second even better :slight_smile: