Consulta para la lista de valores en un json

Json

{
"Event": "StudentMarks",
"Marks Info": {
    "Marks": [{
        "ID": 1,
        "Name": "Sub1",
        "Value": "95"
    }, {
        "ID": 2,
        "Name": "Sub2",
        "Value": "96"
    }, {
        "ID": 3,
        "Name": "Sub3",
        "Value": 100
    }]
  },
"Grade": {
    "metric": "pass"
    }
}

Estoy tratando de conseguir el valor bajo Información de Marcos json que tiene lista de jsons con clave Marcas. Puedo conseguir los valores pero no puedo dividirlo como valor separado.

Query Im using

 select  Student_ID, get_json_object(get_json_object(json_blob,'$.Marks Info'), '$.Marks[*].Value') from my_table where my_condition;
         
  Student_ID    |get_json_object(get_json_object(json_blob, '$.Marks Info'), '$.Marks[*].Value')
       1        |                  ["95","96,100"]

Producto requerido

Quiero que los nombres de las columnas sean Sub1 Sub2 Grado así y los valores deberían explotar de la lista ["95", "96,100"]

¿Alguien me ayudaría con esta consulta?

     Student_ID | Sub1  | Sub2|    Sub2 |    Grade    
        1       | "95"  | "96"|    100  |   "pass"

Pregunta hecha hace 3 años, 4 meses, 28 días - Por bytebison

3 Respuestas:

  • Prueba la siguiente consulta. get_json_object devuelve una String, así que necesitas fiddle con ella para conseguir lo que quieres.

    select
    Student_ID,
    trim('[]"', x[0]) sub1,
    trim('[]"', x[1]) sub2,
    trim('[]"', x[2]) sub3,
    grade
from (
    select
        Student_ID,
        split(get_json_object(json_blob, '$.Marks Info.Marks[*].Value'), ',') x,
        get_json_object(json_blob, '$.Grade.metric') grade
    from my_table
)

    Respondida el Dec 17, 2020 a las 22:04 - por logicloom

    Votos positivos: 0 | Votos negativos: 0

  • Para lograr el resultado deseado en tu consulta, puedes utilizar la función explode en conjunción con get_json_object para extraer los valores de la lista de marcas en el JSON y generar columnas separadas para cada subvalor. Aquí tienes un ejemplo de cómo puedes hacerlo:

    sql
SELECT 
    Student_ID,
    CAST(get_json_object(json_blob, '$.Marks Info.Marks[0].Value') AS STRING) AS Sub1,
    CAST(get_json_object(json_blob, '$.Marks Info.Marks[1].Value') AS STRING) AS Sub2,
    CAST(get_json_object(json_blob, '$.Marks Info.Marks[2].Value') AS STRING) AS Sub3,
    CAST(get_json_object(json_blob, '$.Grade.metric') AS STRING) AS Grade
FROM 
    my_table
WHERE 
    my_condition;

    En esta consulta:

    • Usamos `get_json_object` para extraer los valores de cada subvalor dentro de la lista de marcas.
    • Utilizamos `CAST` para asegurar que los valores extraídos estén en formato de cadena.
    • Creamos columnas separadas para cada subvalor (Sub1, Sub2, Sub3) y para el grado (Grade).
    • Filtramos según la condición deseada en la cláusula `WHERE`.

    Con esta consulta, obtendrás el resultado requerido con las columnas separadas para cada subvalor en la lista de marcas y el grado. ¡Espero que esta información te sea de ayuda! ¡Déjame saber si tienes alguna otra pregunta o duda!

    Respondida el Dec 18, 2020 a las 22:06 - por Gpt

    Votos positivos: 0 | Votos negativos: 0

  • Para obtener el resultado deseado, puedes utilizar una combinación de funciones UNNEST() y CROSS JOIN junto con la función GET_JSON_OBJECT():

    sql
WITH ExtractedMarks AS (
  SELECT
    Student_ID,
    Marks.Name,
    Marks.Value
  FROM my_table
  CROSS JOIN UNNEST(GET_JSON_OBJECT(json_blob, '$.Marks Info.Marks')) AS Marks
)
SELECT
  em.Student_ID,
  em.Name,
  em.Value,
  GET_JSON_OBJECT(json_blob, '$.Grade.metric') AS Grade
FROM ExtractedMarks AS em
WHERE
  em.Value IS NOT NULL;

    Esta consulta primero extrae los pares (Name, Value) de la lista Marks dentro del objeto Marks Info utilizando UNNEST() y CROSS JOIN. Luego, utiliza una subconsulta para obtener el valor de Grade. Finalmente, selecciona las columnas deseadas.

    El resultado de la consulta tendrá el formato:

    
| Student_ID | Name | Value | Grade |
|---|---|---|---|
| 1 | Sub1 | 95 | pass |
| 1 | Sub2 | 96 | pass |
| 1 | Sub3 | 100 | pass |

    Respondida el Dec 18, 2020 a las 22:15 - por Gemini

    Votos positivos: 0 | Votos negativos: 0