Postgres row to json structure

Question

I need to get the columns from a row in a table into a predefined json structure, BUT only those that are not Empty.

I have written the following code but now I want to know how I can only add a certain key if it actually has a value.

  IF(i_zone_id NOTNULL) THEN
    SELECT to_jsonb(('[{"n": "'       || coalesce("name"::text, '') || '",
      "id": "'                        || coalesce(controller_zone_id::text, '') ||'",
      "calc": "'                      || coalesce(calculate_using::text, '') || '",
      "aa": "'                        || coalesce(anode_area::text, '') || '",
      "ca": "'                        || coalesce(cathode_area::text, '') || '",
      "startMonitoringInterval": "'   || coalesce(start_mon_interval::text, '') || '",
      "interval": "'                  || coalesce("interval"::text, '') || '",
      "interval1": "'                 || coalesce(interval1::text, '') || '",
      "mt": "'                        || coalesce(monitoring::text, '') || '",
      "totalInterval": "'             || coalesce(total_interval::text, '') || '"' ||
      t_nodes ||
      t_ppsus ||
      '}]')::jsonb)
    FROM  cfg_zn
    WHERE zone_id = i_zone_id
    INTO  jb_zone;

I want to prevent keys being added to the json array if they are empty.

Kristo Mägi · Accepted Answer · 2017-05-29 17:03:44Z

Something like this?

SELECT  -- Eliminate all keys with 'null' values
        jsonb_strip_nulls(
            -- Build json objects structure
            jsonb_build_object(
                'n', "name"::text,
                'id', controller_zone_id::text,
                'calc', controller_zone_id::text,
                'aa', anode_area::text,
                'ca', cathode_area::text,
                'startMonitoringInterval', start_mon_interval::text,
                'interval', "interval"::text,
                'interval1', interval1::text,
                'mt', monitoring::text,
                'totalInterval', total_interval::text
            )
            -- Concatenate jsonb columns
            || t_nodes
            || t_ppsus
        )
FROM    cfg_zn
WHERE   zone_id = i_zone_id
INTO    jb_zone;

I assumed t_nodes and t_ppsus were self already jsons.

More about json(b) functions and operators: https://www.postgresql.org/docs/current/static/functions-json.html

Collectives™ on Stack Overflow

Postgres row to json structure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related