Result

{"id": 12345, "groupid": 34, "name": ABC, "category_id": 7, "level_id": 2}
{"id": 67549, "groupid": , "name": GI, "category_id": 5, "level_id": 1}

SELECT CONCAT ('{','"id": ', source_id,', ', '"groupid": ', source_groupid,', ','"name": ',source_nm,', ','"category_id": ',category_id,', ', '"level_id": ', level_id, '}') as full_info
FROM table

I need to do column concatenation according to the following pattern. If, for example, there is no entry in the group_id or category_id, then how to write the code so that the template changes and looks for lines 2 and 3 as follows.

{"id": 12345, "groupid": 34, "name": ABC, "category_id": 7, "level_id": 2}
{"id": 67549, "name": GI, "category_id": 5, "level_id": 1}
{"id": 24751, "name": BL, "level_id": 6}

CodePudding user response：

Well, in Oracle (which tag you've used), CONCAT function accepts only two arguments, and - therefore - that code won't work.

Instead, use a double pipe || operator. As of your main problem, CASE it is.

SQL> with test (source_id, source_groupid, source_nm, category_id, level_id) as
  2    (select 12345,   34, 'ABC', 7,    2    from dual union all
  3     select 67549, null, 'GI' , 5,    1    from dual union all
  4     select 24751, null, 'BL' , null, 6  from dual
  5    )
  6  select '{' || '"id": ' || source_id ||
  7    case when source_groupid is not null then ', "groupid": '     || source_groupid end ||
  8    case when source_nm      is not null then ', "name": '        || source_nm      end ||
  9    case when category_id    is not null then ', "category_id": ' || category_id    end ||
 10    case when level_id       is not null then ', "level_id": '    || level_id       end || '}'
 11    as result
 12  from test;

RESULT
--------------------------------------------------------------------------------
{"id": 12345, "groupid": 34, "name": ABC, "category_id": 7, "level_id": 2}
{"id": 67549, "name": GI, "category_id": 5, "level_id": 1}
{"id": 24751, "name": BL, "level_id": 6}

SQL>

CodePudding user response：

From Oracle 12, don't build JSON by hand; use the JSON_OBJECT function and then you can use ABSENT ON NULL:

SELECT JSON_OBJECT(
         KEY 'id'          VALUE source_id,
         KEY 'groupid'     VALUE source_groupid,
         KEY 'name'        VALUE source_nm,
         KEY 'category_id' VALUE category_id,
         KEY 'level_id'    VALUE level_id
         ABSENT ON NULL
       ) As json
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (source_id, source_groupid, source_nm, category_id, level_id) AS
SELECT 12345, 34,   'ABC', 7,    2 FROM DUAL UNION ALL
SELECT 67549, NULL, 'GI',  5,    1 FROM DUAL UNION ALL
SELECT 24751, NULL, 'BL',  NULL, 6 FROM DUAL;

Outputs:

JSON
{"id":12345,"groupid":34,"name":"ABC","category_id":7,"level_id":2}
{"id":67549,"name":"GI","category_id":5,"level_id":1}
{"id":24751,"name":"BL","level_id":6}

db<>fiddle here