MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test
AS
SELECT  1 AS id, '[{"name": "aman"}, {"name": "jay"}]' AS arr
UNION ALL SELECT 2,  '[{"name": "yash"}, {"name": "aman"}, {"name": "jay"}]'
UNION ALL SELECT 3,  '[{"name": "yash"}, {"name": "aman"}]'

Records: 3  Duplicates: 0  Warnings: 0


SELECT JSON_CONTAINS(arr, '{"name": "jay"}') from test WHERE id=1;


JSON_CONTAINS(arr, '{"name": "jay"}')


1


SELECT *, JSON_CONTAINS(arr, '{"name": "jay"}') from test 


id
arr
JSON_CONTAINS(arr, '{"name": "jay"}')


1
[{"name": "aman"}, {"name": "jay"}]
1

2
[{"name": "yash"}, {"name": "aman"}, {"name": "jay"}]
1

3
[{"name": "yash"}, {"name": "aman"}]
0

id	arr	JSON_CONTAINS(arr, '{"name": "jay"}')
1	[{"name": "aman"}, {"name": "jay"}]	1
2	[{"name": "yash"}, {"name": "aman"}, {"name": "jay"}]	1
3	[{"name": "yash"}, {"name": "aman"}]	0