MySQL 8.0

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

CREATE TABLE users ( name VARCHAR(255), last VARCHAR(255), data JSON);
INSERT INTO users VALUES
( 'john' , 'doe'  , '{"acctNum": "123"}'            ),
( 'john' , 'doe'  , '{"data":{ "acctNum": "123" }}' ),
( 'jane' , 'doe'  , '{"data":{ "acctNum": "1234" }}'),
( 'jane' , 'doe'  , '{"data":{ "acctNum": "1234", "acctRel": "123" }}');
SELECT * FROM users;


name
last
data


john
doe
{"acctNum": "123"}

john
doe
{"data": {"acctNum": "123"}}

jane
doe
{"data": {"acctNum": "1234"}}

jane
doe
{"data": {"acctNum": "1234", "acctRel": "123"}}


SELECT *, JSON_SEARCH(data, 'all', 123)
FROM users;


name
last
data
JSON_SEARCH(data, 'all', 123)


john
doe
{"acctNum": "123"}
"$.acctNum"

john
doe
{"data": {"acctNum": "123"}}
"$.data.acctNum"

jane
doe
{"data": {"acctNum": "1234"}}
null

jane
doe
{"data": {"acctNum": "1234", "acctRel": "123"}}
"$.data.acctRel"


SELECT *
FROM users
WHERE JSON_SEARCH(data, 'one', 123) = '$.acctNum';


name
last
data


john
doe
{"acctNum": "123"}


SELECT *
FROM users
WHERE JSON_SEARCH(data, 'one', 123) = '$.data.acctNum';


name
last
data


john
doe
{"data": {"acctNum": "123"}}


SELECT *
FROM users
WHERE JSON_SEARCH(data, 'one', 123);


name
last
data


john
doe
{"acctNum": "123"}

john
doe
{"data": {"acctNum": "123"}}

jane
doe
{"data": {"acctNum": "1234", "acctRel": "123"}}


SELECT *
FROM users
WHERE JSON_SEARCH(data, 'all', 123) LIKE '%.acctNum"%';


name
last
data


john
doe
{"acctNum": "123"}

john
doe
{"data": {"acctNum": "123"}}

name	last	data
john	doe	{"acctNum": "123"}
john	doe	{"data": {"acctNum": "123"}}
jane	doe	{"data": {"acctNum": "1234"}}
jane	doe	{"data": {"acctNum": "1234", "acctRel": "123"}}

name	last	data	JSON_SEARCH(data, 'all', 123)
john	doe	{"acctNum": "123"}	"$.acctNum"
john	doe	{"data": {"acctNum": "123"}}	"$.data.acctNum"
jane	doe	{"data": {"acctNum": "1234"}}	null
jane	doe	{"data": {"acctNum": "1234", "acctRel": "123"}}	"$.data.acctRel"