Consultando un array json en MySQL utilizando MySQL Workbench.

Aquí está mi data JSON:

{
“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
}
]
}
]
}

Quiero obtener el número de teléfono de las personas que tienen phonetype como landline.
¿Cómo hacer eso?

Intenté este enfoque:

Encuentra el número de teléfono cuando el tipo de teléfono es ‘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;

El problema con esto es que estoy usando ‘one’ y así puedo lograr resultados, pero aquí en mi JSON tengo 2 personas que tienen type como landline.
Usando la búsqueda JSON obtengo una matriz de valores y no puedo decidir cómo extraer estos valores de fila de matriz de forma que pueda extraer los caminos.

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

Como puede ver en la 3ª y 4ª fila, estoy obteniendo 2 datos como una matriz.

¿Cómo almaceno estos datos para obtener el resultado apropiado?

También intenté otra consulta, pero no puedo recuperar resultados para una matriz de datos.

No puedo usar un procedimiento almacenado y tengo que usar el banco de trabajo de MySQL.

Tenga en cuenta que soy principiante, por lo que no sé cómo abordar esta solución para consultas más complejas donde pueda tener que recuperar la identificación de una persona que tenga un tipo como landline (varias personas en una sola matriz).

davy.ai

June 16, 2023 at 6:13 pm

Para obtener los números de teléfono de todas las personas que tienen el tipo de teléfono fijo, puede utilizar la siguiente consulta SQL:

SELECT 
    json_extract(applicationData, CONCAT('$.PersonApplicant[', person_index, '].ApplicantPhone[', phone_index, '].PhoneNumber')) AS PhoneNumber
FROM 
    application,
    JSON_TABLE(
        applicationData, 
        '$.PersonApplicant[*]'
        COLUMNS (
            person_index FOR ORDINALITY,
            phones JSON PATH '$.ApplicantPhone[*]'
        )
    ) person,
    JSON_TABLE(
        person.phones,
        '$[*]' 
        COLUMNS (
            phone_index FOR ORDINALITY,
            PhoneType VARCHAR(255) PATH '$.PhoneType',
            PhoneNumber VARCHAR(255) PATH '$.PhoneNumber'
        )
    ) phone
WHERE
    phone.PhoneType = 'TELEFONO FIJO';

Esta consulta utiliza la función JSON_TABLE para extraer los datos relevantes del objeto JSON. Primero extrae todos los solicitantes de personas utilizando $.PersonApplicant[*]. Para cada persona, luego extrae todos sus números de teléfono utilizando $.ApplicantPhone[*]. Crea una nueva fila para cada número de teléfono e indexa los números de teléfono utilizando el person_index y el phone_index. Finalmente, filtra las filas según el tipo de teléfono que sea ‘TELEFONO FIJO’ y extrae el número de teléfono para cada fila.

Esta consulta devolverá un conjunto de resultados con una fila para cada número de teléfono fijo, y la columna PhoneNumber contendrá el número de teléfono para cada resultado.

Para obtener el ID de la persona que tiene un tipo de teléfono ‘TELEFONO FIJO’, puede incluir la columna PersonalId en la función JSON_TABLE y agruparla en la consulta externa. Por ejemplo:

SELECT 
    person_index, 
    json_extract(applicationData, CONCAT('$.PersonApplicant[', person_index, '].PersonalId')) AS PersonalId,
    GROUP_CONCAT(phone.PhoneNumber SEPARATOR ',') AS NumerosTelefonicosFijos
FROM 
    application,
    JSON_TABLE(
        applicationData, 
        '$.PersonApplicant[*]'
        COLUMNS (
            person_index FOR ORDINALITY,
            phones JSON PATH '$.ApplicantPhone[*]'
        )
    ) person,
    JSON_TABLE(
        person.phones,
        '$[*]' 
        COLUMNS (
            phone_index FOR ORDINALITY,
            PhoneType VARCHAR(255) PATH '$.PhoneType',
            PhoneNumber VARCHAR(255) PATH '$.PhoneNumber'
        )
    ) phone
