Question 109930 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – Filter to have keys with specific value only presto

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}
Tags:

2

Answers


  1. 0

    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:

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

    Check the demo here.

    Login or Signup to reply.
  2. 0

    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}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search