Hi,
I understand MySQL JSON functions work in many places of Backendless.
However, I have been trying to use them while performing data queries, without any success. Perhaps you could help ?
I am trying to do something like this :
JSON_CONTAINS(localizations, 'test')
or
JSON_CONTAINS(localizations, 'test', '$.field')
or
JSON_EXTRACT(localizations, '$.field') like '%test%'
in order to find those records where the JSON field localizations contains ‘test’.
Thanks for any guindance.
Hello @Nicolas_REMY,
(E.g) In case in you localizations JSON column you have an object with the name field
You can use this condition to filter all records which contain test in their name:
localizations->'$.name' LIKE '%field%'
You can find more information about [JSON queries] (JSON Query - Backendless REST API Documentation) in our documentation.
And this is documentation for Searching with the Where Clause with condition samples.
Regards,
Olha
Hi @olhadanylova and thanks a lot for the reply.
I tested this and it’s great that it’s working. Sorry for overlooking this part of the documentation.
This does mean, however, that I do need to specify the JSON path where I am searching. Ideally I am looking to find a way to search in the entire JSON text. Indeed I am looking into many fields, so I would need to combine many fields rather than search the whole object, which would be far simpler.
Based on the JSON Query Functions section of the doc, I understood that something like
JSONCONTAINS(localizations, ‘test’) = 1
could work, but it doesn’t : I get an “Invalid JSON text in argument.” error.
Could you help ?
Hi Nicolas,
I just tried and it didn’t work for me either. I opened a ticket for the dev team (BKNDLSS-24142)
Regards,
Mark
Thanks @mark-piller ,
Indeed, according to the documentation I was under the impression it should have worked.
Have nice week-end-
(1)
Where Clause expects boolean result and function returns the number.
Thus the condition should always look like: function_name(arg1, arg2, ...) = <something>
Where <something> can be a literal, or a column name, or even other function.
(2)
For simplification the request can be constructed using dynamic properties, where the result of function call is associated with the alias.
In properties: property1, property2, function_name(arg1, arg2) as func_result
In where clause use alias: func_result = <something>
(3)
Don’t confuse the contains and search functions.
JsonContains looks into the json document and tries to find a part which is equal to another json document.
Here is the doc link: JSON Query - Backendless REST API Documentation
Example:
The json document in the db column identities:
[ {"id": 123, "name": "Test"}, {"id": 23, "name": "Test2"} ]
JsonContains( identities , '{ "id": 123 }' ) as idcontains should return 1.
In where clause: idcontains = 1