How do I get the value of Total No. of Transaction and Sum of Transaction from below string using or-CodePudding

{
  "timestamp": 1654752742887,
  "status": "OK",
  "statusCode": 200,
  "message": "GET response successful.",
  "content": [
    {
      "Total No. of Transaction": 1,
      "Sum of Transaction": 473
    }
  ]
}

CodePudding user response：

This is a JSON value which can be invoked from a SQL SELECT statement and extracted through use of JSON_TABLE() function such as

WITH t(js) AS
(
 SELECT '{
           "timestamp": 1654752742887,
           "status": "OK",
           "statusCode": 200,
           "message": "GET response successful.",
           "content": [
             {
               "Total No. of Transaction": 1,
               "Sum of Transaction": 473
             }
           ]
         }' 
   FROM dual
)
 SELECT "Total No. of Transaction", "Sum of Transaction" 
   FROM t,
        JSON_TABLE(js, '$'
                        COLUMNS (NESTED PATH '$.content[*]'
                        COLUMNS ("Total No. of Transaction" VARCHAR2 PATH '$."Total No. of Transaction"', 
                                 "Sum of Transaction"       VARCHAR2 PATH '$."Sum of Transaction"') 
                   ));

Total No. of Transaction	Sum of Transaction
1	473

provided that the DB version is at least 12.1.0.2

Demo

CodePudding user response：

That's a JSON, so you'd

SQL> select t.col.content."Total No. of Transaction" total_no,
  2         t.col.content."Sum of Transaction" sum_of_trans
  3  from test t;

TOTAL_NO        SUM_OF_TRANS
--------------- ---------------
1               473

SQL>