how to perform a SELECT Statement on a JSON column in mysql/mariaDB

how to apply WHERE clause on JSON column to perform a SELECT query on a table which is having two columns (id Integer, collection JSON). I am trying to look for specific integers in the collection column, like 223447 or 235073.

I tried to query 223447 (MariaDB 10.4.31) with:

SELECT
    *
FROM
    `wp_collection_data`
WHERE
    JSON_CONTAINS(id, '"223447"', '$.i');

and

SELECT 
    * 
FROM 
    `wp_collection_data`
WHERE 
    JSON_EXTRACT( id, '$.i' ) = 223447

and got:

Warning: #4038 Syntax error in JSON text in argument 1 to function 'json_contains' at position 1

I think the "a:2:" in front of the json is causing this issue.

Can I edit the where clause to use the mariadb JSON Functions?

Is it possible to query an integer by searching all integers between the characters ":" and ";"?

The collection column does not contains a valid json.

here is a way to filter your data :

SELECT
    *
FROM
    `wp_collection_data`
WHERE
    collection like '%i:223447;%'