I have a table with singers and each singer can be linked to many musical styles.
Musical Styles are in a TEXT column, but the content of the column has JSON format.
I want to count the number of occurences of each musical style.

Here is a sample of my table :

For example, the column elements includes :

{
    "ffcc1c50-8dbd-4115-b463-b43bdcd44a57": {
        "file": "images/juke-box/artistes/elvis-presley-1.jpg",
        "title": "",
        "width": 250,
        "height": 250
    },

    "2276b834-35d0-4ef0-a747-106ea454e478": {
        "option": {
            "0": "Gospel",
            "1": "Rock'N Roll"
        },
        "check": "1"
    }

}

or for next row :

{
    "ffcc1c50-8dbd-4115-b463-b43bdcd44a57": {
        "file": "images/juke-box/artistes/freddie-mercury-1.jpg",
        "title": "",
        "width": 250,
        "height": 250
    },

    "2276b834-35d0-4ef0-a747-106ea454e478": {
        "option": {
            "0": "Gospel",
            "1": "Opera"
        },
        "check": "1"
    }

}

In the example above, Gospel can appear on many artists.

I want to count the number of occurences for each musical style in the whole table.

If I create this query it’s very approximative because it’s not limited to the “2276b834-35d0-4ef0-a747-106ea454e478” argument.

SELECT (SELECT COUNT(*) FROM `rwm_zoo_item` WHERE `elements` LIKE '%"Drum And bass"%') AS "Drum And bass", (SELECT COUNT(*) FROM `rwm_zoo_item` WHERE `elements` LIKE '%"House"%') AS "HOUSE", (SELECT COUNT(*) FROM `rwm_zoo_item` WHERE `elements` LIKE '%"Pop-Rock"%') AS "Pop Rock", (SELECT COUNT(*) FROM `rwm_zoo_item` WHERE `elements` LIKE '%"Gospel"%') AS "Gospel";