How to compare JSON-like text in MySQL 5.6?

Question

DBMS: MySQL 5.6

I have a table tblin which a column jsonstores JSON-like text, a column type text. The column jsonlooks like

{"id": "123", "name": "foo", "age": "20"}

---

I tried to select lines with a condition json.id = '123'. Failed to complete the request select * from tbl where json like '%"id": "123"%'.

I found that MySQL 5.6 does not support Json functions. So how to use Json in a sentence WHERE?

---

Append

The layout in which JSON is stored in a single column is definitely not that smart. But I can’t change the scheme, as the business has been working for some time. The MySQL version is not a concern. Therefore, I think a workaround is needed.

Answer 1

select * from tbl where json like '%\"id\": \"123\"%'

. ".

Answer 2

LOCATE

Select * from tbl where 
LOCATE('"id": "123"','{"id": "123", "name": "foo", "age": "20"}') >0

Answer 3

Try this, common_schema is used to parse json,

SELECT json 
    FROM tbl
    WHERE common_schema.extract_json_value(json ,'id')
    LIKE "123%"