JSON_CONTAINS for multiple attributes on object in array?

Currently using MYSQL 5.7, soon to be migrating to 8.0. We have a json field in our database which has values which look like this:

[
  {
     "first_name":"bob",
     "last_name":"lisserman"
  },
  {
     "first_name":"bob",
     "last_name":"rippleman"
  },
  {
     "first_name":"joe",
     "last_name":"roterlam"
  }
]

I’m trying to write a query which finds records which contain an object in the array with both “first_name” = “bob” AND “last_name” = “rippleman”

Does JSON_CONTAINS support a search like this?

Answer

12.17.3 Functions That Search JSON Values :: JSON_CONTAINS(target, candidate[, path]):

…

• A candidate object is contained in a target object if and only if for each key in the candidate there is a key with the same name in the target and the value associated with the candidate key is contained in the value associated with the target key.

…

MySQL 5.7 and MySQL 8.0 try:

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

See dbfiddle.

MySQL 8.0 try:

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';

See dbfiddle.