meta_query with meta values as serialize arrays

by

No, it is not possible, and could even be dangerous.

There is no reliable way to do this without encountering gotchas and pitfalls such as a search for 10 matching 100 or 210, or matching the wrong sub-value etc. Such solutions are unreliable and have performance and security issues.

Serialised data is an attack vector, and a major performance issue.

I’ll cover:

  • Fixing it and making it queryable
  • Why Can’t You Query Inside Serialised Data?
    • Why LIKE isn’t a solution
  • A Note on Storing Records/Entities/Objects as Serialized Objects in Meta
  • Security and Serialized Objects
  • What to do If I have a List of IDs?
  • What to do If I Have an Array of Named items?
  • Sidestepping The Problem Entirely
  • Conclusion

Fixing It And Making It Queryable

I strongly recommend you unserialise your data and modify your save routine. Something similar to this should convert your data to the new format:

$args = [
    'post_type' => 'my-post-type',
    'meta_key' => '_coordinates',
    'posts_per_page' => -1,
];
$query = new WP_Query( $args );
if ( $query->have_posts() ) {
    while ( $query->have_posts() ) {
        $query->the_post();
        // get the data
        $c = get_post_meta( $post->ID, '_coordinates', true );

        // save it in the new format, separate post meta, taxonomy term etc
        add_post_meta( $post->ID, '_longitude', $c['longitude'] );
        add_post_meta( $post->ID, '_latitude', $c['latitude'] );

        // Optional: clean up the old post meta
        delete_post_meta( $post->ID, '_coordinates', $c );
    }
    wp_reset_postdata();
}

Then you’ll be able to query as you want with individual keys

If you need to store multiple longitudes, and multiple latitudes, you can store multiple post meta with the same name. Simply use the third parameter of get_post_meta, and it will return them all as an array

Why Can’t You Query Inside Serialised Data?

MySQL sees it as just a string, and can’t break it apart into structured data. Breaking it apart into structured data is exactly what the code above does

You may be able to query for partial chunks of date, but this will be super unreliable, expensive, slow, and very fragile, with lots of edge cases. Serialised data isn’t intended for SQL queries, and isn’t formatted in a regular and constant way.

Aside from the costs of partial string searches, post meta queries are slow, and serialised data can change depending on things such as the length of contents, making searching incredibly expensive, if not impossible depending on the value you’re searching for

What About LIKE?

You might see some well meaning questions that suggest using LIKE to achieve this. Does this not solve the problem? This is not the solution, it is fools gold.

There are several major problems:

  • false matches, searching for test with LIKE will also match test, testing, untested, and other values
  • there’s no way to constrict this to sub-keys for arrays with keys or objects
  • it’s not possible to do sorting
  • it’s extremely slow and expensive

LIKE will only work for specific limited situations that are unrealistic, and carries a heavy performance penalty.

A Note on Storing Records/Entities/Objects as Serialized Objects in Meta

You might want to store a transaction record in post meta, or some other kind of data structure in user meta, then run into the problem above.

The solution here is not to break it out into individual post meta, but to realise it should never have been meta to begin with, but a custom post type. For example, a log or record can be a custom post type, with the original post as a parent, or joined via a taxonomy term

Security and Serialized Objects

Storing serialized PHP objects via the serialize function can be dangerous, which is unfortunate as passing an object to WordPress will mean it gets serialised. This is because when the object is de-serialized, an object is created, and all its wake up methods and constructors get executed. This might not seem like a big deal until a user manages to sneak a carefully crafted input, leading to remote code execution when the data is read from the database and de-serialized by WordPress.

This can be avoided by using JSON instead, which also makes the queries easier, but it’s much easier/faster to just store the data correctly and avoid structured serialized data to begin with.

What If I have a List of IDs?

You might be tempted to give WP an array, or to turn it into a comma separated list, but you don’t have to!

Post meta keys are not unique, you can store the same key multiple times, e.g.:


$id = ...;
add_post_meta( $id, 'mylist', 1 );
add_post_meta( $id, 'mylist', 2 );
add_post_meta( $id, 'mylist', 3 );
add_post_meta( $id, 'mylist', 4 );
add_post_meta( $id, 'mylist', 5 );
add_post_meta( $id, 'mylist', 6 );

But how do I get the data back out? Have you ever noticed how calls to get_post_meta have a 3rd parameter that’s always set to true? Set it to false:

$mylist = get_post_meta( $id, 'mylist', false );
foreach ( $mylist as $number ) {
    echo '<p>' . esc_html( $number ) . '</p>;
}

What If I Have an Array of Named items?

What if I wanted to store this data structure in a way that lets me query the fields?

{
  "foo": "bar",
  "fizz": "buzz"
  "parent": {
    "child": "value"
  }
}

That’s easy, split it up with prefixes:

add_post_meta( $id, "tomsdata_foo", "bar" );
add_post_meta( $id, "tomsdata_fizz", "buzz" );
add_post_meta( $id, "tomsdata_parent_child", "value" );

And if you needed to loop over some of those values, use get_post_meta( $id ); to grab all post meta and loop over the keys, e.g.:

$all_meta = get_post_meta( $id );
$look_for="tomsdata_parent";
foreach ( $all_meta as $key => $value ) {
    if ( substr($string, 0, strlen($look_for)) !== $look_for ) {
        continue; // doesn't match, skip!
    }
    echo '<p>' . $key . ' = ' . $value . '</p>';
}

Which would output:

<p>tomsdata_parent_child = value</p>

Remember, when WP fetches a post it fetches all its post meta at the same time, so get_post_meta calls are super cheap and do not trigger extra database queries

Sidestepping The Problem Entirely

If you know you’re going to need to search/query/filter on a sub-value, why not store an additional post meta with that value so you can search for it?

Conclusion

So you don’t need to store structured data as a string in the database, and you shouldn’t if you plan to search/query/filter on those values.

It might be possible to use a regular expression and a LIKE, but this is extremely unreliable, doesn’t work for most types of data, and very, very slow and heavy on the database. You also can’t perform math on the results like you could if they were separate values