JSON parse with sql

Case sensitive for keys

JSON_OBJECT_T is the type for JSON object (TEXT OR CLOB) the method get is case senstive i.e. json_obj.get(‘key’) is different than json.obj.get(‘KEY’)

put method to add new key-value

DECLARE 
   l_object   json_object_t; 
BEGIN 
   -- Elements can be added to an object... 
   -- ...conditionally... 
   -- ...overloaded... 
   -- ...and removed... 
   -- ...or renamed... 
   l_object := json_object_t.parse ('{"Name":"Elvis"}'); 
   l_object.put ('Nickname', 'The King'); 
   DBMS_OUTPUT.put_line (l_object.to_string); 

   -- PUT is an up-sert! 

   l_object.put ('Nickname', 'Not The King'); 
   DBMS_OUTPUT.put_line (l_object.to_string); 

   l_object.put ('Age', 11); 
   DBMS_OUTPUT.put_line (l_object.to_string); 

   IF NOT l_object.has ('Age') 
   THEN 
      l_object.put ('Age', 22); 
   END IF; 

   l_object.remove ('Age'); 
   DBMS_OUTPUT.put_line (l_object.to_string); 

   l_object.rename_key ('Nickname', 'RealityIs'); 
   DBMS_OUTPUT.put_line (l_object.to_string); 
END;

test json easily in oracle

with json as
( select '["mit", "nach", "nebst", "bei"]' doc
  from   dual
)
SELECT value
FROM json_table( (select doc from json) , '$[*]'
                COLUMNS (value PATH '$'
                        )
               )

create or replace FUNCTION json_array_to_string_tbl ( 
    p_json_array IN VARCHAR2 
    ) RETURN string_tbl_t 
    is 
      l_string_tbl string_tbl_t:= string_tbl_t(); 
    begin 
       if p_json_array is not null and length(p_json_array)>0 
       then 
           SELECT value 
           bulk collect into l_string_tbl 
           FROM json_table( p_json_array, '$[*]' 
                            COLUMNS (value PATH '$' 
                           ) 
          );  
       end if; 
       return l_string_tbl; 
    end json_array_to_string_tbl;

with json as  
( select '[{"firstName": "Tobias", "lastName":"Jellema"},{"firstName": "Anna", "lastName":"Vink"} ]' doc  
  from   dual  
)  
SELECT first_name 
,      last_name 
FROM  json_table( (select doc from json) , '$[*]'  
                COLUMNS ( first_name PATH '$.firstName'  
                        , last_name PATH '$.lastName'  
                        )  
               )

group by is more powerful than distinct

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

SELECT DISTINCT a,b,c FROM t

is roughly equivalent to:

SELECT a,b,c FROM t GROUP BY a,b,c

It’s a good idea to get used to the GROUP BY syntax, as it’s more powerful.

For your query, I’d do it like this:

UPDATE sales
SET status='ACTIVE'
WHERE id IN
(
    SELECT id
    FROM sales S
    INNER JOIN
    (
        SELECT saleprice, saledate
        FROM sales
        GROUP BY saleprice, saledate
        HAVING COUNT(*) = 1 
    ) T
    ON S.saleprice=T.saleprice AND s.saledate=T.saledate
 )