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.4.0 is now available

6th May 2024

This release has a focus on improving compatibility with PHP 8.2. The introduction of inheritance validation to PHP produced a lot of warnings when using MasterPress with PHP 8.2. I have updated most of the problematic code – but some code would have broken in previous PHP versions, so I have temporarily disabled inheritance validation… 

Plugin Requirements

MasterPress requires a minimum of WordPress version 4.9, MySQL 5.6, and PHP version 5.6.20.

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.

Three AM