Question 30500 in Wordpress Question posted in
The official Wordpress documentation can be found here.

How can I find value (Count) in JSON data in PHP Mysql? – WordPress

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

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

Back To Top
Search