CODEWITHSUNDEEP
mysqlsqlarraysjson
bpeikes
bpeikes

Reputation: 3695

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?

Upvotes: 2

Views: 2473

Answers (1)

wchiquito
wchiquito

Reputation: 16551

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.

Upvotes: 2

Related Questions