CODEWITHSUNDEEP
jsonpostgresql
Vern
Vern

Reputation: 147

Postgres row to json structure

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.

Upvotes: 0

Views: 84

Answers (1)

Kristo Mägi
Kristo Mägi

Reputation: 1684

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

Upvotes: 1

Related Questions