Core: Add support for searching in json fields

[Taluu]

It seems that currently we can't use the SearchFilter with a JSON field type (it seems, because I don't see it handled in this filter, unless I'm wrong).

Let's say I have the following data in my json foo field :

[
     "foo",
     "bar"
]

It would be great to be able to search ?foo[]=foo, ?foo[]=qux, ?foo[]=foo&foo[]=qux, ...

Support for "json object" could be a tad more complicated though. Maybe ?foo[name]=bar or something like that ?

[alanpoulain]

Did you try to use https://github.com/dunglas/doctrine-json-odm?

[Taluu]

I'm not using Doctrine ODM, as I'm not using a nosql database. It's just that I have a foo json file in my entity that I'd like to be able to search in it.

[alanpoulain]

This bundle is for ORM:

An Object-Document Mapper (ODM) for Doctrine ORM leveraging new JSON types of modern RDBMS.

[Taluu]

Indeed, but I don't think it resolves the problem ; in the search filter, there is no handling of json data through proper functions. Only =, IN and LIKE are supported, so you can only search with likeness, which isn't that great imo.

[soyuka]

We'd need to add specific filters for this. to work imo it's feasible :)

[patrick-mota]

After a little chat with @Taluu it seems totaly feasible.
I was thinking about using ast to do this and saw a little repo with a doctrine extension which supports json search : https://github.com/ScientaNL/DoctrineJsonFunctions

If we look for MySQL we will probably use:
https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-contains
and
https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-contains-path

An example exists in:
https://github.com/ScientaNL/DoctrineJsonFunctions/blob/master/src/Query/AST/Functions/Mysql/JsonContains.php
and
https://github.com/ScientaNL/DoctrineJsonFunctions/blob/master/src/Query/AST/Functions/Mysql/JsonContainsPath.php

What do you think @soyuka ? Do you see something wrong with this approach ?

[teohhanhui]

There's no cross-platform way to do this, so I don't think we can include it in API Platform at this point. But perhaps a docs entry would be good.

[teohhanhui]

Also, if you're using PostgreSQL, you should use the jsonb type.

[patrick-mota]

@teohhanhui an example in doc. could indeed be a really good starting point :)

Maybe I'm gonna start this first so it could be more useful and available quickly for the end user and not having any impact on the core.

[thomas-seres]

Hi !
I just make a custom filter to do this, You will find it here :
https://gist.github.com/thomas-seres/301c43649d40ebd947a9ac1f6aee681d

Feedbacks are welcomed !