CREATE TABLE product_catalog (
   ident number generated always as identity,
   json_document CLOB
   CONSTRAINT validate_json CHECK (json_document IS JSON));

INSERT INTO product_catalog (json_document)
   VALUES ('{
      "id": 14,
      "name": "Table",
      "price": 22.95,
      "details": {"color" : "brown",
      "stock" : "yes" }
   }');

INSERT INTO product_catalog (json_document)
   VALUES ('{
      "id": 22,
      "name": "Door",
      "price": 14.99,
      "details": {"color" : "white",
      "stock" : "yes" }
   }');

INSERT INTO product_catalog (json_document)
   VALUES ('{
      "id": 99,
      "name": "Chair",
      "price": 5.25,
      "details": {"color" : "black",
      "stock" : "no" }
   }');

SET AUTOTRACE TRACE EXPLAIN

/* dot notation */
SELECT ident,
       json.json_document.id as id,
       json.json_document.name as name,
       json.json_document.price as price
  FROM product_catalog json
 WHERE json.json_document.name = 'Chair';


/* JSON value */
SELECT ident,
       json.json_document.id as id,
       json.json_document.name as name,
       json.json_document.price as price
  FROM product_catalog json
 WHERE json_value(json_document, '$.name' returning varchar2 error on error) = 'Chair'; 

CREATE INDEX json_name
  ON product_catalog json (json.json_document.name);

/* dot notation */
SELECT ident,
       json.json_document.id as id,
       json.json_document.name as name,
       json.json_document.price as price
  FROM product_catalog json
 WHERE json.json_document.name = 'Chair';

/* JSON value */
SELECT ident,
       json.json_document.id as id,
       json.json_document.name as name,
       json.json_document.price as price
  FROM product_catalog json
 WHERE json_value(json_document, '$.name' returning varchar2 error on error) = 'Chair';

DECLARE
   recins  NUMBER := 0;
   limit   NUMBER := 3334;
BEGIN
   WHILE recins < limit LOOP

      INSERT INTO product_catalog (json_document)
         VALUES ('{
            "id": 14,
            "name": "Table",
            "price": 22.95,
            "details": {"color" : "brown",
            "stock" : "yes" }
         }');

      INSERT INTO product_catalog (json_document)
         VALUES ('{
            "id": 22,
            "name": "Door",
            "price": 14.99,
            "details": {"color" : "white",
            "stock" : "yes" }
         }');

      INSERT INTO product_catalog (json_document)
         VALUES ('{
            "id": 99,
            "name": "Chair",
            "price": 5.25,
            "details": {"color" : "black",
            "stock" : "no" }
         }');

      recins := recins + 1;
   END LOOP;
END;
/

COL name FORMAT a10
COL total FORMAT 99999

SELECT json.json_document.name as name,
       count(*) as total
  FROM product_catalog json
 GROUP BY json.json_document.name;

/* dot notation */
SELECT ident,
       json.json_document.id as id,
       json.json_document.name as name,
       json.json_document.price as price
  FROM product_catalog json
 WHERE json.json_document.name = 'Chair';


/* JSON value */
SELECT ident,
       json.json_document.id as id,
       json.json_document.name as name,
       json.json_document.price as price
  FROM product_catalog json
 WHERE json_value(json_document, '$.name' returning varchar2 error on error) = 'Chair';

-- Clean-Up

DROP TABLE product_catalog PURGE;