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

MySQL select by count of elements in JSON array

I currently have this JSONs in my database

user info
0 ["subscriptions": [{"user": 1}, {"user": 2}]]
1 []
2 null

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

Tags:

2

Answers


  1. 0

    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

    enter image description here

    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,

    Login or Signup to reply.
  2. 0

    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:

    user info user_count
    0 ["subscriptions": [{"user": 1}, {"user": 2}]] 2
    1 [] 0
    2 null 0
    3 ["subscriptions": [{"user": 1}, {"user": 2}, {"user": 3}]] 3
    4 ["subscriptions": [{"user": 1}, {"user": 2} , {"user": 3}, {"user": 4}]] 4

    db<>fiddle here.

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

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

Back To Top
Search