CODEWITHSUNDEEP
mysqlmysql-json
JaneYe
JaneYe

Reputation: 1

How to count certain values in a JSON array in MySQL?

The JSON data is as follows:

enter image description here

I would like to count how many times each element in a collection, such as {"3467562849402896", "3467562861985809", "3465044211793921"}, has been used. The number of elements in the collection is not fixed and is an input parameter.

I am aware that JSON_TABLE in MySQL 8 can be used, but my version is 5.7. One approach I thought of is to first use the following SQL to find out who used this ID, and then handle the frequency count in the code.

where JSON_CONTAINS(tags ,'"3467562849402896"' , '$')
    OR JSON_CONTAINS(tags, '"3467562861985809"', '$')
    OR JSON_CONTAINS(tags, '"3465044211793921"', '$');

Is there a better solution to this issue?

Upvotes: 1

Views: 86

Answers (2)

Bernd Buffen
Bernd Buffen

Reputation: 15057

Here is a way you can do that easily.

convert tags to rows

select id, j.singletag from tagsample 
cross join json_table(tag, '$[*][*]' columns ( 
  singletag int(64) path '$')) as j;

count entrys

  select count(j.singletag) as cnt, j.singletag as tag from tagsample 
cross join json_table(tag, '$[*][*]' columns ( 
  singletag int(64) path '$')) as j
GROUP BY j.singletag
ORDER by count(j.singletag) DESC, j.singletag;

sample

create table

CREATE TABLE `tagsample` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `tag` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

insert data

INSERT INTO `tagsample` (`id`, `tag`)
VALUES
    (1, '[[1232, 5678], [12321, 52678], [1232, 15678], [11232, 5678], [998877]]'),
    (2, '[[1232, 5678], [12321, 252678], [1232, 15678], [11232, 25678]]'),
    (3, '[[998877]]');

run first query - tags to rows

select id, j.singletag from tagsample 
    cross join json_table(tag, '$[*][*]' columns ( 
      singletag int(64) path '$')) as j;

1   1232
1   5678
1   12321
1   52678
1   1232
1   15678
1   11232
1   5678
1   998877
2   1232
2   5678
2   12321
2   252678
2   1232
2   15678
2   11232
2   25678
3   998877

run second query - count duplicates

select count(j.singletag) as cnt, j.singletag as tag from tagsample 
    cross join json_table(tag, '$[*][*]' columns ( 
      singletag int(64) path '$')) as j
    GROUP BY j.singletag
    ORDER by count(j.singletag) DESC, j.singletag;

4   1232
3   5678
2   11232
2   12321
2   15678
2   998877
1   25678
1   52678
1   252678

sample : https://dbfiddle.uk/o5Pj2J0i

change the line singletag int(64) path '$')) as j to your datatype ie. VARCHAR

Note: the Sample is for MySQL Version 8

Upvotes: 1

Akina
Akina

Reputation: 42764

Schematically:

SELECT table_with_JSON_array.id,
       COUNT(values_to_test_against.one_value) matches_found
FROM table_with_JSON_array
LEFT JOIN ( SELECT 'value 1' UNION ALL
            SELECT 'value 2' UNION ALL
         -- ....
            SELECT 'value N-1' UNION ALL
            SELECT 'value N' 
            ) values_to_test_against (one_value) 
     ON values_to_test_against.one_value MEMBER OF table_with_JSON_array.JSON_column
GROUP BY table_with_JSON_array.id

if you need only in those rows which contains at least one matched value then use INNER JOIN and COUNT(*).

Upvotes: 2

Related Questions