CODEWITHSUNDEEP
regexbigdatagoogle-bigquery
Teja
Teja

Reputation: 13534

How to extract a specific string from a key,value pair data in Big Query using regexp_extract?

I am trying to extract the value for a particular key 'xyz' and tried the below regex expression to accomplish it. Is this an effective method to deal with key value pairs? Can someone suggest me if there is any other solution which is more effective. Thank you.

**

 - Input & SQL :-



**

 SELECT FIRST( SPLIT( regexp_extract(kvp,r'SuppressFlexCacheHydrationIndicator=(.*)&'), '&' ) ) AS SuppressFlexCacheHydrationIndicator,
       regexp_extract(kvp,r'campaignName=(.*)$') AS campaign,
       regexp_extract(LOWER(kvp),r'resultcode=(.*)&') AS resultcode,       
  FROM ( SELECT 'SuppressFlexCacheHydrationIndicator=True&templateVersionId=5&vmtaText=mail2&sequenceId=300&resultCode=DoNotMailBounceList&campaignName=classicimport' AS kvp )

Output :-

    SuppressFlexCacheHydrationIndicator campaign        resultcode   
    True                                classicimport   donotmailbouncelist

Comments :-

In the above SQL I am using FIRST(SPLIT()) to extract the sub-string from the input which I think that it's not effective and can be implemented just by using regular expression.Please kindly share your insights if there is any other possible solution. Thanks.

Upvotes: 1

Views: 1637

Answers (1)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14004

I would use slightly different regular expressions to avoid SPLIT and FIRST costs, as well as encoding special knowledge about parameter positions in URL:

SELECT regexp_extract(kvp,r'SuppressFlexCacheHydrationIndicator=([^&]*)') AS SuppressFlexCacheHydrationIndicator,
       regexp_extract(kvp,r'campaignName=([^&]*)') AS campaign,
       regexp_extract(LOWER(kvp),r'resultcode=([^&]*)') AS resultcode,       
  FROM ( SELECT 'SuppressFlexCacheHydrationIndicator=True&templateVersionId=5&vmtaText=mail2&sequenceId=300&resultCode=DoNotMailBounceList&campaignName=classicimport' AS kvp )

Upvotes: 1

Related Questions