MySQL LEFT JOIN json field with another id from table

Question

I have two tables:

Bouquets

+----+------------+
| id | bouquet    |
+----+------------+
|  1 | Package #1 |
|  2 | Package #2 |
|  3 | Package #3 |
|  4 | Package #4 |
|  5 | Package #5 |
+----+------------+

And

Prices
+----+----------+-------------------------------------------------------------------+
| id | reseller | price                                                     
+----+----------+-------------------------------------------------------------------+
|  1 |        1 | {"1": "1.11", "2": "0.00", "3": "0.00", "4": "4.44", "5": "5.55"} |
+----+----------+-------------------------------------------------------------------+

I need to get bouquet names that price value is not "0.00"...so i try LEFT JOIN to join bouquets.id ON prices.price but i can't get how?

I need to get this:

+----+------------+
| id | bouquet    |
+----+------------+
|  1 | Package #1 |
|  4 | Package #4 |
|  5 | Package #5 |
+----+------------+

Here is my try but i im getting empty result:

SELECT b.id, b.bouquet FROM bouquets b 
LEFT JOIN prices p ON JSON_CONTAINS(p.price, CAST(b.id as JSON), '$') != "0.00"
WHERE p.reseller=1;

Answer 1

This is not easy to do purely in mysql as it seems, the best idea is to use (PHP,ASP,etc) to do the heavy lifting but after a lot of trial and error I found this post:

Convert JSON array in MySQL to rows

From there this query seems to work for me

SELECT 
    b.id,
    b.bouquet
FROM bouquet AS b
JOIN (
    SELECT
        indx.id,
        indx.idx,
        JSON_EXTRACT(p.price, idx) AS bouquetprice
    FROM prices AS p
    JOIN ( 
        SELECT  '$."1"' AS idx, 1 AS id UNION
        SELECT  '$."2"' AS idx, 2 AS id UNION
        SELECT  '$."3"' AS idx, 3 AS id UNION
        SELECT  '$."4"' AS idx, 4 AS id UNION
        SELECT  '$."5"' AS idx, 5 AS id 
    ) AS indx
    WHERE JSON_EXTRACT(p.price, idx) IS NOT NULL
    AND p.reseller = 1
) AS ind
ON b.id = ind.id
AND ind.bouquetprice != "0.00"

The trick seems to be that the CONCAT in the linked SO post does not work well with numeric key names in your json. So you have to resort to the 2 indexes in the temporary join to search on.

Also the temporary join table is less than ideal in terms of creating a list of ever growing indexes but it's a place to start at least. (sorry about all the bad naming idx, indx, etc.)

Edit: forgot the reseller part

Answer 2

I im programming in node js using mysql wrapper this is the solution that i use and it is working:

/* QUERY - aaBouquets */
            connection.query("SELECT id, bouquet FROM bouquets ORDER BY bouquet ASC",function(err, rows, fields){
                /* BOUQUETS - number */
                var total = rows.length;

                /* FOUND - bouquets */
                if (rows.length) {

                    /* GET - prices */
                    for (var i in rows) {
                        var s = 1;
                        connection.query("SELECT '"+rows[i].id+"' AS id, '"+rows[i].bouquet+"' AS bouquet FROM prices p LEFT JOIN bouquets b ON JSON_SEARCH(p.price, 'one', '$.\""+rows[i].id+"\"') WHERE p.reseller=? AND FORMAT(JSON_EXTRACT(price, '$.\""+rows[i].id+"\"'), 2) != \"0.00\"",[qreseller], function(err, rows, results){

                            /* CHECK - prices */
                            if (s < total) {
                                if (rows.length) {
                                    /* GET - prices */
                                    data.push(rows[0]);
                                };
                                s++;
                            } else {
                                /* CHECK - prices */
                                if(data.length) {
                                    if (rows.length) {
                                        /* GET - prices */
                                        data.push(rows[0]);
                                    };

                                    /* RETURN - servers data */
                                    res.json(data);
                                };
                            }
                        });
                    }
                }
            });

You can see that first query is getting id and bouquet names then in for loop i im using that id to get values for that bouquet id and show only if value not equal "0.00"..using variable s and total is used here because if i call console.log(data) i get undefined variable..because in node js variable is local and need to be called inside for loop if is called outside i get undefined variable error.

This way i im getting only bouquets with defined price...i don't know if it can be done in single query (because you can't use LEFT JOIN ON b.id on p.prices) so need this two query...to me it is getting ok...so if someone can minimize code to get it more speed or improve...it is welcome.

Answer 3

Call me old-fashioned, but I'm really not a fan of storing json data. Any way, a normalized table might look like this...

Prices
+----------+------------+-------+
| reseller | bouquet_id | price |
+----------+------------+-------+
|        1 |          1 |  1.11 |
|        1 |          4 |  4.44 |
|        1 |          5 |  5.55 |
+----------+------------+-------+