MySQL 8.0

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

SELECT VERSION();


VERSION()


8.0.36


DROP TABLE IF EXISTS `tbl_table`;

CREATE TABLE IF NOT EXISTS `tbl_table` (
  `id` SERIAL,
  `json_field` JSON
);

INSERT INTO `tbl_table`
  (`json_field`)
VALUES
  ('[
      {
        "first_name":"bob",
        "last_name":"lisserman"
      },
      {
        "first_name":"bob",
        "last_name":"rippleman"
      },
      {
        "first_name":"joe",
        "last_name":"roterlam"
      }
  ]'),
  ('[
      {
        "first_name":"bob",
        "last_name":"lisserman"
      },
      {
        "first_name":"bob",
        "last_name":"NOrippleman"
      },
      {
        "first_name":"joe",
        "last_name":"roterlam"
      }
  ]'),
  ('[
      {
        "first_name":"bob",
        "last_name":"lisserman"
      },
      {
        "first_name":"bob",
        "last_name":"rippleman"
      },
      {
        "first_name":"joe",
        "last_name":"roterlam"
      }
  ]');

Records: 3  Duplicates: 0  Warnings: 0


SELECT
  `id`,
  `json_field`
FROM
  `tbl_table`
WHERE
  JSON_CONTAINS(`json_field`, '{"first_name": "bob", "last_name": "rippleman"}');


id
json_field


1
5b7b226c6173745f6e616d65223a20226c69737365726d616e222c202266697273745f6e616d65223a2022626f62227d2c207b226c6173745f6e616d65223a2022726970706c656d616e222c202266697273745f6e616d65223a2022626f62227d2c207b226c6173745f6e616d65223a2022726f7465726c616d222c202266697273745f6e616d65223a20226a6f65227d5d

3
5b7b226c6173745f6e616d65223a20226c69737365726d616e222c202266697273745f6e616d65223a2022626f62227d2c207b226c6173745f6e616d65223a2022726970706c656d616e222c202266697273745f6e616d65223a2022626f62227d2c207b226c6173745f6e616d65223a2022726f7465726c616d222c202266697273745f6e616d65223a20226a6f65227d5d


SELECT
  `tbl_table`.`id`,
  `tbl_table`.`json_field`
FROM
  `tbl_table`, JSON_TABLE(
    `json_field`,
    '$[*]'
    COLUMNS(
      `first_name` VARCHAR(50) PATH '$.first_name',
      `last_name` VARCHAR(50) PATH '$.last_name'
    )
) `der`
WHERE
  `der`.`first_name` = 'bob' AND
  `der`.`last_name` = 'rippleman';


id
json_field


1
5b7b226c6173745f6e616d65223a20226c69737365726d616e222c202266697273745f6e616d65223a2022626f62227d2c207b226c6173745f6e616d65223a2022726970706c656d616e222c202266697273745f6e616d65223a2022626f62227d2c207b226c6173745f6e616d65223a2022726f7465726c616d222c202266697273745f6e616d65223a20226a6f65227d5d

3
5b7b226c6173745f6e616d65223a20226c69737365726d616e222c202266697273745f6e616d65223a2022626f62227d2c207b226c6173745f6e616d65223a2022726970706c656d616e222c202266697273745f6e616d65223a2022626f62227d2c207b226c6173745f6e616d65223a2022726f7465726c616d222c202266697273745f6e616d65223a20226a6f65227d5d

id	json_field
1	5b7b226c6173745f6e616d65223a20226c69737365726d616e222c202266697273745f6e616d65223a2022626f62227d2c207b226c6173745f6e616d65223a2022726970706c656d616e222c202266697273745f6e616d65223a2022626f62227d2c207b226c6173745f6e616d65223a2022726f7465726c616d222c202266697273745f6e616d65223a20226a6f65227d5d
3	5b7b226c6173745f6e616d65223a20226c69737365726d616e222c202266697273745f6e616d65223a2022626f62227d2c207b226c6173745f6e616d65223a2022726970706c656d616e222c202266697273745f6e616d65223a2022626f62227d2c207b226c6173745f6e616d65223a2022726f7465726c616d222c202266697273745f6e616d65223a20226a6f65227d5d