Question 214360 in PostgreSQL Question posted in
The official documentation can be found here.

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

2

Answers


  1. 0 
    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;
    json_object
    {"A": 10}
    {"A": 10, "B": 5, "C": 10}
    {"A": 10, "B": 5}

    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);


    CREATE TABLE

    INSERT 0 1

    INSERT 0 1

    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;
    json_object
    {"A": 10}
    {"A": 10, "B": 5, "C": 10}
    {"A": 10, "B": 5} 
    SELECT 3

    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;

    CREATE FUNCTION
    json_object
    {"A": 10} 
    SELECT 1
    json_object
    {"A": 10, "B": 5, "C": 10} 
    SELECT 1
    json_object
    {"A": 10, "B": 5} 
    SELECT 1

    fiddle

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search