Skip to content

Instantly share code, notes, and snippets.

@polevaultweb

polevaultweb/mysql_extract_serialized_array_data_in_select.sql

Last active July 30, 2021 07:52
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save polevaultweb/a3e8b73caa9d1c1c700fb4c3a25f4dd5 to your computer and use it in GitHub Desktop.
Save polevaultweb/a3e8b73caa9d1c1c700fb4c3a25f4dd5 to your computer and use it in GitHub Desktop.
Download ZIP
Select a value from a serialized array using the array key in MySQL https://polevaultweb.com/2020/02/selecting-data-serialized-array-mysql/
Raw
mysql_extract_serialized_array_data_in_select.sql
# replace 'file' with the array key you want to extract
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(
SUBSTRING(pm.meta_value, ( INSTR( pm.meta_value, CONCAT( 'file', '";' ) ) + CHAR_LENGTH( 'file') + 1 ) ),
'"', 2 ),
'"', -1 ) as attachment_file
FROM wp_posts p
INNER JOIN wp_postmeta pm
ON p.ID = pm.post_id
AND pm.meta_key = '_wp_attachment_metadata'
WHERE post_type = 'attachment'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment