MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- sample data

CREATE TABLE IF NOT EXISTS `Centers` (
`id` int(11) NOT NULL ,
`name` VARCHAR(255)  NOT NULL ,
`teams` JSON NOT NULL 
);

INSERT INTO `Centers`  (`id`, `name`, `teams`) VALUES
('1','Texas','{"teams0":{"name":"Alex","training":"worker","agenda":"https:\/\/example.com\/booking\/alex","terminland_days":"0"},"teams1":{"name":"thomas","training":"worker","agenda":"https:\/\/example.com\/booking\/thomas","terminland_days":"0"}}'),
('2','California','{"teams0":{"name":"Dean","training":"worker","agenda":"https:\/\/example.com\/booking\/dean","terminland_days":"0"},"teams1":{"name":"Rose","training":"worker","agenda":"https:\/\/example.com\/booking\/rose","terminland_days":"0"}}'),
('3','Alaska','{"teams2":{"name":"John","training":"worker","agenda":"https:\/\/example.com\/booking\/john","terminland_days":"0"}}');

SELECT id, name, JSON_PRETTY(teams)
FROM Centers;


id
name
JSON_PRETTY(teams)


1
Texas
{
  "teams0": {
    "name": "Alex",
    "agenda": "https://example.com/booking/alex",
    "training": "worker",
    "terminland_days": "0"
  },
  "teams1": {
    "name": "thomas",
    "agenda": "https://example.com/booking/thomas",
    "training": "worker",
    "terminland_days": "0"
  }
}

2
California
{
  "teams0": {
    "name": "Dean",
    "agenda": "https://example.com/booking/dean",
    "training": "worker",
    "terminland_days": "0"
  },
  "teams1": {
    "name": "Rose",
    "agenda": "https://example.com/booking/rose",
    "training": "worker",
    "terminland_days": "0"
  }
}

3
Alaska
{
  "teams2": {
    "name": "John",
    "agenda": "https://example.com/booking/john",
    "training": "worker",
    "terminland_days": "0"
  }
}


-- get keys array

SELECT id, JSON_KEYS(teams)
FROM Centers;


id
JSON_KEYS(teams)


1
["teams0", "teams1"]

2
["teams0", "teams1"]

3
["teams2"]


-- gets specific keys (up to 10 objects per value)

SELECT id, CAST(JSON_EXTRACT(JSON_KEYS(teams), CONCAT('$[', n, ']')) AS CHAR) object_name
FROM Centers
JOIN ( SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
       SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) numbers
     ON n < JSON_LENGTH(JSON_KEYS(teams))


id
object_name


1
"teams0"

2
"teams0"

3
"teams2"

1
"teams1"

2
"teams1"


-- parse JSON

SELECT id, 
       name, 
       CAST(object_name AS CHAR) object_name,
       CAST(JSON_EXTRACT(teams, CONCAT('$.', object_name, '.name')) AS CHAR) name, 
       CAST(JSON_EXTRACT(teams, CONCAT('$.', object_name, '.agenda')) AS CHAR) agenda, 
       CAST(JSON_EXTRACT(teams, CONCAT('$.', object_name, '.training')) AS CHAR) training, 
       CAST(JSON_EXTRACT(teams, CONCAT('$.', object_name, '.terminland_days')) AS CHAR) terminland_days
FROM Centers
JOIN ( SELECT id, JSON_EXTRACT(JSON_KEYS(teams), CONCAT('$[', n, ']')) object_name
       FROM Centers
       JOIN ( SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
              SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) numbers
            ON n < JSON_LENGTH(JSON_KEYS(teams))) paths USING (id)
ORDER BY id


id
name
object_name
name
agenda
training
terminland_days


1
Texas
"teams0"
"Alex"
"https://example.com/booking/alex"
"worker"
"0"

1
Texas
"teams1"
"thomas"
"https://example.com/booking/thomas"
"worker"
"0"

2
California
"teams0"
"Dean"
"https://example.com/booking/dean"
"worker"
"0"

2
California
"teams1"
"Rose"
"https://example.com/booking/rose"
"worker"
"0"

3
Alaska
"teams2"
"John"
"https://example.com/booking/john"
"worker"
"0"

id	name	JSON_PRETTY(teams)
1	Texas	{ "teams0": { "name": "Alex", "agenda": "https://example.com/booking/alex", "training": "worker", "terminland_days": "0" }, "teams1": { "name": "thomas", "agenda": "https://example.com/booking/thomas", "training": "worker", "terminland_days": "0" } }
2	California	{ "teams0": { "name": "Dean", "agenda": "https://example.com/booking/dean", "training": "worker", "terminland_days": "0" }, "teams1": { "name": "Rose", "agenda": "https://example.com/booking/rose", "training": "worker", "terminland_days": "0" } }
3	Alaska	{ "teams2": { "name": "John", "agenda": "https://example.com/booking/john", "training": "worker", "terminland_days": "0" } }

id	name	object_name	name	agenda	training	terminland_days
1	Texas	"teams0"	"Alex"	"https://example.com/booking/alex"	"worker"	"0"
1	Texas	"teams1"	"thomas"	"https://example.com/booking/thomas"	"worker"	"0"
2	California	"teams0"	"Dean"	"https://example.com/booking/dean"	"worker"	"0"
2	California	"teams1"	"Rose"	"https://example.com/booking/rose"	"worker"	"0"
3	Alaska	"teams2"	"John"	"https://example.com/booking/john"	"worker"	"0"