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: http://pastebin.com/wExPk3Zn 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.

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:

http://pastebin.com/wExPk3Zn

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.

MasterPress

Latest From the Blog

MasterPress 1.3.10 is now available

9th November 2023

For Members

Resources

Plugin Requirements

Three AM