如何查询sql字符串中的子字符串

Question

我有日志sql字符串，我想查询其中的一个子字符串。字符串的形式与此类似：

2014-04-16 00:01:31-0400,583 {"Items":[{"EventName":"REDISTRIBUTION","PubEndDate":"2014/04/04","HistoryId":30330244,"AlertId":311116,"ItmId":"1515998685","SourceType":"Trade Journals","ReasonCode":"","Origin":"Alert","MyResearchUser":"610165","ProjectCode":"","PublicationCode":"0FQN","PubStartDate":"2014/04/04","ItmFrmt":"ResultItem","Subrole":"ResultItem","PaymentType":"PrePaid","NumberOfCopies":1,"UsageInfo":"P-1008254-158123-CUSTOMER-null-3667449","Role":"ResultItem","RetailPrice":0.39,"EffectivePrice":0,"Multiplier":1,"ParentItemId":"186168"},{"EventName":"REDISTRIBUTION","PubEndDate":"2014/06/30","HistoryId":30330244,"AlertId":311116,"ItmId":"1515993519","SourceType":"Trade Journals","ReasonCode":"","Origin":"Alert","MyResearchUser":"610165","ProjectCode":"","PublicationCode":"3GGY","PubStartDate":"2014/04/01","ItmFrmt":"ResultItem","Subrole":"ResultItem","PaymentType":"PrePaid","NumberOfCopies":1,"UsageInfo":"P-1008254-158123-CUSTOMER-null-3667449","Role":"ResultItem","RetailPrice":0.39,"EffectivePrice":0,"Multiplier":1,"ParentItemId":"186168"},{"EventName":"REDISTRIBUTION","PubEndDate":"2014/06/30","HistoryId":30330244,"AlertId":311116,"ItmId":"1515993511","SourceType":"Trade Journals","ReasonCode":"","Origin":"Alert","MyResearchUser":"610165","ProjectCode":"","PublicationCode":"3GGY","PubStartDate":"2014/04/01","ItmFrmt":"ResultItem","Subrole":"ResultItem","PaymentType":"PrePaid","NumberOfCopies":1,"UsageInfo":"P-1008254-158123-CUSTOMER-null-3667449","Role":"ResultItem","RetailPrice":0.39,"EffectivePrice":0,"Multiplier":1,"ParentItemId":"186168"},{"EventName":"REDISTRIBUTION","PubEndDate":"2014/03/31","HistoryId":30330244,"AlertId":311116,"ItmId":"1516002290","SourceType":"Trade Journals","ReasonCode":"","Origin":"Alert","MyResearchUser":"610165","ProjectCode":"","PublicationCode":"0EUH","PubStartDate":"2014/03/31","ItmFrmt":"ResultItem","Subrole":"ResultItem","PaymentType":"PrePaid","NumberOfCopies":1,"UsageInfo":"P-1008254-158123-CUSTOMER-null-3667449","Role":"ResultItem","RetailPrice":0.39,"EffectivePrice":0,"Multiplier":1,"ParentItemId":"186168"},{"EventName":"REDISTRIBUTION","PubEndDate":"2014/02/28","HistoryId":30330244,"AlertId":311116,"ItmId":"1515999310","SourceType":"Trade Journals","ReasonCode":"","Origin":"Alert","MyResearchUser":"610165","ProjectCode":"","PublicationCode":"1ZEC","PubStartDate":"2014/02/01","ItmFrmt":"ResultItem","Subrole":"ResultItem","PaymentType":"PrePaid","NumberOfCopies":1,"UsageInfo":"P-1008254-158123-CUSTOMER-null-3667449","Role":"ResultItem","RetailPrice":0.39,"EffectivePrice":0,"Multiplier":1,"ParentItemId":"186168"},{"EventName":"REDISTRIBUTION","PubEndDate":"2014/01/31","HistoryId":30330244,"AlertId":311116,"ItmId":"1515993424","SourceType":"Trade Journals","ReasonCode":"","Origin":"Alert","MyResearchUser":"610165","ProjectCode":"","PublicationCode":"5AMP","PubStartDate":"2014/01/01","ItmFrmt":"ResultItem","Subrole":"ResultItem","PaymentType":"PrePaid","NumberOfCopies":1,"UsageInfo":"P-1008254-158123-CUSTOMER-null-3667449","Role":"ResultItem","RetailPrice":0.39,"EffectivePrice":0,"Multiplier":1,"ParentItemId":"186168"},{"EventName":"REDISTRIBUTION","PubEndDate":"2014/01/31","HistoryId":30330244,"AlertId":311116,"ItmId":"1515993462","SourceType":"Trade Journals","ReasonCode":"","Origin":"Alert","MyResearchUser":"610165","ProjectCode":"","PublicationCode":"5AMP","PubStartDate":"2014/01/01","ItmFrmt":"ResultItem","Subrole":"ResultItem","PaymentType":"PrePaid","NumberOfCopies":1,"UsageInfo":"P-1008254-158123-CUSTOMER-null-3667449","Role":"ResultItem","RetailPrice":0.39,"EffectivePrice":0,"Multiplier":1,"ParentItemId":"186168"},{"EventName":"REDISTRIBUTION","PubEndDate":"2014/01/31","HistoryId":30330244,"AlertId":311116,"ItmId":"1515993356","SourceType":"Trade Journals","ReasonCode":"","Origin":"Alert","MyResearchUser":"610165","ProjectCode":"","PublicationCode":"5AMP","PubStartDate":"2014/01/01","ItmFrmt":"ResultItem","Subrole":"ResultItem","PaymentType":"PrePaid","NumberOfCopies":1,"UsageInfo":"P-1008254-158123-CUSTOMER-null-3667449","Role":"ResultItem","RetailPrice":0.39,"EffectivePrice":0,"Multiplier":1,"ParentItemId":"186168"},{"EventName":"REDISTRIBUTION","PubEndDate":"2014/01/31","HistoryId":30330244,"AlertId":311116,"ItmId":"1515993516","SourceType":"Trade Journals","ReasonCode":"","Origin":"Alert","MyResearchUser":"610165","ProjectCode":"","PublicationCode":"5AMP","PubStartDate":"2014/01/01","ItmFrmt":"ResultItem","Subrole":"ResultItem","PaymentType":"PrePaid","NumberOfCopies":1,"UsageInfo":"P-1008254-158123-CUSTOMER-null-3667449","Role":"ResultItem","RetailPrice":0.39,"EffectivePrice":0,"Multiplier":1,"ParentItemId":"186168"}],"Operation":["optype:Email","location:null","target:null"],"UserInfo":{"IP":"172.27.242.164","AppId":"DIALOG","SessId":"20140416040131549:367642","UsageGroupId":"1138209"},"UsageType":"multiRetrieve","BreadCrumb":"OS-AlertEmailBase-logAlertEmail-2-AlertID-311116"}

我想在ItmFrmt键之后查询引号中的项，在这种情况下，值"ResultItem“，但是在完整的数据库中，这些引号之间会有不同的值。

Answer 1

你可以试试这个：

SELECT *
FROM your_table_name
WHERE jsonFieldName like '%"ItmFrmt":"ResultItem"%'

这将给出所有具有该字符串的确切部分的字段。

Answer 2

您可以使用替换获取所需的文本。

 select regexp_replace(columnName, '.*"ItmFrmt":"(.*)",.*', '\1') as queryString
 from tableName
 where queryString ~ '"ItmFrmt":'
 and queryString ...

第一个where子句将只获得实际拥有ItemFrmt片段的条目，因为替换不会在不匹配的行中替换任何内容，只会按原样报告。

...是用来填写您要查询的内容。但请注意，这并不能处理对JSON的特殊引用。最好将JSON放在单独的列/表中，并使用内置的JSON处理。此外，我不能保证这是多么有效，特别是如果你有一个非常大的桌子。