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.
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: