Forums PostgreSQL.fr

coucou78187

Membre

Update de ligne avec champs de type jsonb

Bonjour à toutes et à tous,

je me penche actuellement sur le stockage au format JSON dont voici la table appellée referencement :

il s'agit de la version 11.1 de PostgreSQL

CHAMPS                          TYPE                                                 
num_matricule              varchar(18)                      not null 
valeur                          jsonb                                               
timestamp                    timestamp with time zone                               default_value:now()

PK: num_matricule

Je cherche à stocker un grand nombre de valeur (nom, poids, couleurs des cheveux, couleurs des yeux, age, nationalité, habitude alimentaire (vegan)...)
en gros à peu près 50 paramètres qui peuvent varier d'un individu à l'autre.

En tout et à terme, il y aurait environ 100 millions de lignes (num_matricule) avec son format json respectifs contenant toutes les caractéristiques.
Aussi, comme je trouve l'idée de mettre 50 colonnes sur une table (obèse) pas très correcte et que les caractéristiques sont très hétérogènes pour créer des tables relationnelles (PK, FK),
je me tourne vers le JSON, d'ailleurs j'aimerais si possible vous poser le question pour savoir si c'est une bonne idée de vouloir procéder ainsi?

déroulement des étapes:

- création de la table
- injection de 100 millions de lignes dans la colonnes num_matricule (1,2,3,4,5,6,7,8,9,..10000000) respectant le format varchar(18)

et là l'étape bloquante lorsque j'essaie d'UPDATER une ligne en lui ajoutant un document json comme suit avec jsonb_set :

UPDATE referencement
SET valeur = jsonb_set(valeur, '{nationalité}',  '"française"', TRUE)
WHERE num_matricule = '154698754522365412';
UPDATE

on dirait qu'il n'y a pas d'erreur de syntaxe mais lorsque je fait :

select * from referencement where num_matricule = '154698754522365412';  ==> aucune donnée dans la colonne "valeur"

Je me permets de vous solliciter pour connaitre le moyen d'updater une ligne en lui ajoutant un document json dans son champs jsonb sachant que la colonne jsonb était vide avant.

Je ne sais pas si je suis assez claire.

Aussi je voulais savoir s'il existait un moyen de peupler en masse un champs jsonb avec des document json aléatoires selon des valeurs?

je récapitule les points :

- j'aimerais si possible vous poser le question pour savoir si c'est une bonne idée de vouloir procéder ainsi?
- le moyen d'updater une ligne en lui ajoutant un document json dans son champs jsonb sachant que la colonne jsonb était vide avant?
- je voulais savoir s'il existait un moyen de peupler en masse un champs jsonb avec des document json aléatoires selon des valeurs?

Merci beaucoup pour votre aide et échanges

Cordialement,

rjuju

Administrateur

Re : Update de ligne avec champs de type jsonb

Bonjour,

jsonb_set sur un NULL renverra NULL.  Vous pouvez utiliser jsonb_set(COALESCE(valeur, '{}'), '{nationalité}',  '"française"', TRUE) par exemple.

Pour les autres questions, à votre place je créeerai des colonnes pour les champs nécessitant une contrainte "forte" (je suppose que vous ne pouvez pas avoir de données pour une personne n'ayant pas de nationalité ou de nom par exemple) que vous pourrez alors garantir au niveau du modèle de données.  Aussi, pourquoi utiliser un varchar pour stocker un identifiant numérique ?

---

Julien.
https://rjuju.github.io/

coucou78187

Membre

Re : Update de ligne avec champs de type jsonb

Bonjour rjuju,

Merci pour la réponse et la syntaxe de mise à jour qui a fonctionné pour une ligne

En fait pour être plus précis, il sera stocké des codes (entre 30 et 50 par matricule et de tailles variables)  très aléatoires (IP, MAC et autres différents) donc pas trop de contraintes fortes.

Concernant le varchar pour la colonne faisant office de PK, je l'ai utilisé car le modèle est le suivant AAAAAANNNNNNNNNNNN où "A" = caractère alphabétique (A-Z) et N = caractère numérique (0-9), peut-être est-ce une mauvaise idée ?...

C'est surtout le fait d'avoir des codes très nombreux par matricule qui m'ont fait pensé au stockage JSON pour ne pas avoir des colonnes très nombreuses  et se retrouver face à une table dite "obèse".

Comme il s'agit d'une phase de test de perf, c'est pour cela que je posais la question de savoir s'il existait un moyen de peupler en masse un champs jsonb avec des documents json aléatoires selon des valeurs (generate_series par exemple) pour pouvoir réaliser des tests sur postgreSQL pour voir si les performances étaient assurées

J'espère que ces infos apportent des indications utiles

Merci beaucoup rjuju!

rjuju

Administrateur

Re : Update de ligne avec champs de type jsonb

il sera stocké des codes [...]  très aléatoires (IP, MAC et autres différents) donc pas trop de contraintes fortes.

Quid des contraintes sur les données ?  Utiliser de vrais types vous permet de garantir que les données sont saines, et donc éviter de nombreux problèmes lors de l'exploitation de ces données.

Il n'est pas spécialement recommandé d'avoir des tables avec des milliers de colonnes, mais vous avez des alternatives possibles en fonctions de vos besoins.  Par exemple une partie en colonnes fixes, et un champ jsonb pour les données vraiment variables, ou une seconde tables pour stocker des données moins fréquemment accédées par exemple.

---

Julien.
https://rjuju.github.io/

coucou78187

Membre

Re : Update de ligne avec champs de type jsonb

Bonjour Julien,

Merci pour le retour,

En effet, j'ai positionné d'autre colonnes (date de création --> timestamp, ID --> PK) pour ne pas tout mettre dans la colonne JSONB.

Ce qui se passe est que cette application sera utilisée par différents clients avec des besoins (codes différents) donc il fallait un modèle qui puisse être transposable à chacun sans avoir a redévelopper à chaque fois.

Peut être que cette démarche est contre productive...

Mais c'est vrai que mettre des colonnes fixes pour des codes communs aux différents clients serait à étudier.