Mysql join on json value issue

Question

i have the folowwing tables on mysql 5.7 server

produit__parure

id_parure (int)	ids_produits_parure (json)
21	["34809", "34823", "34813"]
22	["35703", "35854", "35877"]

and produit :

id_product (int)	... other columns
34809	...
34810	...

i try to join both on id.prod = value on ids_produits_parure with this query:

SELECT p.id_prod, pp.* FROM produit p left JOIN produit__parure pp on
JSON_CONTAINS(pp.ids_produits_parure->'$[*]', CAST(p.id_prod as JSON))
where id_prod=34809

but it return NULL on produit__parure fileds:

id_prod	id_parure	ids_produits_parure
34809	NULL	NULL

what am i doing wrong? what i want as a result is:

id_prod	id_parure	ids_produits_parure
34809	21	["34809", "34823", "34813"]

i tried Dan Chase answer but it produce wrong results (i have lines with ids_produits_parure not equal to id_prod )

id_prod	id_parure	ids_produits_parure
34809	21	["34809", "34823", "34813"]
34809	22	["35703", "35854", "35877"]

score 0 · Answer 1 · answered Aug 14 '21 at 18:20

0

Is it possible you are only selecting the ID as JSON on JSON_CONTAINS? I don't know what the resulting code should look like, except maybe something similar:

JSON_CONTAINS(pp.ids_produits_parure->'$[*]', CAST(pp as JSON))

because I doubt the ID is an array, and wouldn't you search pp not p?

Reference: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#:~:text=JSON_CONTAINS%20%28target%2C%20candidate%20%5B%2C%20path%20%5D%29%20Indicates%20by,found%20at%20a%20specific%20path%20within%20the%20target.

answered Aug 14 '21 at 18:20

Dan Chase

993
7
18

i tried Dan Chase answer but it produce wrong results (i have line with ids_produits_parure not equal to id_prod ) id_prod id_parure ids_produits_parure 34809 21 ["34809", "34823", "34813"] 34809 22 ["35703", "35854", "35877"] – MicMoz Aug 14 '21 at 19:03
I think we need more definition on what the data set is, and what you are trying to accomplish, so that we can suggest an appropriate query structure. Your intent changes everything above. My answer only outlines the reason it has NULLs, which is what you asked, but I can't predict what it should return. Your added comments add clarity as we go, but we need it up front. – Dan Chase Aug 14 '21 at 19:07

score 0 · Answer 2 · answered Aug 14 '21 at 20:31

Your KSON_CONTAINS is a bit of.

Json are in most cases tricky to handle, so you should condsider switching to a normalized design

CREATE TABLE produit__parure
    (`id_parure` int, `ids_produits_parure` json)
;
    
INSERT INTO produit__parure
    (`id_parure`, `ids_produits_parure`)
VALUES
    ('21', '["34809", "34823", "34813"]'),
    ('22', '["35703", "35854", "35877"]')
;

CREATE TABLE produit
    (`id_product` int, `other columns` varchar(3))
;
    
INSERT INTO produit
    (`id_product`, `other columns`)
VALUES
    (34809, '...'),
    (34810, '...')

;

SELECT JSON_CONTAINS(`ids_produits_parure`, CONCAT('"','34809' ,'"'), '$') FROM produit__parure

| JSON_CONTAINS(`ids_produits_parure`, CONCAT('"','34809' ,'"'), '$') |
| ------------------------------------------------------------------: |
|                                                                   1 |
|                                                                   0 |

SELECT p.id_product, pp.* FROM produit p left JOIN produit__parure pp on
JSON_CONTAINS(pp.ids_produits_parure, CONCAT('"',p.id_product,'"'),'$')
where id_product=34809

id_product | id_parure | ids_produits_parure                                   
---------: | --------: | :-----------------------------------------------------
     34809 |        21 | 5b223334383039222c20223334383233222c20223334383133225d

db<>fiddle here

Thank you for your help, but the result is strange, why do we have : 5b223334383039222c20223334383233222c20223334383133225d instead of the correct ids_products_parure values? — MicMoz, Aug 15 '21 at 04:01
it work well on my server i have the correct result, thank you again — MicMoz, Aug 15 '21 at 05:38

Mysql join on json value issue

2 Answers2