How to query JSON?

Is it possible to directly search for a given key value pair in a JSON field?


module.exports = {
  findProductInStock(size) {
    return strapi.query("product").find({ sizes: {size: true} });

where sizes is a json field of sizes with boolean values.

At this time it’s not possible to filter on JSON fields as not all databases support native JSON filtering. You can kinda get away with it by using a _contains filter but it can be quite ugly.

Is it possible by using knex/bookshelf methods?

If not, what is the best way to have a collection of products, each with multiple sizes that are either in or out of stock, then have a query fetching all products that are in stock for a given size. By having many enums on each products?

Relations would be the best way

1 Like

How does it work with _contains in a GraphQL query? I’m getting no result from my JSON field…

You can’t filter on JSON either (most databases we support don’t have normal JSON fields and we never implemented such a feature if the databases don’t support it).

Thanks DMehaffy for the response. I see you’d made it in the past but wasn’t sure if it was still not possible.

So instead I’ve added a text field in Strapi, saving to it a stringified version of my JSON, and querying that instead.

That should work, given a _contains might work with a json field, it’s certainly not guaranteed and it would be searched just like a string would anyway.

Hi. I am a little bit late into the discussion however this might be useful for someone in the future. My problem was actually simple - how to add query filters into gql query. It turned out that the where parameter accepts JSON file, where you can specify your query.

By “your query” I mean your parameters followed by _ and then filter operator → see here: Deep Dive into Strapi GraphQL under filters paragraph.