How to select subset JSON with PostgresQL?
You can remove the empty values during aggregation. And use an array operator in the final select to get only rows that contain a specific key:

SELECT tmp.* 
FROM (
  SELECT id, 
         ARRAY(SELECT t.k
               from json_each_text(json_data) as t(k,v)
               where nullif(trim(t.v),'') is not null) AS keys
  FROM json_table
) tmp 
WHERE keys &amp;&amp; array['foo','bar']
;


If you want the complete JSON values, rather than just the keys, you can use something like this:

SELECT tmp.* 
FROM (
  SELECT id, 
         (SELECT jsonb_object_agg(t.k, t.v)
               from json_each_text(json_data) as t(k,v)
               where nullif(trim(t.v),'') is not null) AS keys
  FROM json_table
) tmp 
WHERE keys ?| array['foo','bar']
;


(Note that I am using jsonb for the result in this case as that is much more flexible to handle. In general it's recommended to prefer jsonb over json these days)



If you need to cleanup the json frequently, you might want to consider writing a function for that:

create function non_empty_values(p_input jsonb)
  returns jsonb
as
$$
  SELECT jsonb_object_agg(t.k, t.v)
  from jsonb_each(jsonb_strip_nulls(p_input)) as t(k,v)
  where trim(t.v::text) &lt;&gt; '';
$$
language sql
immutable;