MariaDB JOIN based on JSON array

Question

I'm pretty sure this can be done, but after searching MariaDB docs, I'm not sure how.

I would like to return all items based on a join_extract search (I think).

These are the values to return from the vendor_certs table:

+----+-------------------+
| id | cert              |
+----+-------------------+
|  1 | Organic           |
|  2 | Kosher            |
|  5 | BRC               |
|  6 | SQF               |
|  7 | Halal             |
|  8 | ISO               |
|  9 | EPA               |
| 10 | NSF               |
| 11 | Walmart Certified |
| 12 | Target Certified  |
| 13 | FDA               |
+----+-------------------+

Based on a SELECT from the vendor_key_value table:

+----+-----------+--------------+------------+
| id | vendor_id | meta_key     | meta_value |
+----+-----------+--------------+------------+
| 28 |        12 | vendor_certs | ["9","13"] |
+----+-----------+--------------+------------+

I'm looking for the output to be:

EPA, FDA

Any help would be great. Thanks.

I adjusted your tags and question title, since you are using MariaDB, not MySQL. Those two products have been slowly diverging since 2010, and you should think of them as different software now. — Bill Karwin, Dec 09 '20 at 00:02

GMB · Accepted Answer · 2020-12-10T23:44:15.080

3

If I follow you correctly, you can filter with json_contains():

select vc.*
from vendor_certs vc
where exists (
    select 1
    from vendor_key_value vkv
    where 
        vkv.id = ? 
        and vkv.meta_key = 'vendor_certs' 
        and json_contains(vkv.meta_value, concat('"', cv.id, '"')
)
)

You can apply an aggregate function on the resultset if you like. Say you want a JSON array:

select json_arrayagg(vc.cert)
from vendor_certs vc
where exists (
    select 1
    from vendor_key_value vkv
    where 
        vkv.id = ? 
        and vkv.meta_key = 'vendor_certs' 
        and json_contains(vkv.meta_value, concat('"', cv.id, '"'))
)

edited Dec 10 '20 at 23:44

answered Dec 08 '20 at 23:33

GMB

216,147
25
84
135

I get "Variable name was expected. (near "?" at position 116)" – Dario Zadro Dec 08 '20 at 23:38
@DarioZadro: `?` represents a parameter to the query - I assumed that you wanted to to filter on a given `vendor_key_value` only. If that's not relevant, you can just remove that condition. – GMB Dec 08 '20 at 23:41
My bad. Now I get `json_contains does not exist` – Dario Zadro Dec 08 '20 at 23:43
@DarioZadro: which version of MySQL are you running? `JSON_CONTAINS()` was added quite a while ago. – GMB Dec 08 '20 at 23:44
Looks like I might need a higher MariaDB version, currently 10.1.47 – Dario Zadro Dec 08 '20 at 23:44
1

@DarioZadro: yes, this requires MariaDB 10.2.3: https://mariadb.com/kb/en/json_contains/. Manipulating JSON without JSON functions will be tedious. Any plans to upgrade? – GMB Dec 08 '20 at 23:48
Upgraded to 10.5 and your solution works great. Thanks! – Dario Zadro Dec 09 '20 at 02:31
Actually, this doesn't work. It's pulling extra results that are not part of `meta_key` – Dario Zadro Dec 10 '20 at 23:41
1

@DarioZadro: well, if there are several rows in `vendor_key_value` for a given `id`, then you need a filter on `meta_key`. I updated the answer. – GMB Dec 10 '20 at 23:45

score 1 · Answer 2 · answered Dec 11 '20 at 00:44

I'll still accept the answer from @GMB because it got me on the right track.

However, after some tinkering and reading docs on JSON functions, this was much easier to work with and understand for me.

SELECT cert
FROM vendor_certs
JOIN vendor_key_value
ON JSON_CONTAINS(vendor_key_value.meta_value, concat('"', vendor_certs.id, '"'))
WHERE meta_key = 'vendor_certs' AND vendor_id = 12

I don't know if the other answer is a faster query or has other benefits, but I hope my answer helps someone else that might get stuck on a similar JSON join.

MariaDB JOIN based on JSON array

2 Answers2