JSON_CONTAINS for multiple attributes on object in array?

Question

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?

@GMB - Added info on version in description. MYSQL 5.7 now, hoping to move to 8.0 soon. — bpeikes
– bpeikes, Commented Mar 3, 2020 at 3:38

wchiquito · Accepted Answer · 2020-03-05 20:16:46Z

2

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.

edited Mar 5, 2020 at 20:16

answered Mar 5, 2020 at 18:30

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JSON_CONTAINS for multiple attributes on object in array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related