Find in a json array column

Good morning all of you, it’s 6AM here and very cold :slight_smile:
Does anybody know if we can perform a find in a field that is a json array ?
The array contains a list of numbers :
[“1”,“2”,“19”,“24”] etc.

I could not manage to use JSON_CONTAINS or JSON_SEARCH
It is a json array stored in a Mysql table;
I tried a standard find method :
$this->Documents->find()->where([‘Documents.readers JSON_CONTAINS’ => n])
where n can be any number

cakephp 3.x

Since JSON is just a string, is it not possible to simply do something like:

['Model.Fieldname LIKE' => '%"'.$n.'"%']

Other than that, I believe CakePHP will automatically use whatever you provide as a function in a query without necessarily knowing anything about that function. I mean, for instance, you could say

>where([‘Documents.readers NONEXISTINGFUNC’ => n])

And I thought CakePHP would just throw that into your query even if it’s invalid:

WHERE Documents.readers = NONEXISTINGFUNC(n)

Which would mean your query should work. That it isn’t tells me either I’m wrong about my understanding of how Cake handles this, or there’s something else going on giving you trouble that is not about CakePHP’s finder capacities.

MySQL definitely has the ability to look inside JSON arrays, but I don’t know how it works. Do you have an example of the actual query that you want Cake to generate, i.e. something that works when you run it directly in the MySQL client (or phpMyAdmin or whatever)? @calzone is right that you can inject arbitrary functions into your SQL with the Cake ORM, but the structure isn’t quite what he’s got there.

In fact, I never used this function. But I have to search in a json array so I googled to mysql docs and found this :
https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-contains
example found :
mysql> SET @j = ‘{“a”: 1, “b”: 2, “c”: {“d”: 4}}’;
mysql> SET @j2 = ‘1’;
mysql> SELECT JSON_CONTAINS(@j, @j2, ‘$.a’);

I just wondered if I could use this the cakephp way.

You want to execute this exactly as shown, with variables that you’re setting? Or you want to execute it against a column in a table somewhere? If the latter, please show an actual query that you might run on your existing database to get the desired result. It’s easiest for people to help you convert an actual SQL query into an ORM version, rather than guess at what you want to actually do.