I'm new to MySql queries:

Below is what I'm having issues with.

So I'm saving json_data as: I'm using PHP json_encode to convert the array into JSON formate and store it directly in the data column.

I just want to get the total no of users (COUNT) that have the value 123 in their Data Column.

Thanks in advance.

CodePudding user response：

I'm using PHP json_encode to convert the array into JSON formate and store it directly in the data column.

You may use JSON_SEARCH() function. But it searches for string-type data only. Hence for to create effective query you must store the values into your JSON as strings, not as numbers.

CREATE TABLE test (id INT, data JSON)
SELECT 1 id,  '{"abc": "123", "947": "234", "874": "123"}' data 
UNION ALL
SELECT 2, '{"abc": "369", "659": "123", "523": "123"}'
UNION ALL
SELECT 2, '{"abc": "369", "659": "456", "523": "567"}';

SELECT id, JSON_LENGTH(JSON_SEARCH(data, 'all', '123')) AS values_amount
FROM test;

id | values_amount
-: | ------------:
 1 |             2
 2 |             2
 2 |          null

SELECT id
FROM test
WHERE JSON_LENGTH(JSON_SEARCH(data, 'all', '123'));

| id |
| -: |
|  1 |
|  2 |

SELECT id, JSON_LENGTH(JSON_SEARCH(data, 'all', '123')) AS values_amount
FROM test
HAVING values_amount;

id | values_amount
-: | ------------:
 1 |             2
 2 |             2

db<>fiddle here

CodePudding user response：

you can try it also by PHP after you made your sql query, you get you object $YOUR_USER_SQL_ARRAY

foreach($YOUR_USER_SQL_ARRAY as $userID => $userValues){

$userValuesdecoded = json_decode($userValues)
$counter  ;

foreach($userValuesdecoded as $key => $value){

if($value == 123) $counter  ;

}
}

this way you can get the number of 123 for all users, even if there is more than "123" for each user