To get partial array values in MySql json type

Question

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.

Answer 1

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]