CODEWITHSUNDEEP
jsonregexhivehiveql
SAM244776
SAM244776

Reputation: 1465

Regex extra in Hive after particular string

I am very bad with Regex extract. I am trying to get the string which is marked in bold. Basically everything after cardUUID':u' till next '

'{u'cardName': u'Hilton Garden Inn Macon/Mercer University', u'cardUUID': u'a99512ea-c875-4aaa-8b0d-bb8dd668aaa8', u'attributionId': u'29fb392a-b4b6-ffab-d7e8-45e9d470e585', u'title': {u'content': u'Hilton Garden Inn Macon/Mercer University', u'type': u'title'}, u'cardSubtype': u'GETAWAYS_MARKET_RATE', u'slot_1': {u'content': u'Macon, Georgia', u'type': u'location-and-distance'}, u'value': {u'content': u'$135', u'type': u'price'}}'

I tried couple of things in regex it did not work. Any suggestions? I am using Hive

Upvotes: 1

Views: 66

Answers (2)

leftjoin
leftjoin

Reputation: 38335

Using get_json_object:

Check original string:

 select  '{u\'cardName\': u\'Cortyard Greenbelt\', u\'cardUUID\': u\'cfcc39d4-24d1-40b2-84b5-9aaab263fa0e\', u\'attribtionId\': u\'29fb392a-95fd-268f-7f84-7a58a7494c35\', u\'title\': {\'content\': \'Cortyard Greenbelt\', \'type\': \'title\'}, \'cardSbtype\': \'GETAWAYS_MARKET_RATE\', \'slot_1\': {\'content\': \'Greenbelt, Maryland\', \'type\': \'location-and-distance\'}, \'vale\': {\'content\': \'$140\', \'type\': \'price\'}}' as json;
OK
{u'cardName': u'Cortyard Greenbelt', u'cardUUID': u'cfcc39d4-24d1-40b2-84b5-9aaab263fa0e', u'attribtionId': u'29fb392a-95fd-268f-7f84-7a58a7494c35', u'title': {'content': 'Cortyard Greenbelt', 'type': 'title'}, 'cardSbtype': 'GETAWAYS_MARKET_RATE', 'slot_1': {'content': 'Greenbelt, Maryland', 'type': 'location-and-distance'}, 'vale': {'content': '$140', 'type': 'price'}}
Time taken: 2.38 seconds, Fetched: 1 row(s)

Now remove u and replace ' with " and extract JSON element:

hive> select get_json_object(regexp_replace(json,'(u\')|\'','"'),'$.cardUUID') cardUUID
    > from
    > (
    > select  '{u\'cardName\': u\'Cortyard Greenbelt\', u\'cardUUID\': u\'cfcc39d4-24d1-40b2-84b5-9aaab263fa0e\', u\'attribtionId\': u\'29fb392a-95fd-268f-7f84-7a58a7494c35\', u\'title\': {\'content\': \'Cortyard Greenbelt\', \'type\': \'title\'}, \'cardSbtype\': \'GETAWAYS_MARKET_RATE\', \'slot_1\': {\'content\': \'Greenbelt, Maryland\', \'type\': \'location-and-distance\'}, \'vale\': {\'content\': \'$140\', \'type\': \'price\'}}' as json
    > )s;
OK
cfcc39d4-24d1-40b2-84b5-9aaab263fa0e
Time taken: 0.184 seconds, Fetched: 1 row(s)

If the string contains leading and trailing ' like in your post, they should be removed.

Upvotes: 1

Rocky Li
Rocky Li

Reputation: 5958

Positive lookbehind and lookahead with lazy matching:

(?<='cardUUID': u').*?(?=')

Check: https://regexr.com/42jru

Upvotes: 0

Related Questions