Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE myjson( doc VARCHAR2(4000) check (doc is JSON) );

INSERT INTO myjson VALUES('{
  "totalResultsCount": 19,
  "geonames": [
    {
      "geonameId": 2593109,
      "fcode": "ADM1"
    },
    {
      "geonameId": 3336899,
      "fcode": "ADM1"
    }]
}    
');         

1 rows affected

SELECT ROW_NUMBER() OVER (ORDER BY 1) AS row_id, js.product 
  FROM myjson
 CROSS JOIN
       JSON_TABLE( 
                 doc, '$.geonames[*]' COLUMNS (
                                      product VARCHAR(100) PATH '$.geonameId',
                                      fcode   VARCHAR(100) PATH '$.fcode'
                                      )
        ) js
    WHERE js.fcode = 'ADM1'   
 


ROW_ID
PRODUCT


1
2593109

2
3336899