I have a varchar column

{" l  l": 2, " m  m": 2, " xs  xs": 2, " xxs  xxs": 2, "s  s": 0, "xl  xl": 0}

How do I filter it to get only keys with values greater then 0? Expected output is

{" l  l": 2, " m  m": 2, " xs  xs": 2, " xxs  xxs": 2}

CodePudding user response：

Your data looks like json so you can try to process it like one - parse as json, turn it into map(varchar, integer) (or double), filter, turn back into string:

-- sample data
with dataset(json_str) as (
    values ('{" l  l": 2, " m  m": 2, " xs  xs": 2, " xxs  xxs": 2, "s  s": 0, "xl  xl": 0}')
)

-- query
select json_format(cast( 
        map_filter(cast(json_parse(json_str) as  map(varchar, integer)), (k, v) -> v > 0) 
    as json))
from dataset;

Output:

                      _col0
-------------------------------------------------
 {" l  l":2," m  m":2," xs  xs":2," xxs  xxs":2}

CodePudding user response：

One option to carry out this task is by:

• using JSONB_EACH_TEXT to convert your json to table
• filtering out values equal to 0
• recreating your json with JSON_OBJECT ARRAY_AGG

SELECT JSON_OBJECT(ARRAY_AGG(k), ARRAY_AGG(v))
FROM tab
CROSS JOIN LATERAL JSONB_EACH_TEXT(tab.string) AS j(k,v)
WHERE v::int > 0

Output:

Check the demo here.