WP Meta Query for some meta (array) values

Question

If I get my posts using meta_query by 'my_meta_key' only I get something like this

Array(
    [0] => Array(
       [nested_key1] => something_I_want1
       [nested_key2] => something_I_want2
       [dont_care_key] => something_I_dont_care_about
    )
    [1] => Array(
       [nested_key1] => something_I_want1
       [nested_key2] => something_I_DONT_want
       [dont_care_key] => something_I_dont_care_about
    )
)

Now I would like to do a meta_query, that would only fetch posts with 'my_meta_key' containing something_I_want1 AND something_I_want2 at the same time...

Problem is, I can't query by the full array, because a) I really don't care about "something_I_dont_care_about" metas and b) I don't know what they will be - so I need to query only by partial array...

I've tried this without success (WP_Query meta_query arg) (no posts fetched):

'meta_query' => [
  [
    'key' => 'my_meta_key',
    'value' => [
      'nested_key1' => 'something_I_want1',
      'nested_key2' => 'something_I_want2',
    ],
    'compare' => 'IN', //I've tried with and without  
  ]
],

What am I doing wrong? Is it even possible to query only by part of meta array?

Answer 1

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 );
    }
}