I am trying to parse below JSON data in column test_column from table table_a. If the label department has Electrical, dept_1 should be defaulted as 1, if label department has Electronics, dept_2 should be defaulted to 1, and if label department has Chemical, dept_3 should be defaulted to 1.

I used below query but it is not yielding what i want -

Query used:

select dept_id, 
case when dept='Electrical' then 1 else NULL end as dep1,
case when dept='Electronics' then 2 else NULL end as dep2,
case when dept='Chemical' then 3 else NULL end as dep3
from(
     SELECT 
         test_column:labels:department_id::varchar as dept_id, 
         array_to_string(test_column:labels:department,',') as dept
     FROM table_a
  );

Could you tell me what is going wrong? Am getting NULL for records having all 3 Eletrical, Eletronics, and Chemical.

Expected result:

CodePudding user response：

You have 2 options to do this differently.

Option #1: Flatten your array and then re-aggregate back for your case statements:

WITH x AS (
    SELECT parse_json('{"labels": {"department_id": "1","department": ["Electrical","Electronics","Chemical"]}}') as var
)
SELECT  x.var:labels:department_id::integer as dept_id, 
        max(case when y.value::varchar='Electrical' then 1 else NULL end) as dep1,
        max(case when y.value::varchar='Electronics' then 2 else NULL end) as dep2,
        max(case when y.value::varchar='Chemical' then 3 else NULL end) as dep3
FROM x,
lateral flatten (input=>var:labels:department) y
group by 1;

Option #2 - since your case statement is just evaluating the presence of a value, you can just look at the array using a ARRAY_CONTAINS function to evaluate:

WITH x AS (
    SELECT parse_json('{"labels": {"department_id": "1","department": ["Electrical","Electronics","Chemical"]}}') as var
)
SELECT  x.var:labels:department_id::integer as dept_id, 
        case when array_contains('Electrical'::variant, var:labels:department::array) then 1 else NULL end as dep1,
        case when array_contains('Electronics'::variant, var:labels:department::array) then 2 else NULL end as dep2,
        case when array_contains('Chemical'::variant, var:labels:department::array) then 3 else NULL end as dep3
FROM x;

CodePudding user response：

Please try the below query:

SELECT 
    test_column:labels:department_id::varchar as dept_id, 
    IFF(ARRAY_CONTAINS('Electrical'::VARIANT, test_column:labels:department), 1, NULL) as DEP_1,
    IFF(ARRAY_CONTAINS('Electronics'::VARIANT, test_column:labels:department), 1, NULL) as DEP_2,
    IFF(ARRAY_CONTAINS('Chemical'::VARIANT, test_column:labels:department), 1, NULL) as DEP_3
FROM table_a;