Fields in database tables can not contain other types of data than text strings and numbers, and variations of them like dates and times.
The field meta_value
of wp_postmeta
is a text field. So, when you store an array as meta_value
you are really storing a string containing the serialized data that represents the original array. If you need to interpret that string again as array, you need to convet it back to that format in the appropiate language.
For example, you can get the string containing the array data in PHP, unserialized it and then searh in that array. If you know the structure of the string, you can also analyze it and cast fragments directly on a SQL statement.
But any of those methods are appropriate to filter or search by values of the array just because that array in SQL does not exist, it is a string, and to get the desired results you need to do a tricky job and consume huge resources.
It is a SQL limitation and, beacuse of that, it is a limitation of WP_Query as well.
That doesn’t mean that serialized data are not appropiated to be stored in database at all. It is appropieated and commonly used for data that are NOT intented to be used for filtering, searching, ordering by or in any other way that is not just display the information.
If you need to perform some of those actions, you should store each array key in its own meta field. If you need to convert the already stored serialized data, you can use somthing like this (source):
$args = array(
'post_type' => 'my_post_type',
'meta_key' => 'my_meta_key',
'posts_per_page' => -1
);
$query = new WP_Query( $args );
if($query->have_posts()){
while($query->have_posts()){
$query->the_post();
$my_meta_key = get_post_meta( get_the_ID(), 'my_meta_key', true );
add_post_meta( get_the_ID(), 'nested_meta_key_1', $my_meta_key['nested_meta_key_1']);
add_post_meta( get_the_ID(), 'nested_meta_key_2', $my_meta_key['nested_meta_key_2'] );
delete_post_meta( get_the_ID(), 'my_meta_key', $my_meta_key );
}
}