How to count values from a column with JSON content?

S233bastienMerour
July 21, 2024
192 views
0 votes
2 Answers

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 :

id	Name	elements
1	Elvis Presley	(see code block below)
2	Freddie Mercury	(see code block below)

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";

Answers

- PaulProgrammer
- July 20, 2024 at 8:54 pm
- 0 votes
0
You’ll have to iterate through the objects, and find the "option" values, and aggregate based on the found values. It might look something like this:
```
const jukebox = {} // your object
const genreCount = {} // the result

Object.keys(jukebox).forEach((key) => {
  if( !(key.option)) { return; } // no "option" structure here!

  Object.values(key.option).forEach((genre) => {
    if( !genreCount[genre] ) { genreCount[genre] = 0; } // seed initial value
    genreCount[genre] = genreCount[genre]+1;
  });
});

console.log(genreCount);
```
Login or Signup to reply.

Oracle:
One option to do it is to define music styles and join them to the data extracted from json using JSON_TABLE().

WITH   -- cte defining styles
  styles (STYLE) AS     
      ( Select 'Gospel' From Dual Union All 
        Select 'Rock''N Roll' From Dual Union All 
        Select 'Opera' From Dual Union All 
        Select 'House' From Dual Union All 
        Select 'Punk' From Dual 
      ),
--  S a m p l e    D a t a :
  tbl (ID, A_NAME, ELEMS) AS
( Select 1, 'Elvis Presley', 
         JSON_SERIALIZE( '{
        "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"
        }
    }' RETURNING CLOB)
  From Dual Union All 
  --
  Select 2, 'Freddie Mercury', 
         JSON_SERIALIZE( '{
        "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"
        }

    }' RETURNING CLOB)
  From Dual
)

--    M a i n    S Q L :
SELECT      s.STYLE, Count(Case When j.A_NAME Is Not Null Then 1 End) "CNT_STYLE"
FROM        styles s
LEFT JOIN   ( Select   t.ID, t.A_NAME, j.*  
              From     tbl t, 
                       JSON_TABLE( t.ELEMS, '$."2276b834-35d0-4ef0-a747-106ea454e478".option'
                               COLUMNS ( STYLE_0        VARCHAR2(64)   PATH '$."0"', 
                                         STYLE_1        VARCHAR2(64)   PATH '$."1"', 
                                         STYLE_2        VARCHAR2(64)   PATH '$."2"',
                                         STYLE_3        VARCHAR2(64)   PATH '$."3"', 
                                         STYLE_4        VARCHAR2(64)   PATH '$."4"'
                                       )
                             ) j
            ) j ON( s.STYLE IN( j.STYLE_0, j.STYLE_1, j.STYLE_2, j.STYLE_3, j.STYLE_4 ) )
GROUP BY    s.STYLE
ORDER BY    s.STYLE
/*   R e s u l t :
STYLE        CNT_STYLE
----------- ----------
Gospel               2
House                0
Opera                1
Punk                 0
Rock'N Roll          1   */

Please signup or login to give your own answer.

Click here to cancel reply.