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
– Bill Karwin, Commented Dec 9, 2020 at 0:02

GMB · Accepted Answer · 2020-12-10 23:44:15Z

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, 2020 at 23:44

answered Dec 8, 2020 at 23:33

GMB

224k25 gold badges103 silver badges151 bronze badges

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

9 Comments

Dario Zadro Over a year ago

I get "Variable name was expected. (near "?" at position 116)"

GMB Over a year ago

@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.

Dario Zadro Over a year ago

My bad. Now I get json_contains does not exist

GMB Over a year ago

@DarioZadro: yes, this requires MariaDB 10.2.3: mariadb.com/kb/en/json_contains. Manipulating JSON without JSON functions will be tedious. Any plans to upgrade?

GMB Over a year ago

@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.

|

Dario Zadro · Accepted Answer · 2020-12-11 00:44:09Z

1

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.

answered Dec 11, 2020 at 0:44

Dario Zadro

1,3242 gold badges15 silver badges27 bronze badges

Collectives™ on Stack Overflow

MariaDB JOIN based on JSON array

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related