在 Oracle 中，您可以拆分逗號(hào)分隔的字符串，然后聚合為 JSON：
WITH bounds ( id, idx, item, i_start, i_end, quantity, q_start, q_end, price, p_start, p_end ) AS (
? SELECT split_id,
? ? ? ? ?1,
? ? ? ? ?item,
? ? ? ? ?1,
? ? ? ? ?INSTR( item, '~|~', 1 ),
? ? ? ? ?quantity,
? ? ? ? ?1,
? ? ? ? ?INSTR( quantity, '~|~', 1 ),
? ? ? ? ?price,
? ? ? ? ?1,
? ? ? ? ?INSTR( price, '~|~', 1 )
? FROM? ?table_name t
UNION ALL
? SELECT id,
? ? ? ? ?idx + 1,
? ? ? ? ?item,
? ? ? ? ?CASE i_end WHEN 0 THEN 0 ELSE i_end + 3 END,
? ? ? ? ?CASE i_end WHEN 0 THEN 0 ELSE INSTR( item, ',', i_end + 3 ) END,
? ? ? ? ?quantity,
? ? ? ? ?CASE p_end WHEN 0 THEN 0 ELSE q_end + 3 END,
? ? ? ? ?CASE p_end WHEN 0 THEN 0 ELSE INSTR( quantity, ',', q_end + 3 ) END,
? ? ? ? ?price,
? ? ? ? ?CASE q_end WHEN 0 THEN 0 ELSE p_end + 3 END,
? ? ? ? ?CASE q_end WHEN 0 THEN 0 ELSE INSTR( price, ',', p_end + 3 ) END
? FROM? ?bounds
? WHERE? i_end > 0
? OR? ? ?q_end > 0
? OR? ? ?p_end > 0
),
split_data ( items ) AS (
? SELECT JSON_OBJECT(
? ? ? ? ? ?KEY 'id' VALUE id,
? ? ? ? ? ?KEY 'items' VALUE
? ? ? ? ? ? ?JSON_OBJECTAGG(
? ? ? ? ? ? ? ?KEY? ?TO_CHAR( idx )
? ? ? ? ? ? ? ?VALUE JSON_OBJECT(
? ? ? ? ? ? ? ? ? ? ? ?KEY? ?'item'
? ? ? ? ? ? ? ? ? ? ? ?VALUE CASE
? ? ? ? ? ? ? ? ? ? ? ? ? ? ?WHEN i_start > 0 AND i_end = 0
? ? ? ? ? ? ? ? ? ? ? ? ? ? ?THEN SUBSTR( item, i_start )
? ? ? ? ? ? ? ? ? ? ? ? ? ? ?ELSE SUBSTR( item, i_start, i_end - i_start )
? ? ? ? ? ? ? ? ? ? ? ? ? ? ?END,
? ? ? ? ? ? ? ? ? ? ? ?KEY? ?'quantity'
? ? ? ? ? ? ? ? ? ? ? ?VALUE TO_NUMBER(
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?CASE
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?WHEN q_start > 0 AND i_end = 0
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?THEN SUBSTR( quantity, q_start )
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?ELSE SUBSTR( quantity, q_start, q_end - q_start )
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?END
? ? ? ? ? ? ? ? ? ? ? ? ? ? ?),
? ? ? ? ? ? ? ? ? ? ? ?KEY? ?'price'
? ? ? ? ? ? ? ? ? ? ? ?VALUE TO_NUMBER(
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?CASE
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?WHEN p_start > 0 AND p_end = 0
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?THEN SUBSTR( price, p_start )
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?ELSE SUBSTR( price, p_start, p_end - p_start )
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?END
? ? ? ? ? ? ? ? ? ? ? ? ? ? ?)
? ? ? ? ? ? ? ? ? ? )
? ? ? ? ? ? ? ? ? ? RETURNING CLOB
? ? ? ? ? ? ? )
? ? ? ? )
? FROM? ?bounds
? GROUP BY id
)
SELECT JSON_ARRAYAGG(
? ? ? ? ?items FORMAT JSON
? ? ? ? ?RETURNING CLOB
? ? ? ?) AS value
FROM? ?split_data;
其中，對(duì)于樣本數(shù)據(jù)：
CREATE TABLE table_name ( split_id, item, quantity, price ) AS
SELECT 61, 'apple pie~|~banana split', '2~|~1', '100000~|~50000' FROM DUAL UNION ALL
SELECT 62, 'beef steak~|~salad',? ? ? ?'1~|~2', '50000~|~100000' FROM DUAL;
輸出：
VALUE
----------------------------------------
[
? {
? ? "id" : 61,
? ? "items" :
? ? {
? ? ? "1" :
? ? ? {
? ? ? ? "item" : "apple pie",
? ? ? ? "quantity" : 2,
? ? ? ? "price" : 100000
? ? ? },
? ? ? "2" :
? ? ? {
? ? ? ? "item" : "banana split",
? ? ? ? "quantity" : 1,
? ? ? ? "price" : 50000
? ? ? }
? ? }
? },
? {
? ? "id" : 62,
? ? "items" :
? ? {
? ? ? "1" :
? ? ? {
? ? ? ? "item" : "beef steak",
? ? ? ? "quantity" : 1,
? ? ? ? "price" : 50000
? ? ? },
? ? ? "2" :
? ? ? {
? ? ? ? "item" : "salad",
? ? ? ? "quantity" : 2,
? ? ? ? "price" : 100000
? ? ? }
? ? }
? }
]
db<>