WHERE
    phone.PhoneType = 'TELEFONO FIJO'
GROUP BY 
    person_index;

Esta consulta agrega una columna PersonalId a la consulta externa, que extrae el ID personal de cada persona. Luego agrupa las filas por el person_index y agrega los números de teléfono fijos utilizando GROUP_CONCAT. El conjunto de resultados contendrá una fila para cada persona que tenga al menos un número de teléfono fijo, con la columna PersonalId que contiene el ID de la persona y la columna NumerosTelefonicosFijos que contiene una lista separada por comas de sus números de teléfono fijos.

Answer

davy.ai

June 16, 2023 at 6:13 pm
Para obtener los números de teléfono de todas las personas que tienen el tipo de teléfono fijo, puede utilizar la siguiente consulta SQL:
```
SELECT 
    json_extract(applicationData, CONCAT('$.PersonApplicant[', person_index, '].ApplicantPhone[', phone_index, '].PhoneNumber')) AS PhoneNumber
FROM 
    application,
    JSON_TABLE(
        applicationData, 
        '$.PersonApplicant[*]'
        COLUMNS (
            person_index FOR ORDINALITY,
            phones JSON PATH '$.ApplicantPhone[*]'
        )
    ) person,
    JSON_TABLE(
        person.phones,
        '$[*]' 
        COLUMNS (
            phone_index FOR ORDINALITY,
            PhoneType VARCHAR(255) PATH '$.PhoneType',
            PhoneNumber VARCHAR(255) PATH '$.PhoneNumber'
        )
    ) phone
WHERE
    phone.PhoneType = 'TELEFONO FIJO';
```
Esta consulta utiliza la función JSON_TABLE para extraer los datos relevantes del objeto JSON. Primero extrae todos los solicitantes de personas utilizando $.PersonApplicant[*]. Para cada persona, luego extrae todos sus números de teléfono utilizando $.ApplicantPhone[*]. Crea una nueva fila para cada número de teléfono e indexa los números de teléfono utilizando el person_index y el phone_index. Finalmente, filtra las filas según el tipo de teléfono que sea ‘TELEFONO FIJO’ y extrae el número de teléfono para cada fila.

Esta consulta devolverá un conjunto de resultados con una fila para cada número de teléfono fijo, y la columna PhoneNumber contendrá el número de teléfono para cada resultado.

Para obtener el ID de la persona que tiene un tipo de teléfono ‘TELEFONO FIJO’, puede incluir la columna PersonalId en la función JSON_TABLE y agruparla en la consulta externa. Por ejemplo:
```
SELECT 
    person_index, 
    json_extract(applicationData, CONCAT('$.PersonApplicant[', person_index, '].PersonalId')) AS PersonalId,
    GROUP_CONCAT(phone.PhoneNumber SEPARATOR ',') AS NumerosTelefonicosFijos
FROM 
    application,
    JSON_TABLE(
        applicationData, 
        '$.PersonApplicant[*]'
        COLUMNS (
            person_index FOR ORDINALITY,
            phones JSON PATH '$.ApplicantPhone[*]'
        )
    ) person,
    JSON_TABLE(
        person.phones,
        '$[*]' 
        COLUMNS (
            phone_index FOR ORDINALITY,
            PhoneType VARCHAR(255) PATH '$.PhoneType',
            PhoneNumber VARCHAR(255) PATH '$.PhoneNumber'
        )
    ) phone
WHERE
    phone.PhoneType = 'TELEFONO FIJO'
GROUP BY 
    person_index;
```
Esta consulta agrega una columna PersonalId a la consulta externa, que extrae el ID personal de cada persona. Luego agrupa las filas por el person_index y agrega los números de teléfono fijos utilizando GROUP_CONCAT. El conjunto de resultados contendrá una fila para cada persona que tenga al menos un número de teléfono fijo, con la columna PersonalId que contiene el ID de la persona y la columna NumerosTelefonicosFijos que contiene una lista separada por comas de sus números de teléfono fijos.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

Consultando un array json en MySQL utilizando MySQL Workbench.

Answer

davy.ai