How to fetch value from JSON document inside MySQL column?

Question

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 ;

GMB · Accepted Answer · 2020-04-08 16:42:18Z

1

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"

answered Apr 8, 2020 at 16:42

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to fetch value from JSON document inside MySQL column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related