Dynamically build json with json_build_object

I am using json_build_object to build a json object using other columns:

json_build_object(value1, qty1, value2, qty2, value3, qty3)

Every pair (value, qty) go together.

I only want the json object to contain the pairs that don't have an empty value.

At the moment, each pair in json_build_object is hardcoded. How can I change that?

I have this so far:

create table json_object (id integer, value1 varchar, qty1 integer, value2 varchar,  qty2 integer, value3 varchar,  qty3 integer);

insert into json_object values (1, 'A', 10, '', 0, '', 0);
insert into json_object values (2, 'A', 10, 'B', 5, 'C', 10);
insert into json_object values (3, 'A', 10, 'B', 5, '', 0);

select json_build_object(value1, qty1, value2, qty2, value3, qty3) from json_object;

 json_build_object   
----------------------
{"A" : 10, "" : 0, "" : 0}
{"A" : 10, "B" : 5, "C" : 10}
{"A" : 10, "B" : 5, "" : 0}

but I want the output to be:

{"A" : 10}
{"A" : 10, "B" : 5, "C" : 10}
{"A" : 10, "B" : 5}

create table json_object (id integer, value1 varchar, qty1 integer, value2 varchar,  qty2 integer, value3 varchar,  qty3 integer);

insert into json_object values (1, 'A', 10, '', 0, '', 0);
insert into json_object values (1, 'A', 10, 'B', 5, 'C', 10);
insert into json_object values (1, 'A', 10, 'B', 5, '', 0);

SELECT 
  CASE 
    WHEN value1 <> '' AND value2 <> '' AND value3 <> '' THEN 
      jsonb_build_object(value1, qty1, value2, qty2, value3, qty3)
    WHEN value1 <> '' AND value2 <> '' THEN 
      jsonb_build_object(value1, qty1, value2, qty2)
    ELSE 
      jsonb_build_object(value1, qty1)
  END AS json_object
FROM json_object;

fiddle

create table json_object (id integer, value1 varchar, qty1 integer, value2 varchar,  qty2 integer, value3 varchar,  qty3 integer);

insert into json_object values (1, 'A', 10, '', 0, '', 0);
insert into json_object values (1, 'A', 10, 'B', 5, 'C', 10);
insert into json_object values (1, 'A', 10, 'B', 5, '', 0);

> status
> CREATE TABLE
>

> status
> INSERT 0 1
>

> status
> INSERT 0 1
>

> status
> INSERT 0 1
>

SELECT 
  CASE 
    WHEN value1 <> '' AND value2 <> '' AND value3 <> '' THEN 
      jsonb_build_object(value1, qty1, value2, qty2, value3, qty3)
    WHEN value1 <> '' AND value2 <> '' THEN 
      jsonb_build_object(value1, qty1, value2, qty2)
    ELSE 
      jsonb_build_object(value1, qty1)
  END AS json_object
FROM json_object;

CREATE OR REPLACE FUNCTION generate_json_object(value1 varchar, qty1 integer, value2 varchar, qty2 integer, value3 varchar, qty3 integer)
RETURNS jsonb AS $$
DECLARE
  json_obj jsonb;
BEGIN
  json_obj := jsonb_build_object(value1, qty1);
  
  IF value2 <> '' THEN
    json_obj := jsonb_set(json_obj, array[value2], to_jsonb(qty2));
  END IF;
  
  IF value3 <> '' THEN
    json_obj := jsonb_set(json_obj, array[value3], to_jsonb(qty3));
  END IF;
  
  RETURN json_obj;
END;
$$ LANGUAGE plpgsql;

SELECT generate_json_object('A', 10, '', 0, '', 0) AS json_object;
SELECT generate_json_object('A', 10, 'B', 5, 'C', 10) AS json_object;
SELECT generate_json_object('A', 10, 'B', 5, '', 0) AS json_object;

> status
> CREATE FUNCTION
>

fiddle

This will work so long as the column names follow a regular convention:

with transpose as (
  select id, to_jsonb(json_object) - 'id' as j
    from json_object
), associate as (
  select id, k, 
         left(k, 1) as ktype,
         replace(replace(k, 'qty', ''), 'value', '')::int as knum,
         v
    from transpose
         cross join lateral jsonb_each_text(j) as e(k,v)
)
select jsonb_object_agg(v1.v, v2.v)
  from associate v1
       join associate v2 
         on (v1.id, 'q', v1.knum) = (v2.id, v2.ktype, v2.knum)
 where v1.ktype = 'v'
   and coalesce(v1.v, '') != '' 
 group by v1.id;

Working fiddle