0

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user2095758
  • 21
  • 2
  • 1
    Because of [json functions](https://mariadb.com/kb/en/json-functions/) and [WITH](https://mariadb.com/kb/en/with/) it might be a good idea to upgrade your version of MariaDB – Luuk Oct 31 '21 at 08:48
  • I edited the title and tags to make it more clear you are using MariaDB. MariaDB is not MySQL, despite being forked from MySQL in 2010. – Bill Karwin Nov 01 '21 at 05:48

1 Answers1

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 .

Sam020
  • 376
  • 2
  • 6