KMeans DBScan nested group by to json.

cluster_2.sql

SELECT
    SUM(count)::integer count,
    JSON_Agg(JSON_Build_Object(cat, count)) cat,
    ST_AsGeoJson(ST_PointOnSurface(ST_Union(geom))) geomj
  FROM (
    SELECT
      COUNT(cat) count,
      ST_Union(geom) geom,
      cat,
      kmeans_cid,
      dbscan_cid
    FROM (
      SELECT
        cat,
        kmeans_cid,
        geom AS geom,
        ST_ClusterDBSCAN(geom, 0.1, 1) OVER (PARTITION BY kmeans_cid) dbscan_cid
      FROM (
        SELECT
          brand AS cat,
          ST_ClusterKMeans(geom, 10) OVER () kmeans_cid,
          geom
        FROM gb_retailpoint
      ) kmeans
    ) dbscan GROUP BY kmeans_cid, dbscan_cid, cat
  ) cluster GROUP BY kmeans_cid, dbscan_cid;