2023-04-26

PostgreSQL select data in JSON format

SELECT audit_log.id,
    audit_log.event_time,
    audit_log.client_addr,
    audit_log.application_name,
    audit_log.username,
    audit_log.table_name,
    audit_log.operation,
    audit_log.after_value -> 'exterior'::text AS after_value_in_json
   FROM audit.audit_log
  WHERE 1 = 1 
  AND (audit_log.after_value ->> 'exterior'::text) ~~ '%sCrIpT%'::text
  ORDER BY audit_log.event_time DESC;
/
  
SELECT 
id,
event_time,
client_addr,
application_name,
username,
table_name,
operation,
after_value -> 'exterior'as after_value_in_JSON
FROM audit.audit_log
where 1=1
and after_value ->> 'exterior' like '%sCrIpT%'
LIMIT 20;
/

No comments:

Post a Comment