How do I replace text with values contained in JSON?

1

I’m trying to parse through some raw tweet text, which contains the Twitter t.co shortened URLs, and replace them with the non-shortened links, based on a JSON payload. Currently I’m…stuck. The one path I thought to explore was just pulling the JSON payload apart into other columns, and then trying to do a replace, but I get stuck when there are multiple entities (URLs).

Here is the tweet text (ignore the super spammy crap, this was just a good example).

@MartiniGuyYT Sappchat Token (APP)

The world’s first Decentralized Messaging App.
💎Sapp-Chat App✅
💎Sapp-DeFi✅
💎Sapp-Wallet✅
💎Sapp-Swap✅

Download app now on Googleplay;https://t.co/IViZwnbixz
Applestore;https://t.co/QJmkaNr6Sp
Cmc: https://t.co/rWw42hTZKz
TG : https://t.co/PzxEKUi7sp

The Tweet entities payload straight from the Twitter API for this tweet:

{
	"hashtags": [],
	"symbols": [],
	"user_mentions": [
		{
			"screen_name": "MartiniGuyYT",
			"name": "That Martini Guy ₿",
			"id_str": "782946231551131648",
			"indices": [
				0,
				13
			]
		}
	],
	"urls": [
		{
			"url": "https://t.co/IViZwnbixz",
			"expanded_url": "https://play.google.com/store/apps/details?id=com.sappchat",
			"display_url": "play.google.com/store/apps/det…",
			"indices": [
				169,
				192
			]
		},
		{
			"url": "https://t.co/QJmkaNr6Sp",
			"expanded_url": "https://apps.apple.com/fi/app/sappchat/id1569053748",
			"display_url": "apps.apple.com/fi/app/sappcha…",
			"indices": [
				204,
				227
			]
		},
		{
			"url": "https://t.co/rWw42hTZKz",
			"expanded_url": "https://coinmarketcap.com/currencies/sappchat/",
			"display_url": "coinmarketcap.com/currencies/sap…",
			"indices": [
				233,
				256
			]
		},
		{
			"url": "https://t.co/PzxEKUi7sp",
			"expanded_url": "https://t.me/sappchat",
			"display_url": "t.me/sappchat",
			"indices": [
				262,
				285
			]
		}
	]
}

Right now I have the JSON going into a series of other columns and parsing out some of the arrays with:

ParseJSON(thisRow.[Tweet entities],path: "$.urls[*]")

But I’m finding this is pretty tedious, and I still haven’t solved for how to do the replacement with each instance of the short URL.

The desired outcome is to replace the t.co and link to the expanded_url but use the display_url as the …well…display.

Any thoughts on the best way to go about this?

2

So just to be clear - you don’t want to simply grab the urls. You want to insert them as a replacement into the tweet body correct?

3

That’s right. I want to replace the existing t.co addresses with the expanded values and a display. More specifically I’m doing this with Slack-flavored Markdown but could this could also be done with a traditional HTML link.

4

I got you - that was one of the most fun problems I’ve done in a while

I made a custom pack called “GIMME URLS” to grab the URLS from the tweet-JSON, and then run an action to replace them in the actual tweet via a button.

Theres probably more ways to get this done, but thats the one that came to my mind! Hope it helps! If you pass me your email address, Ill share the pack with you as well.

Also just re-read your post and saw you want the display AND expanded url. Easy to do with principles I shared above, just have to modify the pack a bit to pull out both and then use hyperlink() in the button code to use display as the, well, display

Let me know if you need help with those last parts or if what I provided is a good enough start!

5

This is rad!

@Scott_Collier-Weir you can reach me at matt.galligan [at] gmail.

I bet there’s a really good pack to be packaged up around hydrating Twitter URLs with more data!

6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.