CODEWITHSUNDEEP
jsonpostgresql
Tech Marc
Tech Marc

Reputation: 3

How do you display a specific phrase your looking for from a string in POSTGRESQL

Here is the sample row value

CREATION_DATE: 2021-08-30
USER_EMAIL: '[email protected]
ACTION: 'FileAccessedExtended'
AUDITDATA: 
{
   "OrganizationId":"orgid1231231",
   "RecordType":6,
   "UserKey":"i:0h.f|membership|[email protected]",
   "UserType":0,
   "Version":1,
   "Workload":"SharePoint",
   "ClientIP":"11.11.11.11",
   "ObjectId":"https:\/\/someorganization.sharepoint.com\/sites\/MondayMeetings862\/Shared Documents\/folder\/folders\/somefolder\/Contact Database\/Investment Contact Database (MASTER) 1.xlsx",
   "UserId":"[email protected]",
   "CorrelationId":"12-031-23809123809172309",
   "EventSource":"SharePoint",
   "ItemType":"File",
   "ListId":"334b5378-2a4c-4ba6-b1ac-1ccb51a38687",
   "ListItemUniqueId":"a46a8f64-0ecf-415b-b372-32852c160d74",
   "Site":"2839182391823819238192389",
   "UserAgent":"MSOCS",
   "WebId":"1092301723729813",
   "HighPriorityMediaProcessing":false,
   "SourceFileExtension":"xlsx",
   "SiteUrl":"https:\/\/someorganization.sharepoint.com\/sites\/MondayMeetings862\/",
   "SourceFileName":"Investment Contact Database (MASTER) 1.xlsx",
   "SourceRelativeUrl":"Shared Documents\/folder\/folders\/COMMERCIAL\/Contact Database"
}

Desired output:

CREATION_DATE: 2021-08-30  
USER_EMAIL: [email protected]          
ACTION: File Access Extended                
CLIENTIP: 11.11.11.11

Upvotes: 0

Views: 70

Answers (2)

user330315
user330315

Reputation:

Assuming auditdata is a column of type jsonb (or at least json) you can use the ->> operator to extract a value from it:

select creation_date, user_email, action,
       auditdata ->> 'ClientIP' as client_ip
from the_table;

If for some reason, the column is defined properly as a jsonb (or at least json) you need to cast the value auditdata::jsonb ->> 'ClientIP'

Upvotes: 1

bfris
bfris

Reputation: 5825

Ok. Here come a bunch of if's. If AuditData is an array. And if ClientIP is reliably in the same position, then

SELECT AuditData[6] FROM MyTable

should give

"ClientIP":"11.11.11.11"

You can remove the double quotes by using the string function replace to replace double quote with nothing.

Upvotes: 0

Related Questions