PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 02/06/2021 15:58:10

jm130794
Membre

Requête sur une colonne de type JSONB

Bonjour,

J'ai une question sur l'utilisation de requêtes avec des colonnes de type jsonb.

J'ai une table de personnes nommée personne. Partons sur 3 colonnes :
id (clé primaire)
nom
historique

La colonne historique est de type jsonb. Voici un exemple de contenu :

[
    {
        "annee": 2014,
        "equipes": [
            {
                "id": 22,
                "nom": "Equipe 1"
            }
        ],
        "departements": [
            {
                "id": 53,
                "nom": "Département 1"
            }
        ]
    },
    {
        "annee": 2015,
        "equipes": [
            {
                "id": 23,
                "nom": "Equipe 5"
            },
            {
                "id": 22,
                "nom": "Equipe 1"
            }
        ],
        "departements": [
            {
                "id": 53,
                "nom": "Département 1"
            },
            {
                "id": 56
                "nom": "Département 3"
            }
        ]
    }
]
   
Comment faire pour récupérer toutes les personnes ayant travaillé dans le département 1 ?

Merci,

Jean-Marc

Hors ligne

#2 02/06/2021 17:42:12

rjuju
Administrateur

Re : Requête sur une colonne de type JSONB

jm130794 a écrit :

Voici un exemple de contenu :

[
    {
        "annee": 2014,
        "equipes": [
            {
                "id": 22,
                "nom": "Equipe 1"
            }
        ],
        "departements": [
            {
                "id": 53,
                "nom": "Département 1"
            }
        ]
    },
    {
        "annee": 2015,
        "equipes": [
            {
                "id": 23,
                "nom": "Equipe 5"
            },
            {
                "id": 22,
                "nom": "Equipe 1"
            }
        ],
        "departements": [
            {
                "id": 53,
                "nom": "Département 1"
            },
            {
                "id": 56
                "nom": "Département 3"
            }
        ]
    }
]

Tout d'abord ce json est invalide, mais passons.


Comment faire pour récupérer toutes les personnes ayant travaillé dans le département 1 ?

Vous voulez probablement dire "Département 1" ?  Ou peut etre le département avec l'id 53 ?  Ou les deux ?


Il est possible de récupérer l'information que vous voulez avec ce type de json, mais :

- la syntaxe n'est vraiment pas agréable
- cela risque d'être lent (surtout avec postgres 11 et antérieur)
- garder un historique en json va rendre les modifications plus lentes et probablement nécessiter plus d'espace disque

Mais le pire, c'est que vous avez des données parfaitement structurées, mais en les stockant en json vous perdez toute garantie de cohérence.  Rien ne garantis que le "Département 1" a bien l'id 53 et inversement, rien ne garantis qu'une mise à jour d'un nom soit propagée etc etc.

Je vous conseillerais donc de stocker l'historique sous forme relationnelle.

Hors ligne

#3 02/06/2021 19:15:26

jm130794
Membre

Re : Requête sur une colonne de type JSONB

Concernant la première remarque sur le format JSON invalide, j'ai effectivement fait une erreur lors de la mise en forme "à la main"... en supprimant une virgule.
J'imagine que cette erreur ne remet pas en cause la pertinence de ma question ;-)

Pour la seconde remarque, vous avez parfaitement raison. Il faut néanmoins comprendre que ces données JSON sont issues d'une application RH et réceptionnées en utilisant
le protocole AMQP. J'ai besoin de ces données pour certaines requêtes ponctuelles dans une autre application indépendante de l'application RH et transformer ces données
sous une forme relationnelle n'aura aucune valeur ajoutée dans mon contexte.

Je repose donc ma question en précisant que c'est bien le nom = "Département 1".

Jean-Marc

Hors ligne

#4 02/06/2021 19:23:24

rjuju
Administrateur

Re : Requête sur une colonne de type JSONB

Aucune valeur ajoutée à part valider la cohérence des données reçues et simplifier les requêtes, mais soit.


Vous pouvez consulter https://www.postgresql.org/docs/current … -json.html pour votre besoin.

Hors ligne

Pied de page des forums