我正在嘗試決議表 table_a 中 test_column 列中的以下 JSON 資料。如果標簽部門有Electrical，dept_1應該默認為1，如果標簽部門有Electronics，dept_2應該默認為1，如果標簽部門有Chemical，dept_3應該默認為1。
| 測驗列 |
|---|
| {"labels": {"department_id": "1","department": ["Electrical","Electronics","Chemical"]}} |
| {"labels": {"department_id": "2","department": ["電氣"]}} |
我使用了下面的查詢，但它沒有產生我想要的 -
使用的查詢：
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
);
你能告訴我出了什么問題嗎？對于具有全部 3 個電氣、電子和化學的記錄，我正在獲取 NULL。
預期結果：
| 部門編號 | 部門_1 | 部門_2 | 部門_3 |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 2 | 1 | 空值 | 空值 |
uj5u.com熱心網友回復：
您有 2 個選項可以以不同的方式執行此操作。
選項#1：展平你的陣列，然后重新聚合你的case陳述句：
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;
選項 #2 - 因為你的 case 陳述句只是評估一個值的存在，你可以使用一個ARRAY_CONTAINS函式來查看陣列來評估：
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;
uj5u.com熱心網友回復：
請嘗試以下查詢：
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/346600.html