How to check if json array of values present in one table is present in another table using SQL?

Question

I have two tables that look like this..

Baskets

id	Array
1	`["Apple", "Mango", "Pineapple"]`
2	`["Mango", "Pineapple"]`
3	`["Dragonfruit"]`

Stock

Fruit	Count
Apple	100
Mango	500
Kiwi	99
Grapes	0

Through a SQL query, I have to check how many fruits from an array of the first table are present in the second table.

For example, out of ["Apple", "Mango", "Pineapple"] only Apple and Mango is in the Stock table.

Expected outcome:

id	available
1	2
2	1
3	0

PS. this is a simplification of the actual problem I'm trying to solve, my main query is how to match a JSON array of objects against some other table. The fruits/stock example is just for explaining the problem.

Mongo would be if you wanted to do it with JS and not learn the SQL which is pretty concise in the accepted answer and I bet many times faster. — grantwparks
– grantwparks, Commented Dec 7, 2023 at 4:50

Akina · Accepted Answer · 2021-03-16 10:59:29Z

2

SELECT Baskets.id, COUNT(Stock.Fruit) available
FROM Baskets 
LEFT JOIN Stock ON JSON_SEARCH(Baskets.Array, 'one', Stock.Fruit) IS NOT NULL
GROUP BY Baskets.id

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a0f8e8fba8851fcda508affca0201ba9

answered Mar 16, 2021 at 10:59

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

How to check if json array of values present in one table is present in another table using SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related