es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

Seleccionar y contar las claves de matriz en Athena.

15 Jun, 2023 Programación

Tengo muchas filas de datos que representan eventos en mi base de datos. Cada fila tiene una columna “payload” que contiene un array de claves y valores. Puedo analizar fácilmente un valor usando:

Select
payload.keyname
from Database

Pero estoy tratando de obtener una lista y contar todas las claves que aparecen en un día determinado.

|                   payload                               |    
|{id=a, gameid=x, gametype=1, sponserid=null}             |
|{id=b, gameid=y, gametype=2, action=jump, sponserid=null}|
|{id=c, gameid=z, action=jump, sponserid=null}            |

Salida deseada:

|    Clave  |Count|
|id        |  3  |
|game      |  3  |
|gametype  |  2  |
|action    |  2  |
|sponserid |  2  |

¿Existe algún método para consultar fácilmente un array de claves? ¿Tal como?

Select
payload.*, count(*)
from Database
group by payload.*
Tags:  ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Desafortunadamente, no existe un método SQL estándar para consultar fácilmente las claves de una matriz, ya que las matrices no son nativas de SQL. Sin embargo, como estás utilizando Amazon Athena, que admite la consulta de datos JSON, puedes utilizar las funciones JSON para obtener los resultados deseados.

    Suponiendo que tus datos se almacenan como objetos JSON, puedes utilizar la función json_extract para extraer todas las claves de cada objeto JSON. Luego, puedes utilizar la función unnest para aplanar las claves extraídas en filas separadas. Finalmente, puedes agrupar por las claves extraídas y contar las ocurrencias de cada clave.

    Aquí tienes una consulta de ejemplo que debería funcionar para tus datos:

    SELECT key, COUNT(*) AS count
FROM (
  SELECT DISTINCT key, event_date -- agrega event_date si no está incluido en tus filas
  FROM (
    SELECT event_date, json_extract(payload, '$.*') AS keys
    FROM your_table
    WHERE event_date = '2022-01-01' -- reemplaza con la fecha deseada
  )
  CROSS JOIN UNNEST(keys) AS t(key)
)
GROUP BY key
ORDER BY count DESC;

    La consulta primero utiliza json_extract para extraer todas las claves de la columna payload para una event_date específica (ajusta la cláusula WHERE según sea necesario para tu caso de uso). El resultado es una lista de matrices JSON, cada una conteniendo las claves para una sola fila.

    A continuación, la consulta utiliza CROSS JOIN UNNEST para aplanar cada matriz en filas separadas, con una clave por fila. La palabra clave DISTINCT asegura que se eliminen las claves duplicadas, de modo que cada clave se cuente solo una vez por fila.

    Finalmente, la consulta agrupa por las claves extraídas usando GROUP BY y cuenta las ocurrencias de cada clave utilizando COUNT(*). La cláusula ORDER BY es opcional y ordena la salida por cuenta en orden descendente.

    Ten en cuenta que esta consulta supone que todos los objetos JSON en la columna payload tienen las mismas claves. Si algunos objetos tienen claves adicionales que no están presentes en otros, es posible que debas modificar la consulta para extraer las claves dinámicamente en función de la estructura JSON.

Comments are closed.