How to group by on a Query json with SQL?

[R_S_I_F]

I'd like to query my database once for the row data, then use SQL to query JSON and display the group by data in the UI with a table.

[pyrrho]

Hello!

I believe the table already has the Group By functionality built-in so you can just use the database query to populate the table and then setup the column(s) for the grouping:

Is there another transformation that you need to perform on the database rows before grouping?

[R_S_I_F]

the table "group by" doesn't work properly at the moment and that's why i'd like to use the SQL to query JSON

[Paulo]

Hi @R_S_I_F, what issues are we currently experiencing with 'group by'?

[R_S_I_F]

Hey @Paulo
Aggregation does not work for calculated fields. Sorting does not work, filtering is poor, and conditional colors do not work.

[Paulo]

Column aggregation to individual columns require:

• The column format is either numerical or boolean.
• The column you want to aggregate is not the same column set in the Group by rows setting.

For example, here is a dynamic column where I'm calculating the length of the name (first name + last name). Aggregation works if the format is a Number (or boolean).:

Aggregation for each group:

Sorting for each group works like a charm. Are we trying to sort the groups as a whole?

What limitations have we found with filtering? It removes all groups and rows where there wasn't a match:

Conditional colors evaluate to each individual row, the group gets the color of the majority of rows that matched the condition:

[R_S_I_F]

Hey @Paulo

As for aggregation sum works but calculated fields such as ROAS for example (revenue/spend) don't work...

Sort by a whole doesn't work

Filtering:
Filter based on breakdown doesn't work.

Conditional colors are set on a specific field and work only when the group by doesn't on.

[Paulo]

Thank you for expanding on the limitations you've found, I'll pass your feedback along.

To your original question, using 'Query JSON with SQL,' we group the same way we would with pure SQL. However, if we are selecting *, it will return data from the first row in the group.

For example, here is a group by 'country' using Query JSON with SQL:

But if we select the whole row ('*'), we get data from the first row in the group.

Which is not ideal but we should still be able to aggregate.

[R_S_I_F]

Hey @Paulo
I tried it and it doesn't work
with the error * country:undefined

Although the country returns from the query.

Any idea?

[Paulo]

Check search_daily_raw.data. Have we formatted the data as an array?

[R_S_I_F]

Hey @Paulo
Now, it works, but double fields return as Strings, which prevents me from grouping by with aggrigation statements like sum()
Even if I cast double, it still doesn't work.
Any idea?

[Paulo]

Could you share a screenshot of the query?

[R_S_I_F]

Hey @Paulo

Sure:

SELECT 
  country,
cast(revenue as double)
FROM 
  {{ data_raw.data }}
group by country

[Paulo]

If doubles are evaluating to strings, let's parse them into floats on way out of the SQL query.

For example, here is the output of a SQL query that is evaluating the price float to a string:

After we format the data into an array, we can map it to return a new one where all price strings are parsed into floats:

3

Here is the JS:

return formatDataAsArray(data).map(order => {
  return {...order, price: parseFloat(order.price)}
  }
)

This will persist when querying JSON with SQL:

Let us know how it goes.

[R_S_I_F]

Great now it works.
But on the SQL request I get "Result exceeded maximum size of 100 MB"

[R_S_I_F]

Also it does't let me use JS as a dynamic metric:
SELECT
{{select1.value }},
sum(cost)
FROM
{{ daily_raw.data }}
group by {{select1.value }}

• $0:"country"

select1.value the name of the metric I want to group by

[Paulo]

@R_S_I_F

But on the SQL request I get "Result exceeded maximum size of 100 MB"

The query limit of 100MB applies to any response from a query. Transforming the results should occur afterward and shouldn’t impact the size limit. Could you please verify if the query succeeds without the JS iteration but fails when it's added? If so, I’m happy to create a report.

@R_S_I_F

Also it does't let me use JS as a dynamic metric:

Setting fields dynamically is not possible. This applies for both SQL queries and Query JSON with SQL:

We support setting the literal values: