You can reference a JSON column using the col->>path operator in the WHERE clause:
mysql> SELECT emp_no FROM emp_details WHERE details->>'$.address.pin'="560103";+--------+| emp_no |+--------+| 1 |+--------+1 row in set (0.00 sec)
You can also use the JSON_CONTAINS function to search for data. It returns 1 if the data is found and 0 otherwise:
mysql> SELECT JSON_CONTAINS(details->>'$.address.pin', "560103") FROM emp_details;+----------------------------------------------------+| JSON_CONTAINS(details->>'$.address.pin', "560103") |+----------------------------------------------------+| 1 |+----------------------------------------------------+1 row in set (0.00 sec)
How to search for a key? Suppose you want to check whether address.line1 ...