Discussion :

[nazzguhl]

Bonjour,

je connais bien SLQ dans SQL-Server ou MySQL mais moins dans PostGreSQL.
je dois faire une recherche dans la table access_log d'une base qui enregistre les logs de connexion d'un site et autres actions sur ce site, la table est simple, 8 champs :

• un champ ID Pk au format Bigint
• un champ acces_date au format date
• un champ user_action au format Text
• les champs suivants sont inutiles à mon problème

Pour le contenu du champ user_action, il contient un json différent suivant le type d'action, exemples :

• {"action":"authentification','user":"xxxxxxx","compte":"123456789"}
• {"action":"page","pagenum":"1","compte":"123456789"}
• {"action":"tempspage","pagenum":"1","dtDeb":"2024-07-12 13:13:45.122","dtFin":"2024-07-12 13:13:57.459","compte":"123456789"}
• {"action":"download","Filename":"nomdoc.xxx","size":"256745","compte":"123456789"}

je souhaite récupérer toutes les lignes user_action de download, il me semblait que la requête serait :
SELECT access_date, user_action FROM access_log WHERE user_action->"action"="download" => j'ai une erreur : ERROR: column "action" does not exist
si j'écrit la requête avec de simple guillemet :
SELECT access_date, user_action FROM access_log WHERE user_action->'action'="download" => j'ai une erreur : ERROR: operator does not exist: text -> unknown
si j'écrit la requête sans guillemet :
SELECT access_date, user_action FROM access_log WHERE user_action->action="download" => j'ai une erreur : ERROR: column action does not exist

Donc je suppose que j'écris mal la requête mais je n'arrive pas à trouver la bonne syntaxe, si quelqu'un peut m'aider ...
Pour info je suis sous PgAdmin 4 et je n'ai aucune idée de la version de PostGres

Merci d'avance
Alain

[escartefigue]

bonjour,

Tout d'abord, il ne faut pas parler de "champs" mais de "colonnes" : les champs sont des zones de formulaires ou d'états.

Ensuite, la syntaxe à utiliser est tout simplement celle-ci :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT access_date
     , user_action 
FROM access_log 
WHERE user_action = 'download'
;

Il ne faut pas confondre les quotes simples et les doubles quotes.
Les simples servent à délimiter les chaînes de caractères.
Les doubles servent à délimiter les noms d'objets quand ceux-ci contiennent des caractères spéciaux ou sont des mots réservés SQL.

[nazzguhl]

Bonjour,

je viens d'essayer SELECT access_date, user_action FROM access_log WHERE user_action='download'
=> effectivement je n'ai plus d'erreur, mais la requête renvoi 0 ligne alors que lorsque je liste les 100 dernières ligne j'ai bien une ligne contenant :
user_action = {"action":"download","Filename":"nomdoc.xxx","size":"256745","compte":"123456789"}

Mais j'avais déjà testé avec double cote et simple cote, mais je n'avais pas affiché toutes les tentatives ...

Cette solution ne fonctionne pas
Merci quand même

Alain

[ced]

Bonjour,

Deux questions pour pouvoir mieux répondre à votre problème :

1. La colonne qui contient le JSON est de quel type ? TEXT, JSON ou JSONB ? Je parie pour TEXT... ce qui n'est pas bien vu que pour du JSON, il existe le type JSON et surtout JSONB...
2. Quelle est la version de PostgreSQL que vous utilisez ?

ced

[nazzguhl]

Bonsoir,
la colonne a été créé comme indiqué dans ma première note en TEXT et bien sûr je ne peux rien modifier à la base ...
la version de PostgreSQL est : "PostgreSQL 9.6.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit"

j'ai trouvé un fonctionnement qui permet un select et ce n'est pas beau du tout :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
select replace(replace(replace(replace(user_action,':','=>'),'"',''),'{',''),'}','') as action_user
from access_log limit 5;

ce qui transforme par exemple : {"action":"download","Filename":"nomdoc.xxx","size":"256745","compte":"123456789"} en action=>download,Filename=>nomdoc.xxx,size=>256745,compte=>123456789 et donc en transformant la requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
select replace(replace(replace(replace(user_action,':','=>'),'"',''),'{',''),'}','')::hstore->'ACTION' as cmd
from access_log limit 5;

je récupère bien dans cmd : authentification ou page ou download

c'est très moche mais là le json est interprété, par contre ça part vite en vrille avec une ligne ayant en user_action : {"action":"tempspage","pagenum":"1","dtDeb":"2024-07-12 13:13:45.122","dtFin":"2024-07-12 13:13:57.459","compte":"123456789"} qui se transforme en : action=>tempspage,pagenum=>1,dtDeb=>2024-07-12 13=>13=>45.122,dtFin=>2024-07-12 13=>13=>57.459,compte=>123456789
... il y a des ":" dans les dates et la requête plante => je peux lire les 12 premières ligne et ça plante à la 13ème

j'ai essayé de bidouiller les replace pour arriver à gérer les ":" des dates => ça a fini par fonctionner mais je n'ose même pas la copier tellement c'est moche ... mais ça fonctionne pour sélectionner une clé du dictionnaire json, par contre si je mets dans le where pour ne sélectionner que les lignes ayant pour action='download' là ça ne fonctionne pas... j'en déduis que ce n'est pas la bonne manière de gérer la valeur json...

j'aurais aussi la solution de lire toute la table et d'analyser chaque json ligne par ligne par programme mais là il faut lire 6983460 lignes

Merci
Alain

[skuatamad]

Qu'est-ce que ça donne comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
with t as (
select user_action, cast(user_action::json->'action' as varchar) as action 
  from access_log
)
select * 
  from t
 where action = '"download"';

[ced]

Ah oui, la version 9.6, ça date et il n'y avait pas le JSONB dedans (donc pas indexable et donc des perfs qui vont vite être désastreuses, surtout pour parser des logs qui peuvent amener beaucoup de volumétrie...).
Sans parler du fait que la version en question n'est plus maintenue depuis belle lurette...

En attendant (ou en espérant) mieux, voici déjà une requête qui devrait vous aider (je n'ai pas de 9.6 sous la main, mais à la lecture de la documentation, ça devrait le faire) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT access_date, user_action 
FROM access_log 
WHERE user_action::json->>'action'='download';

À tester et adapter au besoin.

Bon courage,

ced