Hive : How to explode a JSON column with an array, and embedded in a CSV file?
I was having similar kind of requirement. The solution from this link helped me solve it.BTW, below is the query for your requirement assuming all the columns in your DB_TABLE are of type 'String'.
SELECT ProductId, IngestTime, split(split(results,",")[0],':')[1] AS OrderId, regexp_replace(split(split(results,",")[1],':')[1], "[\\]|}]", "") AS OrderLocation FROM (SELECT ProductId, IngestTime, split(translate(ProductOrders, '"\\[|]|\""',''), "},") AS r FROM DB_TABLE) t1 LATERAL VIEW explode(r) rr AS results