2

i have two table casetemp and medicication_master

patient has fields

id name age sex medicineid
1 xyz 23 M 1,2
2 abc 20 f 3

medicine has fields

id medname desc
1 crosin fever tab
2 etzaa shampoo
3 zaanta painkiller

i want the mysql left join output as following :

[{
  "id":"1",
  "name":"xyz",
  "age":"23",
  "sex":"M",
  "medicine_id":"1,2",
  "medicine_Data":[
      {
         "id":"1"
         "medname":"crosin",
         "desc":"fever tab"
      },
      {
         "id":"2"
         "medname":"etzaa",
         "desc":"shampoo"
      }]
  },
  {
  "id":"2",
  "name":"abc",
  "age":"20",
  "sex":"F",
  "medicine_id":"3",
  "medicine_Data":[{
         "id":"3"
         "medname":"zaanta",
         "desc":"pain killer"
      }]
  }]

the query i used is

SELECT json_object(
  'id', b.id,
  'name',b.name,
  'age',b.age,
  'sex',b.sex,
  'medicine_id',b.medicine_id,
  'medicine_data', json_arrayagg(json_object(
    'id', pt.id,
    'medname', pt.medname,
    'desc',pt.desc,
  ))
  )
FROM patient b LEFT JOIN medication_master pt
ON b.medicine_id = pt.id
where b.id=1
GROUP BY b.id;

thanks in advance

  • 1) What is the question? 2) Why LEFT joining? INNER is more suitable. 3) Use `ON FIND_IN_SET(medicine.id, patient.medicineid)`. 4) Storing the data array as CSV is bad idea - normalize, add junction table. – Akina Jun 09 '21 at 04:51
  • my question is how to bring the json format using either joins. –  Jun 09 '21 at 06:59
  • You need 2-level JSON, so you need in nested `json_arrayagg`. Inner one (in subquery or CTE) will create `medicine_Data` array per `medicine.id`, outer will create the output which you need. I.e. join not the table `medicine` but aggregating subquery. – Akina Jun 09 '21 at 07:03

0 Answers0