如何在Presto中生成嵌套键值结构的查询结果

Got it, let's walk through how to get that nested JSON structure you want from your sales data using Presto. The key here is leveraging Presto's aggregation functions to build nested maps, then converting that map to a properly formatted JSON string.

Here's the complete query that will give you the exact output you're looking for:

with cte (customer_id, product, sell) as (
 values (1, 'a', 100), (1, 'b', 150), (2, 'a', 90), (2, 'b', 110)
)
select to_json(map_agg(customer_id, product_sell_map)) as result
from (
    select 
        customer_id,
        map_agg(product, sell) as product_sell_map
    from cte
    group by customer_id
) customer_products;

Breakdown of how this works: #

• Inner Subquery: We first group the data by customer_id. Using map_agg(product, sell), we create a map for each customer where the key is the product name and the value is the sales amount. For example, customer 1 gets a map like {'a': 100, 'b': 150}.
• Outer Query: Next, we take those per-customer maps and aggregate them into a single top-level map using map_agg(customer_id, product_sell_map). This creates a map where each key is a customer ID, and the value is their respective product-sales map.
• Convert to JSON: Finally, the to_json() function converts the top-level map into a properly formatted JSON string, matching the output structure you specified.

When you run this query, you'll get a single column result with the value:
{"1": {"a": 100, "b": 150}, "2": {"a": 90, "b": 110}}

内容的提问来源于stack exchange，提问作者Sagar Panchal