[FIXED] How can I find value (Count) in JSON data in PHP Mysql?

Issue

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.

id	data
1	{"abc": 123, "947": 234, "874": 123}
2	{"abc": 369, "659": 123, "523": 123}

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

Thanks in advance.

Solution

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

Answered By - Akina