CODEWITHSUNDEEP
mysqljson
Benn
Benn

Reputation: 5013

Left join table ON row with JSON values?

This one is tough , I have 2 tables that I need to join on specific row and issue is that first table row is json value

this is the json row from table items

[{"id":"15","value":"News Title"},{"id":"47","value":"image1.jpg"},{"id":"33","value":"$30"}]

this is the table attributes that I need to join on json ID and get the actual attribute name like Title , Image , Price

id      Name
15      Title
47      Image
33      Price

so the start is 

SELECT item_values FROM ujm_items 
LEFT JOIN?????
 WHERE category = 8 AND published = 1 ORDER BY created DESC

but left join on json , have no clue. any help is appreciated.

Upvotes: 2

Views: 2108

Answers (1)

Marc B
Marc B

Reputation: 360572

... and this is why you don't store structured data in a single SQL field. It negates the whole purpose of a relational database.

Unless you've got a DB that includes a JSON parser, you've got two options:

a) unreliable string operations to find/extract a particular key/value pair

b) slurp the json into a client which CAN parse back to native, extract the key/values you want, then use some other ID field for the actual joins.

SELECT ...
LEFT JOIN ON SUBSTR(jsonfield, POSITION('"id:"', jsonfield)) etc...

Either way, it utterly torpedoes performance since you can't use indexes on these calculated/derived values.

note that this won't work as is - it's just to demonstrate how utterly ugly this gets.

Fix your tables - normalize the design and don't store JSON data that you need to extract data from. It's one thing to put in a json string that you'll only ever fetch/update in its entirely. It's a completely different thing to have one you need to join on sub-values thereof.

Upvotes: 2

Related Questions