I am using MariaDB 10.5, I have a JSON field in a table which typical contains something like:
[{"Filename":"C:/Users/simon/Documents/file.ext"},{"Soil":"Sand"},{"Present":"None"},{"Value":"5000"}]
I want to construct queries that can look for matches in the database, so I might have a QStringList containing:
Soil:Sand
Value:5000
Where the value left of the colon is the JSON member name and the value on the right is the value to compare.
How can I use these to construct a query where the table name is for example: datasets and the field is jsonParams ?
Best Answer
Use JSON_SEARCH().
Your method with JSON_VALUE() is applicable if there is only one
"Soil"
attribute per the whole value.See DEMO fiddle