{"id":11094,"date":"2026-04-01T14:44:50","date_gmt":"2026-04-01T17:44:50","guid":{"rendered":"https:\/\/kondado.com.br\/blog\/wiki\/?p=11094"},"modified":"2026-04-08T17:48:44","modified_gmt":"2026-04-08T20:48:44","slug":"via-kondado-referencia-ksql","status":"publish","type":"post","link":"https:\/\/kondado.com.br\/blog\/wiki\/2026\/04\/01\/via-kondado-referencia-ksql\/","title":{"rendered":"Via Kondado: Refer\u00eancia KSQL"},"content":{"rendered":"\n

O KSQL \u00e9 a linguagem de consulta da Kondado. Ele utiliza uma sintaxe em formato JSON<\/strong> que permite filtrar, agrupar, ordenar e transformar seus dados de forma simples e segura.<\/p>\n\n\n\n

\u2139\ufe0f Beta:<\/strong> Consultas via KSQL est\u00e3o dispon\u00edveis em modo beta.<\/p><\/blockquote>\n\n\n\n

Estrutura b\u00e1sica<\/h2>\n\n\n\n

Uma consulta KSQL \u00e9 um objeto JSON com os seguintes campos:<\/p>\n\n\n\n

Campo<\/th>Obrigat\u00f3rio<\/th>Descri\u00e7\u00e3o<\/th><\/tr><\/thead>
dest_collection_id<\/code><\/td>Sim<\/td>Identificador da sua cole\u00e7\u00e3o de destino<\/td><\/tr>
table_name<\/code><\/td>Sim<\/td>Nome da tabela a ser consultada<\/td><\/tr>
columns<\/code><\/td>Sim<\/td>Lista de colunas a retornar<\/td><\/tr>
column_aggregations<\/code><\/td>N\u00e3o<\/td>Tipo de agrega\u00e7\u00e3o para cada coluna (ex: \"sum\"<\/code>, \"avg\"<\/code>, \"none\"<\/code>)<\/td><\/tr>
dimensions<\/code><\/td>N\u00e3o<\/td>Colunas de agrupamento (equivalente ao GROUP BY)<\/td><\/tr>
where_conditions<\/code><\/td>N\u00e3o<\/td>Condi\u00e7\u00f5es de filtro<\/td><\/tr>
order_by<\/code><\/td>N\u00e3o<\/td>Ordena\u00e7\u00e3o dos resultados<\/td><\/tr>
limit<\/code><\/td>N\u00e3o<\/td>N\u00famero m\u00e1ximo de registros (m\u00e1ximo: 1.000)<\/td><\/tr>
customColumns<\/code><\/td>N\u00e3o<\/td>Colunas calculadas com express\u00f5es personalizadas<\/td><\/tr>
column_transformations<\/code><\/td>N\u00e3o<\/td>Transforma\u00e7\u00f5es aplicadas \u00e0s colunas<\/td><\/tr>
joins<\/code><\/td>N\u00e3o<\/td>Jun\u00e7\u00e3o com outras tabelas<\/td><\/tr>
union<\/code><\/td>N\u00e3o<\/td>Array de consultas para combinar com UNION ALL (substitui os demais campos)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Exemplo m\u00ednimo<\/h3>\n\n\n\n
{\n  \"dest_collection_id\": 12345,\n  \"table_name\": \"vendas\",\n  \"columns\": [\"produto\", \"valor\", \"data\"]\n}<\/code><\/pre>\n\n\n\n
Filtros (where_conditions)<\/h2>\n\n\n\n
Os filtros s\u00e3o definidos como uma lista de condi\u00e7\u00f5es. Todas as condi\u00e7\u00f5es s\u00e3o combinadas com E<\/strong> (AND).<\/p>\n\n\n\n
\"where_conditions\": [\n  {\"column\": \"status\", \"operator\": \"=\", \"value\": \"ativo\"},\n  {\"column\": \"valor\", \"operator\": \">\", \"value\": 100}\n]<\/code><\/pre>\n\n\n\n
Operadores de compara\u00e7\u00e3o<\/h3>\n\n\n\n
Operador<\/th>Descri\u00e7\u00e3o<\/th>Exemplo de valor<\/th><\/tr><\/thead>
=<\/code><\/td>Igual a<\/td>\"ativo\"<\/code><\/td><\/tr>
!=<\/code><\/td>Diferente de<\/td>\"cancelado\"<\/code><\/td><\/tr>
><\/code><\/td>Maior que<\/td>100<\/code><\/td><\/tr>
<<\/code><\/td>Menor que<\/td>50<\/code><\/td><\/tr>
>=<\/code><\/td>Maior ou igual a<\/td>100<\/code><\/td><\/tr>
<=<\/code><\/td>Menor ou igual a<\/td>200<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Operadores de conjunto<\/h3>\n\n\n\n
Operador<\/th>Descri\u00e7\u00e3o<\/th>Exemplo de valor<\/th><\/tr><\/thead>
IN<\/code><\/td>Est\u00e1 na lista<\/td>[\"SP\", \"RJ\", \"MG\"]<\/code><\/td><\/tr>
NOT IN<\/code><\/td>N\u00e3o est\u00e1 na lista<\/td>[\"cancelado\", \"expirado\"]<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Operadores de texto<\/h3>\n\n\n\n
Operador<\/th>Descri\u00e7\u00e3o<\/th>Exemplo de valor<\/th><\/tr><\/thead>
LIKE<\/code><\/td>Corresponde ao padr\u00e3o (use %<\/code> como coringa)<\/td>\"%kondado%\"<\/code><\/td><\/tr>
NOT LIKE<\/code><\/td>N\u00e3o corresponde ao padr\u00e3o<\/td>\"%teste%\"<\/code><\/td><\/tr>
STARTS_WITH<\/code><\/td>Come\u00e7a com<\/td>\"BR\"<\/code><\/td><\/tr>
ENDS_WITH<\/code><\/td>Termina com<\/td>\".com\"<\/code><\/td><\/tr>
CONTAINS<\/code><\/td>Cont\u00e9m o texto<\/td>\"kondado\"<\/code><\/td><\/tr>
NOT_CONTAINS<\/code><\/td>N\u00e3o cont\u00e9m o texto<\/td>\"teste\"<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Operadores de nulo<\/h3>\n\n\n\n
Operador<\/th>Descri\u00e7\u00e3o<\/th><\/tr><\/thead>
IS NULL<\/code><\/td>\u00c9 nulo<\/td><\/tr>
IS NOT NULL<\/code><\/td>N\u00e3o \u00e9 nulo<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Operador de intervalo<\/h3>\n\n\n\n
Operador<\/th>Descri\u00e7\u00e3o<\/th>Exemplo de valor<\/th><\/tr><\/thead>
BETWEEN<\/code><\/td>Est\u00e1 entre dois valores<\/td>[100, 500]<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Operadores de data relativa<\/h3>\n\n\n\n
Estes operadores facilitam filtros de data sem precisar calcular datas manualmente:<\/p>\n\n\n\n
Operador<\/th>Descri\u00e7\u00e3o<\/th><\/tr><\/thead>
LAST_N_DAYS<\/code><\/td>\u00daltimos N dias<\/td><\/tr>
NEXT_N_DAYS<\/code><\/td>Pr\u00f3ximos N dias<\/td><\/tr>
LAST_N_HOURS<\/code><\/td>\u00daltimas N horas<\/td><\/tr>
LAST_N_MINUTES<\/code><\/td>\u00daltimos N minutos<\/td><\/tr>
THIS_WEEK<\/code><\/td>Semana atual<\/td><\/tr>
LAST_WEEK<\/code><\/td>Semana passada<\/td><\/tr>
THIS_MONTH<\/code><\/td>M\u00eas atual<\/td><\/tr>
LAST_MONTH<\/code><\/td>M\u00eas passado<\/td><\/tr>
THIS_QUARTER<\/code><\/td>Trimestre atual<\/td><\/tr>
LAST_QUARTER<\/code><\/td>Trimestre passado<\/td><\/tr>
THIS_YEAR<\/code><\/td>Ano atual<\/td><\/tr>
LAST_YEAR<\/code><\/td>Ano passado<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Exemplo:<\/p>\n\n\n\n
{\"column\": \"criado_em\", \"operator\": \"LAST_N_DAYS\", \"value\": 7}<\/code><\/pre>\n\n\n\n
Agrega\u00e7\u00f5es<\/h2>\n\n\n\n
Para calcular m\u00e9tricas sobre seus dados, use column_aggregations<\/code> junto com dimensions<\/code>.<\/p>\n\n\n\n
O campo column_aggregations<\/code> \u00e9 uma lista com o mesmo tamanho de columns<\/code>. Cada posi\u00e7\u00e3o define a agrega\u00e7\u00e3o da coluna correspondente. Use \"none\"<\/code> para colunas sem agrega\u00e7\u00e3o (que devem estar em dimensions<\/code>).<\/p>\n\n\n\n
{\n  \"table_name\": \"pedidos\",\n  \"columns\": [\"categoria\", \"receita\", \"quantidade\"],\n  \"column_aggregations\": [\"none\", \"sum\", \"count\"],\n  \"dimensions\": [\"categoria\"]\n}<\/code><\/pre>\n\n\n\n
Fun\u00e7\u00f5es de agrega\u00e7\u00e3o dispon\u00edveis<\/h3>\n\n\n\n
Fun\u00e7\u00e3o<\/th>Descri\u00e7\u00e3o<\/th><\/tr><\/thead>
none<\/code><\/td>Sem agrega\u00e7\u00e3o (coluna de agrupamento)<\/td><\/tr>
sum<\/code><\/td>Soma dos valores<\/td><\/tr>
avg<\/code><\/td>M\u00e9dia dos valores<\/td><\/tr>
count<\/code><\/td>Contagem de registros<\/td><\/tr>
count_distinct<\/code><\/td>Contagem de valores \u00fanicos<\/td><\/tr>
min<\/code><\/td>Valor m\u00ednimo<\/td><\/tr>
max<\/code><\/td>Valor m\u00e1ximo<\/td><\/tr>
stddev<\/code><\/td>Desvio padr\u00e3o<\/td><\/tr>
variance<\/code><\/td>Vari\u00e2ncia<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Ordena\u00e7\u00e3o (order_by)<\/h2>\n\n\n\n
\"order_by\": [\n  {\"column\": \"receita\", \"direction\": \"desc\"},\n  {\"column\": \"nome\", \"direction\": \"asc\"}\n]<\/code><\/pre>\n\n\n\n
Colunas calculadas (customColumns)<\/h2>\n\n\n\n
Crie colunas derivadas usando express\u00f5es:<\/p>\n\n\n\n
\"customColumns\": [\n  {\"name\": \"lucro\", \"expression\": \"receita - custo\"},\n  {\"name\": \"margem\", \"expression\": \"(receita - custo) \/ receita * 100\"}\n]<\/code><\/pre>\n\n\n\n
As colunas calculadas podem ser usadas em columns<\/code>, where_conditions<\/code>, order_by<\/code> e dimensions<\/code>, como qualquer outra coluna.<\/p>\n\n\n\n
Fun\u00e7\u00f5es dispon\u00edveis em express\u00f5es<\/h3>\n\n\n\n
Matem\u00e1ticas:<\/strong> ABS<\/code>, CEIL<\/code>, FLOOR<\/code>, ROUND<\/code>, TRUNCATE<\/code>, MOD<\/code>, SQRT<\/code>, POWER<\/code>, EXP<\/code>, LOG<\/code>, LN<\/code>, SIGN<\/code><\/p>\n\n\n\n
Texto:<\/strong> UPPER<\/code>, LOWER<\/code>, TRIM<\/code>, LTRIM<\/code>, RTRIM<\/code>, SUBSTR<\/code>, SUBSTRING<\/code>, LENGTH<\/code>, REPLACE<\/code>, CONCAT<\/code>, SPLIT_PART<\/code>, LEFT<\/code>, RIGHT<\/code>, LPAD<\/code>, RPAD<\/code>, REVERSE<\/code>, REPEAT<\/code>, INSTR<\/code>, LOCATE<\/code>, REGEXP_EXTRACT<\/code>, REGEXP_REPLACE<\/code>, REGEXP_LIKE<\/code><\/p>\n\n\n\n
Data:<\/strong> DATE<\/code>, YEAR<\/code>, MONTH<\/code>, DAY<\/code>, HOUR<\/code>, MINUTE<\/code>, SECOND<\/code>, DATE_FORMAT<\/code>, DATE_TRUNC<\/code>, DATE_ADD<\/code>, DATE_DIFF<\/code>, CURRENT_DATE<\/code>, CURRENT_TIMESTAMP<\/code>, NOW<\/code>, EXTRACT<\/code>, TO_DATE<\/code>, TO_TIMESTAMP<\/code>, FROM_UNIXTIME<\/code><\/p>\n\n\n\n
Convers\u00e3o:<\/strong> CAST<\/code>, COALESCE<\/code>, NULLIF<\/code>, NVL<\/code>, IFNULL<\/code>, GREATEST<\/code>, LEAST<\/code><\/p>\n\n\n\n
Condicional:<\/strong> IF<\/code>, IIF<\/code>, CASE WHEN ... THEN ... ELSE ... END<\/code><\/p>\n\n\n\n
Transforma\u00e7\u00f5es de colunas (column_transformations)<\/h2>\n\n\n\n
Aplique transforma\u00e7\u00f5es sequenciais a colunas existentes. As transforma\u00e7\u00f5es s\u00e3o processadas na ordem em que aparecem.<\/p>\n\n\n\n
\"column_transformations\": {\n  \"nome\": [\n    {\"type\": \"text\", \"operation\": \"uppercase\"}\n  ],\n  \"criado_em\": [\n    {\"type\": \"date\", \"operation\": \"year_month\"}\n  ],\n  \"valor\": [\n    {\"type\": \"number\", \"operation\": \"round\", \"params\": {\"decimals\": 2}}\n  ]\n}<\/code><\/pre>\n\n\n\n
Transforma\u00e7\u00f5es de texto:<\/strong> uppercase<\/code>, lowercase<\/code>, trim<\/code>, ltrim<\/code>, rtrim<\/code>, extract_domain<\/code>, extract_before<\/code>, extract_after<\/code>, replace<\/code> (params: find<\/code>, replace<\/code>), regexp_extract<\/code> (params: pattern<\/code>), regexp_replace<\/code> (params: pattern<\/code>, replacement<\/code>)<\/p>\n\n\n\n
Transforma\u00e7\u00f5es de data:<\/strong> date<\/code>, year_month<\/code>, year_week<\/code>, year_quarter<\/code>, year<\/code>, hour<\/code>, minute<\/code>, extract_year<\/code>, extract_month<\/code>, extract_day<\/code>, extract_hour<\/code>, date_add_days<\/code> (params: days<\/code>), date_add_hours<\/code> (params: hours<\/code>), date_add_minutes<\/code> (params: minutes<\/code>)<\/p>\n\n\n\n
Transforma\u00e7\u00f5es num\u00e9ricas:<\/strong> round<\/code> (params: decimals<\/code>), floor<\/code>, ceil<\/code>, abs<\/code>, truncate<\/code> (params: decimals<\/code>), divide<\/code> (params: divisor<\/code>), multiply<\/code> (params: multiplier<\/code>)<\/p>\n\n\n\n
Convers\u00f5es de tipo:<\/strong> cast_to_text<\/code>, cast_to_int<\/code>, cast_to_number<\/code>, cast_to_date<\/code>, cast_to_timestamp<\/code><\/p>\n\n\n\n
JOINs<\/h2>\n\n\n\n
Combine dados de m\u00faltiplas tabelas:<\/p>\n\n\n\n
{\n  \"table_name\": \"pedidos\",\n  \"columns\": [\"pedidos.id\", \"clientes.nome\", \"pedidos.total\"],\n  \"column_aggregations\": [\"none\", \"none\", \"none\"],\n  \"joins\": [\n    {\n      \"tableName\": \"clientes\",\n      \"joinType\": \"LEFT\",\n      \"conditions\": [\n        {\n          \"leftColumn\": \"pedidos.cliente_id\",\n          \"rightColumn\": \"clientes.id\"\n        }\n      ]\n    }\n  ]\n}<\/code><\/pre>\n\n\n\n
Tipos de JOIN: INNER<\/code> (apenas registros com correspond\u00eancia), LEFT<\/code> (todos da tabela principal + correspond\u00eancias), RIGHT<\/code> (todos da tabela unida + correspond\u00eancias), FULL<\/code> (todos de ambas), CROSS<\/code> (produto cartesiano).<\/p>\n\n\n\n
Ao usar JOINs, prefixe os nomes das colunas com o nome da tabela (ex: pedidos.id<\/code>, clientes.nome<\/code>).<\/p>\n\n\n\n
UNION ALL<\/h2>\n\n\n\n
Combine resultados de consultas em tabelas diferentes. Todas as consultas ficam dentro do array union<\/code> (m\u00ednimo 2). Os campos order_by<\/code> e limit<\/code> ficam no n\u00edvel raiz.<\/p>\n\n\n\n
{\n  \"union\": [\n    {\n      \"table_name\": \"vendas_2025\",\n      \"dest_collection_id\": 12345,\n      \"columns\": [\"mes\", \"receita\"],\n      \"column_aggregations\": [\"none\", \"sum\"],\n      \"dimensions\": [\"mes\"]\n    },\n    {\n      \"table_name\": \"vendas_2024\",\n      \"columns\": [\"mes\", \"receita\"],\n      \"column_aggregations\": [\"none\", \"sum\"],\n      \"dimensions\": [\"mes\"]\n    }\n  ],\n  \"order_by\": [{\"column\": \"receita\", \"direction\": \"desc\"}],\n  \"limit\": 500\n}<\/code><\/pre>\n\n\n\n
Limites e restri\u00e7\u00f5es<\/h2>\n\n\n\n
Restri\u00e7\u00e3o<\/th>Valor<\/th><\/tr><\/thead>
M\u00e1ximo de registros por consulta<\/td>1.000<\/td><\/tr>
Condi\u00e7\u00f5es de filtro<\/td>Apenas AND<\/td><\/tr>
Subconsultas<\/td>N\u00e3o suportadas<\/td><\/tr>
DISTINCT<\/td>N\u00e3o suportado (use count_distinct<\/code>)<\/td><\/tr>
UNION simples<\/td>N\u00e3o suportado (apenas UNION ALL)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Exemplos completos<\/h2>\n\n\n\n
Vendas por regi\u00e3o no \u00faltimo m\u00eas<\/h3>\n\n\n\n
{\n  \"dest_collection_id\": 12345,\n  \"table_name\": \"vendas\",\n  \"columns\": [\"regiao\", \"receita\", \"pedidos\"],\n  \"column_aggregations\": [\"none\", \"sum\", \"count\"],\n  \"dimensions\": [\"regiao\"],\n  \"where_conditions\": [\n    {\"column\": \"data_venda\", \"operator\": \"LAST_MONTH\"}\n  ],\n  \"order_by\": [{\"column\": \"receita\", \"direction\": \"desc\"}]\n}<\/code><\/pre>\n\n\n\n
Top 10 clientes por valor de compra<\/h3>\n\n\n\n
{\n  \"dest_collection_id\": 12345,\n  \"table_name\": \"pedidos\",\n  \"columns\": [\"pedidos.cliente_id\", \"clientes.nome\", \"pedidos.valor\"],\n  \"column_aggregations\": [\"none\", \"none\", \"sum\"],\n  \"dimensions\": [\"pedidos.cliente_id\", \"clientes.nome\"],\n  \"joins\": [\n    {\n      \"tableName\": \"clientes\",\n      \"joinType\": \"LEFT\",\n      \"conditions\": [\n        {\"leftColumn\": \"pedidos.cliente_id\", \"rightColumn\": \"clientes.id\"}\n      ]\n    }\n  ],\n  \"order_by\": [{\"column\": \"valor\", \"direction\": \"desc\"}],\n  \"limit\": 10\n}<\/code><\/pre>\n\n\n\n
Filtro com m\u00faltiplas condi\u00e7\u00f5es<\/h3>\n\n\n\n
{\n  \"dest_collection_id\": 12345,\n  \"table_name\": \"leads\",\n  \"columns\": [\"nome\", \"email\", \"origem\", \"score\"],\n  \"where_conditions\": [\n    {\"column\": \"score\", \"operator\": \">=\", \"value\": 80},\n    {\"column\": \"origem\", \"operator\": \"IN\", \"value\": [\"google\", \"facebook\"]},\n    {\"column\": \"email\", \"operator\": \"IS NOT NULL\"},\n    {\"column\": \"criado_em\", \"operator\": \"LAST_N_DAYS\", \"value\": 90}\n  ],\n  \"order_by\": [{\"column\": \"score\", \"direction\": \"desc\"}],\n  \"limit\": 100\n}<\/code><\/pre>\n\n\n\n
<\/p>Consultas KSQL est\u00e3o em modo beta<\/cite><\/blockquote>\n\n","protected":false},"excerpt":{"rendered":"
Refer\u00eancia completa do KSQL, a linguagem de consulta da Kondado. Aprenda a filtrar, agrupar e transformar dados no Via Kondado.<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[281],"tags":[],"_links":{"self":[{"href":"https:\/\/kondado.com.br\/blog\/wiki\/wp-json\/wp\/v2\/posts\/11094"}],"collection":[{"href":"https:\/\/kondado.com.br\/blog\/wiki\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kondado.com.br\/blog\/wiki\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kondado.com.br\/blog\/wiki\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/kondado.com.br\/blog\/wiki\/wp-json\/wp\/v2\/comments?post=11094"}],"version-history":[{"count":1,"href":"https:\/\/kondado.com.br\/blog\/wiki\/wp-json\/wp\/v2\/posts\/11094\/revisions"}],"predecessor-version":[{"id":11099,"href":"https:\/\/kondado.com.br\/blog\/wiki\/wp-json\/wp\/v2\/posts\/11094\/revisions\/11099"}],"wp:attachment":[{"href":"https:\/\/kondado.com.br\/blog\/wiki\/wp-json\/wp\/v2\/media?parent=11094"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kondado.com.br\/blog\/wiki\/wp-json\/wp\/v2\/categories?post=11094"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kondado.com.br\/blog\/wiki\/wp-json\/wp\/v2\/tags?post=11094"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}