I currently have this JSONs in my database

And I want something like

SELECT * 
FROM user_info 
WHERE count(subscriptions) == 2

(select by count of elements in "subscriptions" key of JSON)

I've tried with JSON_SEARCH, but didn't managed to get something to work

CodePudding user response：

I think your JSON format that you saved in DB is not proper JSON, Please validate the string in JSON validator online. Please check below screenshot

Correct JSON format should be

{"subscriptions": [{"user": 1}, {"user": 2}]}

Based on the JSON format provided below is the sql query for your requirement

SELECT * FROM user_info WHERE info is not null and JSON_LENGTH(JSON_EXTRACT(info, '$.subscriptions')) = 2

If above suggested format is not suitable for you, then use below:

[{"subscriptions": [{"user": 1}, {"user": 2}]}]

Then your query will be:

SELECT * FROM user_info WHERE info is not null and JSON_LENGTH(info->'$[0].subscriptions') = 2

When you use any JSON format, make sure it is a valid format by using json validators,

CodePudding user response：

You can count the number of occurrences of the key user by getting the difference between the CHAR_LENGTH of the info column and the CHAR_LENGTH of the info column (again) but this time, using REPLACE to replace any occurrence of user with the LENGTH of user using SPACE. The difference will essentially be the occurrence count of the static value user.

SELECT 
    user,
    info,   
    COALESCE(CHAR_LENGTH(info) - CHAR_LENGTH(REPLACE(info, 'user', SPACE(LENGTH('user')-1))), 0) AS user_count   
FROM user_info

Result:

db<>fiddle here.

Otherwise, you need to fix your JSON formatting in your array column to use the JSON functionality in MySQL.