Remove extra data within meta value...

[Oran223]

Hi ...

I have a database that hold the images in a strange way and to import them I need to clear the extra data sitting around them.

The meta_value is as follows : 

[{"id":"31768","imageurl":"https://sampledomain.com/wp-content/uploads/2021/10/904002d0f93c473a98f586302ccbd67d_ful.jpg"},{"id":"31766","imageurl":"https://sampledomain.com/wp-content/uploads/2021/10/5720254985644cb9a6679e258b74a202_ful.jpg"},{"id":"31767","imageurl":"https://sampledomain.com/wp-content/uploads/2021/10/155aa9070ad6404586a50e865be43a17_ful.jpg"},{"id":"31765","imageurl":"https://sampledomain.com/wp-content/uploads/2021/10/10ba1f392a7b401087e6d1ede995057b_ful.jpg"}]

but I need it as this (simply the image url separated by a comma) .... 

https://sampledomain.com/wp-content/uploads/2021/10/904002d0f93c473a98f586302ccbd67d_ful.jpg,https://sampledomain.com/wp-content/uploads/2021/10/5720254985644cb9a6679e258b74a202_ful.jpg,https://sampledomain.com/wp-content/uploads/2021/10/155aa9070ad6404586a50e865be43a17_ful.jpg, https://sampledomain.com/wp-content/uploads/2021/10/10ba1f392a7b401087e6d1ede995057b_ful.jpg

Using wildcards is there a way to be able clean/remove the unwanted data???  I have had a go but my knowledge of MySQL is not that advanced.

Many thanks in advance.

 

[kicken]

The original data is JSON. If the " is actually part of the data and not a copy/paste issue here, then it's been run through htmlentities() or similar and you'll need to reverse that.

If you're using a new enough version of mysql, you could potentially use json_extract to get the URLs.

select json_extract(replace(jsonColumn, '"','"'), '$[*].imageurl')

Otherwise, you'd probably just want to select the column, and parse and extract the data as you fetch results in your code.

 

Edited March 27, 2023 by kicken