WITH patterns AS ( SELECT jsonb_array_elements_text(patterns) AS pattern FROM cp_patterns_cache WHERE id = 1 ), matched AS ( SELECT DISTINCT k.key, k.updated_at, COALESCE(k.data->'metadata'->>'title', k.data->>'document_id', k.key) AS title, COALESCE(k.data->>'document_id', replace(k.key, '', '/')) AS document_id, CASE WHEN k.key LIKE 'knowledge__dev__laws%' THEN 'laws' WHEN k.key LIKE 'knowledge__dev__ssot_%' THEN 'ssot' WHEN k.key LIKE 'knowledge__dev__architecture__%' THEN 'architecture' ELSE 'other' END AS doc_family FROM kb_documents k JOIN patterns p ON k.key LIKE p.pattern WHERE (k.data->>'deleted_at') IS NULL ) SELECT (SELECT COALESCE( jsonb_agg(jsonb_build_object('pattern', pattern) ORDER BY pattern), '[]'::jsonb ) FROM patterns) AS source_patterns, (SELECT COALESCE( jsonb_agg( jsonb_build_object( 'title', title, 'document_id', document_id, 'key', key, 'doc_family', doc_family, 'updated_at', to_char(updated_at, 'YYYY-MM-DD"T"HH24:MI:SSOF') ) ORDER BY doc_family, key ), '[]'::jsonb ) FROM matched) AS documents, (SELECT COALESCE( jsonb_agg( jsonb_build_object('doc_family', doc_family, 'doc_count', doc_count) ORDER BY doc_count DESC, doc_family ), '[]'::jsonb ) FROM (SELECT doc_family, count(*) AS doc_count FROM matched GROUP BY doc_family) f ) AS family_counts