How can I write SQL to retrieve data from two tables which outputs nested objects in json format when json_encode?

Viewed 19

My tables & fields

person - id, first_name, last_name, dob
phone - id, person_id, number, type
email - id, person_id, email, type

One person can have multiple phones and/or emails. I want to response person list in json format from remote using PHP.

I need the sql query which results in following json format when json_encoding in PHP?

[
  {
    "id": 68014,
    "first_name": "James",
    "last_name": "Bond",
    "dob": "2022-08-17",
    "phones": [
      {
        "number": "0221 802 540",
        "type": "business",
        "person_jrid": 68014
      },
      {
        "number": "0221 802 540",
        "type": "personal",
        "person_jrid": 68014
      }
    ],
    "emails": [
      {
        "email": "info@test.de",
        "type": "business",
        "person_jrid": 68014
      },
      {
        "email": "info2@test2.de",
        "type": "personal",
        "person_jrid": 68014
      }
    ]
  },
  {
    "id": 68016,
    "first_name": "James2",
    "last_name": "Bond2",
    "dob": "2022-08-17",
    "phones": [
      {
        "number": "0221 802 540",
        "type": "business",
        "person_jrid": 68014
      },
      {
        "number": "0221 802 540",
        "type": "personal",
        "person_jrid": 68014
      }
    ],
    "emails": [
      {
        "email": "info@test.de",
        "type": "business",
        "person_jrid": 68014
      },
      {
        "email": "info2@test2.de",
        "type": "personal",
        "person_jrid": 68014
      }
    ]
  }
]

Please help me.. my dear friends. Thanks in advance.

0 Answers
Related