1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Ripudaman Singh
  • 13
  • 5

1 Answers1

0

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Hi bill, I must say i never thought about doing it this way. I was trying with JSON_EXTRACT(`attributes`, '$**.event_volunteer_requests[*].volunteertypeid') AS volunteertypeid but that "1": and "2": were too much problem. I tried your solution and it works flawlessly in the sql but php is throwing an error on double quotes in query. I tried to concat it with comma separated single quotes but can't get it to work. Also want to know, is this good approach on huge databases? – Ripudaman Singh Feb 06 '23 at 17:14
  • If you have to set strings in PHP that contain double-quotes I would recommend you learn how to use [heredoc syntax](https://www.php.net/manual/en/language.types.string.php#language.types.string.syntax.heredoc). – Bill Karwin Feb 06 '23 at 17:17
  • As for using JSON — no, I really don't like seeing JSON in MySQL. I think it's a bad feature because so many people abuse it. Storing and fetching JSON is fine, but the trouble comes when people try to use the fields inside the JSON document as if they are normal rows and columns. I say you should reference your JSON column only in the select-list of a query. – Bill Karwin Feb 06 '23 at 17:20
  • Unfortunately i can't change this structure now because it's going in production soon. This has been great learning! Also, the error i am facing is with ."', j.id, '". these double quotes. Using heredoc in sql syntax will look very messy i guess? – Ripudaman Singh Feb 06 '23 at 17:26
  • Oh I'm sorry I misunderstood. Please disregard my suggestion about heredoc. You're talking about the CONCAT to form JSON paths. Yes, that's bound to be messy. But that's the consequence of using JSON. Sorry. – Bill Karwin Feb 06 '23 at 17:29
  • 1
    Hey bill, just an update - I found out that the index integers in the JSON (1:, 2: ) were actually due to bad data encoding by last devs. So i used json_extract from your fix and all worked well!! Now the next challenge was to remove the these indexes from whole DB which i guess was not possible through mysql. Creating migrations in php now. Thanks a lot for your help man! – Ripudaman Singh Feb 07 '23 at 12:07