I know of the functions such as: JSON_SEARCH() and JSON_EXTRACT() etc. This issue is that I am searching for a key in a json string that is not standardized. for example: and the results could be something like this: so in this example I want to get john doe with the acctNum of 123. but, also, the location of the

mysql – Search for a key within inconsistent json structure

I know of the functions such as: JSON_SEARCH() and JSON_EXTRACT() etc. This issue is that I am searching for a key in a json string that is not standardized. for example:

SELECT
    *
FROM
    users

and the results could be something like this:

+---------------------------------------------+
| name | last | data                          |
+------+------+-------------------------------+
| john | doe  | {"acctNum": "123"}            |
+------+------+-------------------------------+
| john | doe  | {"data":{ "acctNum": "123" }} |
+------+------+-------------------------------+
| jane | doe  | {"data":{ "acctNum": "1234" }}|
+------+------+-------------------------------+

so in this example I want to get john doe with the acctNum of 123. but, also, the location of the acctNum key is different. I have seen here that you can use: JSON_SEARCH(data, $**.acctNum) but I get empty results,

In an ideal world what I would like is:

SELECT * FROM users WHERE JSON_EXTRACT(data, "$**.acctNum") = 123

and receive

+---------------------------------------------+
| name | last | data                          |
+------+------+-------------------------------+
| john | doe  | {"acctNum": "123"}            |
+------+------+-------------------------------+
| john | doe  | {"data":{ "acctNum": "123" }} |
+------+------+-------------------------------+

also that in the link that is above finding the key acctNum could be extremely nested.

I had tried using the regexp with SELECT * FROM users WHERE data REGEXP '123'; but as you could imagine then the user jane doe will be matched as well

Answer

Search a defined value at any path:

SELECT *
FROM users
WHERE JSON_SEARCH(data, 'one', 123);

Search a defined value at a defined path:

SELECT *
FROM users
WHERE JSON_SEARCH(data, 'one', 123) = '$.acctNum'; -- or '$.data.acctNum'

Search a defined property name and value at any path:

SELECT *
FROM users
WHERE JSON_SEARCH(data, 'all', 123) LIKE '%.acctNum"%';

fiddle

Advertisement

Answer