RegEx Key Value Pair

[rahulchauhan]

Hi,

I'm fairly new to RegEx and am trying to extract following values from key:value pairs in the following text -

Values to be extracted -

1. RDU5

2. FleetManagementEquipmentRepositioning

3. FIFTY_THREE_FOOT_TRUCK

4. 1562549400000

Above values are a part of this below string (Key:value pairs for above are marked in Red) -

[{"loadEquipmentType":"FIFTY_THREE_FOOT_TRUCK","loadDistance":{"unit":"MILES","value":189.263},"loadDurationInMillis":16200000,"loadCostItems":[{"name":"Fuel Surcharge","description":"FUEL_SURCHARGE_PERMILE","costType":"SHIP_COST","priceValue":{"unit":"USD","value":56.7789}},{"name":"Base Rate","description":"PER_LOAD","costType":"SHIP_COST","priceValue":{"unit":"USD","value":332.70603569}},{"name":"TOTAL_COST","description":"TOTAL_COST","costType":"SHIP_COST","priceValue":{"unit":"USD","value":389.48493569000004}}],"loadOriginSchedule":{"arrivalTime":1562549400000,"departureTime":1562551200000},"loadDestinationSchedule":{"arrivalTime":1562562000000,"departureTime":1562565600000},"loadStops":[{"stopActions":[{"stopActionType":"PICKUP","stopActionSourceRefs":[{"refId":"tr-df366aa1-b290-4228-a6aa-d7b301d43e7d","refVersion":0,"refType":"TR"}],"preloadedTrailerOwner":"AZNG","loadingType":"PRELOADED"}],"stopAddressDetails":{"addressId":"188265441303","marketplace":"ATVPDKIKX0DER","addressLine1":"1805 TW Alexander Drive","stopCode":"RDU5","domicile":"RDU","city":"DURHAM","state":"NORTH CAROLINA","country":"US","postalCode":"27703","timezone":"America/New_York","location":{"lat":35.923225462846894,"lon":-78.83233308792116}},"stopSchedule":{"arrivalTime":1562549400000,"departureTime":1562551200000},"stopWeight":{"unit":"pounds","value":0.0},"stopShipperAccounts":["FleetManagementEquipmentRepositioning"],"stopSpecialServices":[]},{"stopActions":[{"stopActionType":"DROPOFF","stopActionSourceRefs":[{"refId":"tr-df366aa1-b290-4228-a6aa-d7b301d43e7d","refVersion":0,"refType":"TR"}],"unloadingType":"DROP"}],"stopAddressDetails":{"addressId":"191655366903","marketplace":"ATVPDKIKX0DER","addressLine1":"3516 S. Military Hwy","stopCode":"CCVA","city":"CHESAPEAKE","state":"VA","country":"US","postalCode":"23323","timezone":"America/New_York","location":{"lat":36.770517504808275,"lon":-76.35079756386239}},"stopSchedule":{"arrivalTime":1562562000000,"departureTime":1562565600000},"stopShipperAccounts":[],"stopSpecialServices":[]}],"loadSourceRefs":[{"refId":"tr-df366aa1-b290-4228-a6aa-d7b301d43e7d","refVersion":0,"refType":"TR"}],"loadShipperAccounts":["FleetManagementEquipmentRepositioning"],"isExternalLoad":false,"loadSpecialServices":[]},{"loadEquipmentType":"FIFTY_THREE_FOOT_TRUCK","loadDistance":{"unit":"MILES","value":245.77400000000003},"loadDurationInMillis":25200000,"loadCostItems":[{"name":"Fuel Surcharge","description":"FUEL_SURCHARGE_PERMILE","costType":"SHIP_COST","priceValue":{"unit":"USD","value":73.73219999999999}},{"name":"Base Rate","description":"PER_LOAD","costType":"SHIP_COST","priceValue":{"unit":"USD","value":453.01560962}},{"name":"TOTAL_COST","description":"TOTAL_COST","costType":"SHIP_COST","priceValue":{"unit":"USD","value":526.74780962}}],"loadOriginSchedule":{"arrivalTime":1562562000000,"departureTime":1562565600000},"loadDestinationSchedule":{"arrivalTime":1562583600000,"departureTime":1562587200000},"loadStops":[{"stopActions":[{"stopActionType":"PICKUP","stopActionSourceRefs":[{"refId":"tr-c350859f-077c-4e5c-affe-4c2e340bd97d","refVersion":0,"refType":"TR"}],"preloadedTrailerOwner":"AZNG","loadingType":"PRELOADED"}],"stopAddressDetails":{"addressId":"53637540915","marketplace":"ATVPDKIKX0DER","addressLine1":"3516 S. Military Hwy","stopCode":"777-0029","city":"CHESAPEAKE","state":"VA","country":"US","postalCode":"23323","timezone":"America/New_York","location":{"lat":36.770517504808275,"lon":-76.35079756386239}},"stopSchedule":{"arrivalTime":1562562000000,"departureTime":1562565600000},"stopWeight":{"unit":"pounds","value":16.77579475738985},"stopShipperAccounts":["AmazonInboundImports"],"stopSpecialServices":[]},{"stopActions":[{"stopActionType":"DROPOFF","stopActionSourceRefs":[{"refId":"tr-c350859f-077c-4e5c-affe-4c2e340bd97d","refVersion":0,"refType":"TR"}],"unloadingType":"DROP"}],"stopAddressDetails":{"addressId":"232496680003","marketplace":"ATVPDKIKX0DER","addressLine1":"281 Woodbine Rd","stopCode":"BWI4","city":"CLEAR BROOK","state":"VIRGINIA","country":"US","postalCode":"22624","timezone":"America/New_York","location":{"lat":39.289493,"lon":-78.078637}},"stopSchedule":{"arrivalTime":1562583600000,"departureTime":1562587200000},"stopShipperAccounts":[],"stopSpecialServices":[]}],"loadSourceRefs":[{"refId":"tr-c350859f-077c-4e5c-affe-4c2e340bd97d","refVersion":0,"refType":"TR"}],"loadShipperAccounts":["AmazonInboundImports"],"isExternalLoad":false,"loadSpecialServices":[]}]

