How to create a condition on the "array"?

Question

SQL

There is a field in the database that stores json, the field type is longtext.
The information is stored in this form:
["1", "2", "3"]

You need to somehow take the first element of the array and make a condition on it.

Answer 1

Decision:

SELECT * FROM `table` WHERE JSON_VALUE(`col`, '$[0]') > 1;

Answer 2

There is a suggestion: don't store arrays as text. Put them in a separate table, or at least in json (if your DBMS can work with it).
There is also a suggestion to read a textbook on databases, where about normal forms, indexes, etc. But you already know that, don't you?

Answer 3

> Make a condition on the first line with a number in the serialized array inside the line
Short: You can't. Or rather, it is possible, but this will have to be done through stored procedures and this will not fall on any caches. And in general it is impossible to do so for ethical reasons.
You can even come to this in a logical way:
1. The database perceives this "array" as a string. So we need to spread this array somehow - Let there be a magic stored procedure PARSE_ARRAY (str), which will return us a new plate with array elements
2. The array stores LINES, not numbers. so we need to parse them too - fortunately, many DBMS have such functions out of the box. What kind of DBMS is used is not said, so let it be PARSE_INT (str), which will return a number there.
Then the request will look something like this:

SELECT * from table_name t
where (SELECT PARSE_INT(value) > 1 FROM PARSE_ARRAY(t.array) a LIMIT 1)

But I repeat that you can’t do this - in most relational databases there is no concept of "array", and it will slow down a lot