Search code examples
mysqlarraysmysql-json

To get partial array values in MySql json type


When [1,2,3,4,5] is stored in Json Type in MySql with column 'jscol' and table 'Jtest', How to get partial values above 2? I couldn't find the right query.

I think some combination of json functions in mysql 8 could solve the problem.


Solution

  • it will be better if you can provide sample table.

    for your case you may use below query

    --Create TABLE Jtest
    CREATE TABLE Jtest (
        id INT AUTO_INCREMENT PRIMARY KEY,
        jscol JSON NOT NULL
    );
    
    --Insert dummy data
    INSERT INTO Jtest(jscol) VALUES ('[1,2,3,4,5]');
    
    
    -- Query to get values above 2
    SELECT value
    FROM Jtest,
         JSON_TABLE(jscol, '$[*]' 
                    COLUMNS(
                        value INT PATH '$'
                    )
         ) AS jt
    WHERE jt.value > 2;
    

    This query does the following:

    • The JSON_TABLE() function is used to parse the JSON array stored in the jscol column.
    • The path '$[*]' is used to specify that we want to iterate over each element in the JSON array.
    • Inside the COLUMNS clause, we define a column named value that extracts each value from the JSON array.
    • Finally, we filter the results using a WHERE clause to get only values greater than 2.
    • When you run the above query, it will return the values 3, 4, and 5 from the JSON array [1,2,3,4,5]