0

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 ?

2 Answers 2

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

1
  • There is only one of each in each object.
    – SPlatten
    Jun 9, 2021 at 5:41
0

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'.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.