How to fetch value from JSON document inside MySQL…

I have written following MySQL Json Array query. The Mysql version is 8.0.18-commercial

select r.network, s.server
    from table1 e
    inner join table2 s
        on e.objectId  = s.envId
    inner join resources r
        on e.objectId  = r.envId    
    inner join tpgs g
        on e.accountId  = g.objectId      
    inner join msgTable a
        on a.id  = (select max(a.id) from msgTable a where a.logId = s.AuditId)  
    ORDER BY dateSubmitted DESC ;

The output has 2 columns network and server

network                      server
---                          ---
[                           "Linux123"        
{
    "key" : "Key123",
    "name" : "RedHat 7"
}
]    

[                            "Linux234"        
{
    "key" : "Key234",
    "name" : "RedHat 8"
}
]

I want to modify the JOIN query and only fetch network.name from the table:

networkName                  server
---                          ---
RedHat 7                        "Linux123"        

RedHat 8                        "Linux234"

I have written the following JOIN but it is giving null for the networkName

select r.network->>"$.name"  as networkName, s.server
    from table1 e
    inner join table2 s
        on e.objectId  = s.envId
    inner join resources r
        on e.objectId  = r.envId    
    inner join tpgs g
        on e.accountId  = g.objectId      
    inner join msgTable a
        on a.id  = (select max(a.id) from msgTable a where a.logId = s.AuditId)  
    ORDER BY dateSubmitted DESC ;

Answer

It seems like network is an array that always contains a single object, while you are adressing it as an object.

If so, you just need to change this:

r.network->>"$.name"

To:

r.network->>"$[0].name"

How to fetch value from JSON document inside MySQL column?

Advertisement

Answer