Reply To: How to query againts json objects?

Author Replies
traversal # Posted on May 28, 2013 at 9:47 am

Hey Bart.

There are big challenges around storing the data as flattened for those particular fields, largely because it becomes very difficult to query them to build the field set structures for the API. MasterPress uses JUST the post meta table when storing data against posts, and it’s already quite a task to work out the correct structure for field sets that allow multiple items – it’s already tricky to understand where the boundaries between field set items are when the data is flattened out. If we were to store multi-item fields like that as flat data too, it makes that problem even more difficult to solve (perhaps impossible using only a postmeta table).

There are ways to run MySQL queries on that kind of JSON data – they’re not particularly easy but it can be done.

Suppose you were looking for posts with the checkbox value “Feature Homepage”. Then you could use something like this:

Also, just to clarify, are you using a checkbox list field type to store those values?

If you do need to run queries on the posts that are checked that way, you would be better off creating a custom taxonomy, and linking your posts to terms within that taxonomy. The intention with a checkbox list field type like that was really more for inspecting those values as you’re looping over a set of posts, as you’ve mentioned.

Sorry about this, I understand the need, but I do feel that custom taxonomies are a better fit if you’re trying to run queries, as those get stored in such a way that queries are easily supported. And it’s unfortunately not trivial to modify the way multiple value fields are stored, as this could very easily break so many other things.

I will make note of this use-case though – there is some potential to add a friendly wrapper around querying for specific values inside multi-value JSON strings.

Latest From the Blog

MasterPress 1.1.4 now available

MasterPress 1.1.4 is now available. This release contains an important compatibility fix for WordPress 4.5 to allow correct detection of the taxonomy term editing screen. Without this fix, any custom fields you have attached to custom taxonomies will not be shown at all in the editing form. Note also that MasterPress will still detect the edit screen correctly in… 

Plugin Requirements

MasterPress requires a minimum of WordPress version 3.4, MySQL 5, and PHP version 5.2.4.

We also recommend that PHP is configured to use a memory limit of 64MB per request (128MB may be required for sites with higher complexity).

This plug-in is not compatible with the hosted service.