Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;
2 rows affected

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;


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}}}]


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_QUERY(
         JSON_ARRAYAGG(
           items FORMAT JSON
           RETURNING CLOB
         ),
         '$'
         RETURNING CLOB PRETTY
       ) AS value
FROM   split_data;


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
      }
    }
  }
]