MariaDB JSON functions

Question

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 ?

Answer 1

Use JSON_SEARCH().

...
WHERE JSON_SEARCH(value, 'one', 'Sand', NULL, '$[*].Soil') IS NOT NULL

Your method with JSON_VALUE() is applicable if there is only one "Soil" attribute per the whole value.

See DEMO fiddle

• SPlatten: There is only one of each in each object.

Answer 2

Done...I used the function JSON_VALUE like so:

SELECT
 biPK
FROM
 datasets
WHERE
 JSON_VALUE(jsonParams, '$[*].Soil')='Sand'

The [*] above tells it to look for any index in an array for the term 'Soil' and compare with 'Sand'.