plugin development - Is there a way to use MySQL JSON functions in meta queries?

admin管理员组

文章数量:1122832

There are JSON functions: .3/en/json-search-functions.html can be used to search in complex data with MySQL. In the case of meta keys and values it is an issue that with too many keys the database becomes bloated, so better to store the values in an associative array and use only a single key. Currently this array is PHP serialized. Since MySQL does not support PHP unserialization and searching this makes developer's life pretty hard, which leads to hacks like this. I thought that maybe we could use JSON serialization format and the built-in MySQL JSON functions to search and sort this kind of associative arrays. Is this implemented in Wordpress atm? If not, then what do you suggest, how to implement it in theory?

There are JSON functions: https://dev.mysql.com/doc/refman/8.3/en/json-search-functions.html can be used to search in complex data with MySQL. In the case of meta keys and values it is an issue that with too many keys the database becomes bloated, so better to store the values in an associative array and use only a single key. Currently this array is PHP serialized. Since MySQL does not support PHP unserialization and searching this makes developer's life pretty hard, which leads to hacks like this. https://www.youtube.com/watch?v=jUjjAYpkiKg I thought that maybe we could use JSON serialization format and the built-in MySQL JSON functions to search and sort this kind of associative arrays. Is this implemented in Wordpress atm? If not, then what do you suggest, how to implement it in theory?

• plugin-development
• meta-query
• array
• json

Share Improve this question asked Apr 5, 2024 at 13:31 inf3rnoinf3rno 13566 bronze badges 4

• 1 WordPress core doesn't currently implement JSON serialization for meta values. So the custom way is to modify database schema yourself, migration of current data (if any) to JSON format schema, and change the WP core or add filters to modify queries to match your updated schema. This needs time and attention. – Aqsa J. Commented Apr 7, 2024 at 3:54
• @AqsaJ. I might open a feature request for it. Thanks! – inf3rno Commented Apr 7, 2024 at 9:25
• core.trac.wordpress.org/ticket/60950#ticket – inf3rno Commented Apr 7, 2024 at 9:44
• 1 Following that now. – Aqsa J. Commented Apr 7, 2024 at 10:29

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Currently this is not supported, but I added a feature request ticket which you can support here: https://core.trac.wordpress.org/ticket/60950#ticket

本文标签： plugin developmentIs there a way to use MySQL JSON functions in meta queries