| 來源_id | source_groupid | 源_納米 | 類別編號 | 級別_id |
|---|---|---|---|---|
| 12345 | 34 | 美國廣播公司 | 7 | 2 |
| 67549 | 胃腸道 | 5 | 1 | |
| 24751 | BL | 6 |
結果
{"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
我需要根據以下模式進行列連接。例如，如果 group_id 或 category_id 中沒有條目，那么如何撰寫代碼使模板更改并查找第 2 行和第 3 行，如下所示。
{"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}
uj5u.com熱心網友回復：
好吧，在 Oracle（您使用過哪個標簽）中，CONCAT函式只接受兩個引數，因此 - 該代碼將不起作用。
相反，使用雙管道||運算子。至于你的主要問題，CASE它是。
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>
uj5u.com熱心網友回復：
從 Oracle 12 開始，不要手動構建 JSON；使用該JSON_OBJECT功能，然后您可以使用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;
其中，對于樣本資料：
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;
輸出：
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<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/367866.html