How to query JSON?

Questions and Answers Strapi Backend
1

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

e.g.

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

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

2

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.

3

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?

4

Relations would be the best way

1 Like
5

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

6

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).

7

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.

8

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.

9

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.