How to list every JSON Array element separately within MySQL Query?

Question

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

select 
networkInfo->>"$.*" as network,
servers->>"$[*]" as server
from table1
where id = 56;

The output has 2 columns network and server of JSON type

network              server
---                  ---
[                    [
    "Linux123",          "Server123",
    "RHEL",              "Server1231",
    "abc.com"            "Server1232"
]                    ]

I want to modify the SELECT query such that the output has a separate row for every server:

network                      server
---                          ----  
[                             Server123
    "Linux123",
    "RHEL",
    "abc.com"
]

[                             Server1231
    "Linux123",
    "RHEL",
    "abc.com"
]

[                             Server1232
    "Linux123",
    "RHEL",
    "abc.com"
]

Nick · Accepted Answer · 2020-03-31 01:04:11Z

1

You can use JSON_TABLE to extract the different server values from servers, then CROSS JOIN that to the networks value for the given id:

SELECT t1.networkInfo->>"$.*" AS network, j.server
FROM table1 t1
CROSS JOIN JSON_TABLE(t1.servers, 
                      "$[*]" COLUMNS (
                        server VARCHAR(20) PATH '$'
                        )
                      ) j
WHERE t1.id = 56

Output (I've had to hack up a demo based on an expectation of table structure):

network                             server
["abc.com", "Linux123", "RHEL"]     Server123
["abc.com", "Linux123", "RHEL"]     Server1231
["abc.com", "Linux123", "RHEL"]     Server1232

answered Mar 31, 2020 at 1:04

Nick

147k23 gold badges67 silver badges106 bronze badges

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

Collectives™ on Stack Overflow

How to list every JSON Array element separately within MySQL Query?

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