I have a trino table with a multilevel JSON like column, which is dynamic (hence why I don't just parse it).
I am looking to match to values, and group them. Yet there are different values in between.
For instance, the JSON might look like this:
{"items":[{"uri":"7d054d_0f6658f857284eb3bb4ee5b421e3e42e~mv2.jpeg","title":"received_5476618745745539","link":{"externalLink":{"url":"https://www.ladybugcrafter.com/baby-onesies","target":"_BLANK","type":"ExternalLink"}},"alt":"","height":1271,"width":1423,"type":"Image"},{"uri":"7d054d_e3e941bb37894c92b02ac42c316c51be~mv2.jpg","title":"IMG_20210308_165858_626","link":{"externalLink":{"url":"https://www.ladybugcrafter.com/drinkware","target":"_BLANK","type":"ExternalLink"}},"alt":"","height":1080,"width":1080,"type":"Image"}
Different JSON might have different keys. What is globally right to all of them is url, and title.
I want to extract the matching title value, and it's url. To later separate in to two different columns.
I tried doing such thing in Trino:
REGEXP_EXTRACT_ALL(comp_features,'"title":"([^"]+)".*?"url":"([^"]+)"', 2) as link ,REGEXP_EXTRACT_ALL(comp_features,'"title":"([^"]+)".*?"url":"([^"]+)"', 1) as title
which did in fact separate the correct values. Though all values were in link, and all titles in title. And to dynamically parse it in Trino might not be great. I want to either have the correct link and correct title to be in a record, or to group them in to a combination of the correct link and title into one record.
Thanks!
