Can't get through mysql JSON_CONTAINS to show the correcct data. Getting null everytime i try

Question

The data in the table events with column attributes is in json and looks like this -

{"event_volunteer_requests":{"1":{"volunteertypeid":250,"volunteer_needed_count":50,"cc_email":""},"2":{"volunteertypeid":249,"volunteer_needed_count":30,"cc_email":""}}}

So the context is that i want to show the events with volunteertypeid present in the attributes. There are many volunteers., this is just sample data.

I've been using the query below

SELECT
    *
FROM `events`
WHERE `zoneid` = 27 
AND `isactive` = 1 
AND JSON_CONTAINS(`attributes` -> '$.event_volunteer_requests[*].volunteertypeid', '249');

I just can't make this work and i've scorched the internet for hours. Any guidance would be appreciated.

Answer 1

Your JSON structure uses object syntax ({"key": "value"}), but you seem to want to use it as a JSON array (["value", "value", ...]).

The [*] syntax in JSON paths works for JSON arrays, not for JSON objects.

If your JSON document must be nested object syntax:

{
  "event_volunteer_requests": {
    "1": {
      "cc_email": "",
      "volunteertypeid": 250,
      "volunteer_needed_count": 50
    },
    "2": {
      "cc_email": "",
      "volunteertypeid": 249,
      "volunteer_needed_count": 30
    }
  }
}

Then you can do what you want this way:

SELECT t.id, t.volunteertypeid
FROM (
  SELECT j.id, JSON_EXTRACT(a.attributes, CONCAT('$.event_volunteer_requests."', j.id, '".volunteertypeid')) AS volunteertypeid
  FROM (SELECT '{"event_volunteer_requests":{"1":{"volunteertypeid":250,"volunteer_needed_count":50,"cc_email":""},"2":{"volunteertypeid":249,"volunteer_needed_count":30,"cc_email":""}}}' as attributes) AS a
  CROSS JOIN JSON_TABLE(JSON_KEYS(a.attributes, '$.event_volunteer_requests'), '$[*]' COLUMNS (id INT PATH '$')) AS j
) AS t
WHERE t.volunteertypeid = 249;

Result:

+------+-----------------+
| id   | volunteertypeid |
+------+-----------------+
|    2 | 249             |
+------+-----------------+

The JSON_TABLE() function requires MySQL 8.0. If you use MySQL 5.7, you must upgrade.