I have been banging my head against the wall trying to solve this. Any help is much appreciated on this.

[T_Willins]

Hi @rahulchauhan,

Attached is a workflow that parses the values to be extracted based on the precedent key for each value.  The values to be extracted are dynamic, so this should work with data that meets the same format.  If the data values were always in the same order as your sample, this is done in a single RegEx, otherwise separate parses are needed.  Both methods are in the attached workflow.

[JessieC]

@rahulchauhan - you can use the JSON Parse tool - https://help.alteryx.com/2019.3/JSONParse.htm

[rahulchauhan]

@T_Willins 

Separate parsing is almost perfect except that I need just RDU5 instead of "stopCode":"RDU5". Same for others as well. Is it possible to modify to only the value from the K-V pair?

Thanks for your help!

[rahulchauhan]

@JessieC Thanks for your response. But unfortunately this is not the solution that I'm looking for because you've used the Value part from my example to search for those. I have millions of these rows and this example is just one of the data cells. I need a way so that we can look up the key in these Key-Value pair and then only extract the Value part of it and nothing else.

[T_Willins · Accepted solution]

Updated - I moved the parentheses to capture the data you are looking for.  Still dynamic for data with the same key.

If this works, please mark this as an accepted solution so the Community knows it has been answered.

[JessieC]

@rahulchauhan - you can simply change the filters to search for the KEY to return the VALUE. I just mocked up a quick example to show the JSON Parse can parse most of the data without writing RegEx. Attached is the workflow modified with filtering on Key.

[rahulchauhan]

@T_Willins Awesome! This works perfectly fine. Thank you so much, sir. You're a gentleman and a scholar.

[rahulchauhan]

@T_Willins Just a long shot - Do you, by any chance, know the Redshift version of the code you've given? I'm extracting this data directly from Redshift and apparently Redshift doesn't support your normal RegEx. On running your code it gave me following error -

https://docs.aws.amazon.com/redshift/latest/dg/pattern-matching-conditions-posix.html

Here's is the documentation that I've been using to figure this out.

[T_Willins]

Hi @rahulchauhan,

Unfortunately, I do not know the Redshift version.