Search code examples
mysqlsqljsonjson-extract

Mysql Json Extract - wild card selection of key

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.

Solution

  • 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;