mysql - JSON_CONTAINS 用于数组中对象的多个属性？

Question

目前使用 MYSQL 5.7，即将迁移到 8.0。
我们的数据库中有一个 json 字段，其值如下所示:

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

我正在尝试编写一个查询，该查询在数组中查找包含“first_name”=“bob”和“last_name”=“rippleman”的对象的记录

是否 JSON_CONTAINS支持这样的搜索吗？

Answer 1

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 和 MySQL 8.0 尝试:

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

见 dbfiddle .

MySQL 8.0 试试:

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

见 dbfiddle .