Querying on mysql json array using mysql workbench

Question

Here is my json data:

{
  "TransactionId": "1",
  "PersonApplicant": [
    {
      "PersonalId": "1005",
      "ApplicantPhone": [
        {
          "PhoneType": "LANDLINE",
          "PhoneNumber": "8085063644",
          "IsPrimaryPhone": true
        }
      ]
    },
    {
      "PersonalId": "1006",
      "ApplicantPhone": [
        {
          "PhoneType": "LANDLINE",
          "PhoneNumber": "9643645364",
          "IsPrimaryPhone": true
        },
        {
          "PhoneType": "HOME",
          "PhoneNumber": "987654321",
          "IsPrimaryPhone": false
        }
      ]
    }
  ]
}

I want to get phone no of the people who have phonetype as landline. How to do that?

I tried this approach:

#find phoneNumber when phoneType='LANDLINE'

SELECT 
    @path_to_name := json_unquote(json_search(applicationData, 'one', 'LANDLINE')) AS path_to_name,
    @path_to_parent := trim(TRAILING '.PhoneType' from @path_to_name) AS path_to_parent,
    @event_object := json_extract(applicationData, @path_to_parent) as event_object,
    json_unquote(json_extract(@event_object, '$.PhoneNumber')) as PhoneNumber
FROM application;

The issue with this is that I am using 'one' so I am able to achieve results but here in my json I have 2 people who have type as landline. Using json search I am getting array of values and I am not able to decide how to extract these array row values in a manner where I can extract paths.

SELECT 
    @path_to_name := json_unquote(json_search(applicationData, 'all', 'LANDLINE')) from application;

result:

as you can see at 3rd and 4th row i am getting 2 data as an array.

How do I store this data to get the appropriate result?

I also tried one more query but not able to retrieve results for array of data.

I cannot use stored procedure and I have to use mysql workbench.

Please note that I am fresher so I don't know how I can approach this solution for more complex queries where I may have to retrieve id of a person having type as landline (multiple people in single array).

Answer 1

SELECT test.id, jsontable.*
FROM test
CROSS JOIN JSON_TABLE(test.data,
                      '$.PersonApplicant[*]'
                        COLUMNS ( PersonalId INT PATH '$.PersonalId',
                                  PhoneType VARCHAR(255) PATH '$.ApplicantPhone[0].PhoneType',
                                  PhoneNumber VARCHAR(255) PATH '$.ApplicantPhone[0].PhoneNumber')) jsontable
WHERE jsontable.PhoneType = 'LANDLINE';

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4089207ccfba5068a48e06b52865e759