How to fetch data from oracle to nested json object using javascript?

Question

My current json format is like this:

{
  {
    'id' : 61,
    'item' : 'apple pie,banana split',
    'quantity' : '2,1',
    'price' : '100000,50000' 
  },
  {
    'id' : 62,
    'item' : 'beef steak,salad',
    'quantity' : '1,2',
    'price' : '50000,100000'
  }
}

what i want is like this:

{
  {
    'id' : 61,
    'item' : 
            {
              '1': {'name': 'apple pie', 'quantity' : '2','price': '100000'},
              '2': {'name': 'banana split', 'quantity' : '1','price': '50000'}
        }
  },
  {
    'id' : 62,
    'item' : 
            {
              '1': {'name': 'beef steak', 'quantity' : '1','price': '50000'},
              '2': {'name': 'salad', 'quantity' : '2','price': '100000'}
        }
  }
}

My database is like this:

split_id  item                      quantity  price
61        apple pie~|~banana split  2~|~1     100000~|~50000
62        beef steak~|~salad        1~|~2     50000~|~100000

I use "~|~" because if I use "," then it will recognize as the next column value in query. And I'm not create a new row to avoid multiple value of item, quantity,and price because it represents different transaction.

I found answer for this question but it is in php and mysql. I can't find the javascript and oracle one. I appreciate any kind of help for this question. Thank you.

Answer 1

In Oracle you can split the comma delimited strings and then aggregate to 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;

Which, for the sample data:

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;

Outputs:

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<>fiddle here

Answer 2

Your current JSON object format is wrong. You can make that an array to make it much simpler. Something like this you can do:

var data = [ { 'id' : 61, 'item' : 'apple pie,banana split', 'quantity' : '2,1', 'price' : '100000,50000', }, { 'id' : 62, 'item' : 'beef steak,salad', 'quantity' : '1,2', 'price' : '50000,100000', }];

var result = data.map(({id,...rest})=>({id, items:Array.from({length:2},(_,i)=>Object.fromEntries(Object.entries(rest).map(([k,v])=>[k,v.split(',')[i]])))}));

console.log(result);

Here I am using map with a combination of fromEntries and entries to make an object. In Array.from you can see length:2 is hardcoded but you can change it accordingly.