Mysql Question posted in
The official MySQL documentation can be found here.

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?

Tags:

2

Answers


  1. 0

    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(*).

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search