I have a table with data we've collected from various sources. It's got the item name, the source and the source's value.
The table looks like the following:
| id | name | source | value |
|---|---|---|---|
| 1 | abc | web | 1 |
| 2 | abc | 2 | |
| 3 | xyz | 8 | |
| 4 | xyz | web | 9 |
Using the JSON_OBJECTAGG() function, I can group the sources together based on the name:
SELECT name, JSON_OBJECTAGG(source, value) AS data FROM testStuff GROUP BY name This correctly gives:
| name | data |
|---|---|
| abc | {"web":1, "print": 2} |
| xyz | {"print":8, "web": 9} |
Now, I want to run that query, but with a filter on the price/source, so I ran the following query:
SELECT name, JSON_OBJECTAGG(source, value) AS data FROM testStuff GROUP BY name HAVING JSON_CONTAINS(data, 2, '$.print') This seems to work, but there's a slight issue:
| name | data |
|---|---|
| abc | {"web":1, "print": 2}} |
Why is there suddenly an extra } at the end of the JSON data? The HAVING clause is correctly filtering the rows, but why does it modify the result data?
Online demo: https://sqlfiddle.com/mariadb/online-compiler?id=ccf1041c-3ada-4fc9-bd77-2b0fe1428392
I'm not sure what version of MariaDB that site uses, but testing this on my local computer using MariaDB 10.11.9 gives the same results.
EDIT: It seems I can fix this by using:
SELECT name, JSON_OBJECTAGG(source, value) AS data FROM testStuff GROUP BY name HAVING JSON_CONTAINS(JSON_OBJECTAGG(source, value), 2, '$.print') I just was trying to avoid writing the JSON_OBJECTAGG() multiple times. Also, while this fixes the issue, I still am curious as to why this was an issue in the first place.
EDIT 2: Another "fix" is to replace JSON_OBJECTAGG() with GROUP_CONCAT().
The following returns the correct data:
SELECT name, REPLACE(GROUP_CONCAT(JSON_OBJECT(source, value)), '},{', ',') AS data FROM testStuff GROUP BY name HAVING JSON_CONTAINS(data, 2, '$.print') 