Join table where column contain multiples ids in JSON format

Asked Jul 11 '15 at 19:52

Active Jul 11 '15 at 20:01

Viewed 432 times

I would like to get customers table based on 2 conditions, but the customers table has the customer_group value stored in JSON format.

customers table

id,name,customer_group,address
1,Test,["1","2"],Address

customer_group table

id,group_name,code
1,Group 1,type1
2,Group 2,type2

query:

SELECT *.customers
  FROM customers
  INNER JOIN customer_group ON customer_group.id=customers.customer_group
  WHERE customer_group.code = type1 OR customer_group.code = type2
  GROUP BY customers.id;

is there a way to extract first the customer_group values in json and then pass to the query?

asked Jul 11 '15 at 19:52

ndAR

Don't store data in JSON format if you need to join with it. – Barmar Jul 11 '15 at 19:55
You shouldn't store lists in a column at all, use a table for that. – Barmar Jul 11 '15 at 19:55
what do you suggest is to create a new table with columns "id,customer_id,customer_group_id" and store as each ? – ndAR Jul 11 '15 at 19:58
@JDTLH9 That question is for TSQL, not MySQL. – Barmar Jul 11 '15 at 19:59
Yes, that's exactly what I suggest. It's standard database normalization. – Barmar Jul 11 '15 at 20:00
possible duplicate of [Extract data from json inside mysql field](http://stackoverflow.com/questions/31357542/extract-data-from-json-inside-mysql-field) – Nasreddine Jul 11 '15 at 20:04
No, there is a substring that can be used only on SELECT, WHERE or GROUP_BY i think it cannot be used on Multiple JOINS – ndAR Jul 11 '15 at 20:17

Join table where column contain multiples ids in JSON format

0 Answers0