Find all records by the value of the json key in MariaDB 10.1

Question

I have MariaDB 10.1. - I can't use JSON functions - JSON_EXTRACT etc.).

In the database I have a table CONTRACTS and a column data, which contains JSON (data type TEXT):

{"879": "Test", "880": "15255", "881": "2021-10-22"}

And I need to find all records that have a key value of "880" in some range, eg greater than 10000 and less than 20000, ie. in this case, a record with a value of 15255.

Thanks for advice.

Answer 1

Maybe something like this:

SELECT
  TRIM(BOTH '"' FROM 
    REGEXP_SUBSTR(REGEXP_SUBSTR(CONTRACTS.`data`, '"880": "[0-9]+"'), '"[0-9]+"$')
  ) * 1 BETWEEN 10000 AND 20000
FROM
  (SELECT 
   '{"879": "Test", "880": "15255", "881": "2021-10-22"}' AS `data`
  ) AS CONTRACTS

So the most internal regexp gives you the key + value. The outer regexp takes that result and extracts the value in quotes. Trim the quotes and test the value. You could use the entire TRIM(...) as a criterium .