Presto查询条件字段包含问题：如何仅保留值>0的a、b、c字段

Got it, I see exactly what's going on here. When you tried using CASE WHEN to set values to null for entries <=0, the JSON map still retains the key with a null value—instead of removing the key entirely. That’s because your original query builds a fixed map with all three keys (a, b, c) no matter what their values are.

The fix is to dynamically construct the inner map only with keys where the corresponding value is greater than 0. Here's how to adjust your query in Presto:

SELECT 
  json_format(
    cast(
      MAP(
        ARRAY['random_name'], 
        ARRAY[
          MAP_FROM_ENTRIES(
            ARRAY_CONCAT(
              CASE WHEN a > 0 THEN ARRAY[ROW('a', a)] ELSE ARRAY[] END,
              CASE WHEN b > 0 THEN ARRAY[ROW('b', b)] ELSE ARRAY[] END,
              CASE WHEN c > 0 THEN ARRAY[ROW('c', c)] ELSE ARRAY[] END
            )
          )
        ]
      ) AS JSON
    )
  ) AS metadata 
FROM a_table_with_a_b_c;

Let’s break down the key changes: #

• ROW('a', a): Creates a key-value pair entry only if a > 0. If the condition fails, we use an empty array (ARRAY[]) to skip adding that entry.
• ARRAY_CONCAT: Merges all the valid key-value entry arrays into one single array of entries.
• MAP_FROM_ENTRIES: Converts that combined array into a map—this map will only include keys where the value was positive, since we excluded invalid entries entirely.
• The rest of the query wraps this dynamic map into your original outer structure and formats it as valid JSON.

Example Output: #

If your table has a row where a=5, b=-1, c=3, the resulting metadata will look like this:

{"random_name": {"a": 5, "c": 3}}

No trace of the b key since its value wasn’t positive—exactly what you wanted!

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