Contexte

La base unique canutes expose les schémas assemblee, legifrance et senat. Cette recherche se fait avec query_sql, en interrogeant explicitement assemblee.acteurs et assemblee.organes. Le schéma assemblee stocke les acteurs et organes dans des tables avec une colonne data de type JSONB. Les mandats sont imbriqués dans data->'mandats' de chaque acteur, et chaque mandat contient un tableau organesRefs avec les UIDs des organes concernés.

Pour le pendant Sénat de cette recette, voyez Rechercher les sénateurs membres d'une commission du Sénat.

Il n'y a pas de table de jointure relationnelle entre acteurs et organes. La relation passe par le JSONB.

Schéma des tables

-- Table acteurs
-- uid (text PK), data (jsonb), depute_actif (boolean), nom_complet (text), slug (text)

-- Table organes
-- uid (text PK), data (jsonb)

Rechercher tous les acteurs ayant eu un mandat dans un organe donné

La requête utilise l'opérateur de containment JSONB @> pour chercher dans le tableau mandats un élément dont organesRefs contient l'UID de l'organe.

-- Exemple : trouver tous les acteurs de la Commission des affaires sociales (PO420120)
SELECT
  uid,
  data->'etatCivil'->'ident'->>'prenom' AS prenom,
  data->'etatCivil'->'ident'->>'nom' AS nom
FROM assemblee.acteurs
WHERE data->'mandats' @> '[{"organesRefs": ["PO420120"]}]'::jsonb;

Rechercher les membres actuels uniquement

Pour ne garder que les membres dont au moins un mandat dans cet organe est encore actif (pas de dateFin), il faut dénormaliser les mandats puis filtrer :

SELECT DISTINCT
  a.uid,
  a.data->'etatCivil'->'ident'->>'prenom' AS prenom,
  a.data->'etatCivil'->'ident'->>'nom' AS nom
FROM assemblee.acteurs a,
     jsonb_array_elements(a.data->'mandats') AS m
WHERE a.data->'mandats' @> '[{"organesRefs": ["PO420120"]}]'::jsonb
  AND m->'organesRefs' @> '"PO420120"'
  AND m->>'dateFin' IS NULL;

Rechercher les membres à une date donnée

Pour filtrer les membres actifs à une date précise, on vérifie que dateDebut <= date et que dateFin est null ou dateFin >= date :

SELECT DISTINCT
  a.uid,
  a.data->'etatCivil'->'ident'->>'prenom' AS prenom,
  a.data->'etatCivil'->'ident'->>'nom' AS nom
FROM assemblee.acteurs a,
     jsonb_array_elements(a.data->'mandats') AS m
WHERE a.data->'mandats' @> '[{"organesRefs": ["PO420120"]}]'::jsonb
  AND m->'organesRefs' @> '"PO420120"'
  AND (m->>'dateDebut')::date <= '2024-01-15'
  AND (m->>'dateFin' IS NULL OR (m->>'dateFin')::date >= '2024-01-15');

Avec la bibliothèque postgres (postgresjs) en TypeScript

Attention : avec la bibliothèque postgres (postgresjs), il faut utiliser db.json() pour passer des valeurs JSONB dans les template literals. Ne jamais utiliser JSON.stringify(...) + ::jsonb car les paramètres sont envoyés comme du texte et le cast ne fonctionne pas correctement (la requête retourne 0 résultat sans erreur).

// ✅ Correct : utiliser db.json()
const acteursRows = await canutesDb`
  SELECT data
  FROM assemblee.acteurs
  WHERE data->'mandats' @> ${canutesDb.json([{ organesRefs: [uid] }])}
`

// ❌ Incorrect : JSON.stringify avec cast ::jsonb (retourne 0 résultats)
const acteursRows = await canutesDb`
  SELECT data
  FROM assemblee.acteurs
  WHERE data->'mandats' @> ${JSON.stringify([{ organesRefs: [uid] }])}::jsonb
`

Filtrage côté client après la requête

Une fois les acteurs récupérés, leurs mandats sont filtrés en JavaScript pour ne garder que ceux liés à l'organe :

const membres = acteursRows.map((row) => {
  const acteur = row.data
  const mandats = (acteur.mandats || []).filter((m) =>
    (m.organesRefs || []).includes(uid),
  )
  return { acteur, mandats }
})

Pour filtrer les mandats actifs à une date donnée (format YYYY-MM-DD) côté client :

function isMandatActiveAt(mandat: Mandat, dateStr: string): boolean {
  const debut = mandat.dateDebut?.toISOString().slice(0, 10) ?? null
  const fin = mandat.dateFin?.toISOString().slice(0, 10) ?? null
  if (debut && debut > dateStr) return false
  if (fin && fin < dateStr) return false
  return true
}