Mysql Json Extract - wild card selection of key

Question

Below is the json blob that I have in my database

{
  RootData: {
    202003: {
      201903: {
        "abc": 123,
        xyz: 456
      },
      data1: {
        
      },
      data2: {
        
      }
    }
  }
}

Right now I have a query where to pull the data inside the node 201903 as below

select blah blah,
 JSON_EXTRACT(convert(columnname using utf8), '$.RootData."202003"."201903".abc') as blah

In the above query, my question revolve around the part '$.RootData."202003"."201903".abc'

I DO NOT want to hard code the part 201903 and looking for a syntax where it can select the node with help of wildcard's.

I tried the below options with no luck

'$.RootData."202003"."20*".abc'

'$.RootData."202003".[1].abc'

'$.RootData."202003".$.20*.abc'

Not working as it is not correct syntax I guess. Looking for right syntax. 20 is always start of that key, we can depend on that. And it is the first key always.

You must: use JSON_KEYS and get all existing keys; test the keys against your condition/pattern and filter non-matched ones; use matched keys, build according paths (using common string functions) and retrieve according values. — Akina, Dec 24 '20 at 05:14
You can use the path as `'$.RootData."202003"**.abc'`. The result will be in the form of an array. Refer - https://www.db-fiddle.com/f/6g4qiekAU4i3J8iRoAZiCA/0 — Prasanna, Dec 24 '20 at 06:02
In case you need result as an individual values, may be another extract on the result as -https://www.db-fiddle.com/f/6g4qiekAU4i3J8iRoAZiCA/1 — Prasanna, Dec 24 '20 at 06:09
@Prasanna Thanks alot for looking into it. That ** works. However when I said $[0] I am receiving the entire json back to result set. — Suresh Atta, Dec 24 '20 at 06:21
@SureshAtta, Strange. To be precise, there are 2 JSON_EXTRACTs. Inner one will use ** and the outer one will use '$[0]'. Can you replicate the issue in https://www.db-fiddle.com/f/6g4qiekAU4i3J8iRoAZiCA/1 ? — Prasanna, Dec 24 '20 at 06:33
@Prasanna My bad. Didn't notice the second json extract. Gonna try that out. Regardless, can you please add that as answer. ? — Suresh Atta, Dec 24 '20 at 06:43

score 2 · Accepted Answer · answered Dec 24 '20 at 07:18

Path in the form $.RootData."202003"**.abc should help.

Refer - https://www.db-fiddle.com/f/6g4qiekAU4i3J8iRoAZiCA/0

The result type will be array. To fetch the first match result, the result can be nested in another JSON_EXTRACT as below

select JSON_EXTRACT(JSON_EXTRACT(convert(data using utf8), '$.RootData."202003"**.abc'), '$[0]')
from rootdata;

Mysql Json Extract - wild card selection of key

1 Answers1