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"}} |