....
If you're trying to find documents with a particular value for foo and a given letID with the bar array for this object
....

Another possibility of the above case would be to use JSON_EXISTS something like this:

demo@PDB1> select json_query( x ,'$' pretty )
  2  from t
  3  where json_exists( x, '$.foo?(@==100032830)')
  4  and json_exists( x, '$.bar.letID?(@=="2234832333")')
  5  /

JSON_QUERY(X,'$'PRETTY)
------------------------------------------------------------
[
  {
    "foo" : "100083087",
    "bar" :
    [
      {
        "let" : "DIV",
        "letID" : "100083088"
      }
    ]
  },
  {
    "foo" : "100032830",
    "bar" :
    [
      {
        "let" : "DIV",
        "letID" : "1000832333"
      },
      {
        "let" : "TET",
        "letID" : "2234832333"
      }
    ]
  }
]

BTW: JSON_EXISTS function doesn't support bind variables? so how to handle in this case, kindly advice.

demo@PDB1> variable x1 number
demo@PDB1> variable x2 number
demo@PDB1> exec :x1 := 100032830; :x2 := 2234832333;

PL/SQL procedure successfully completed.

demo@PDB1> select *
  2  from t
  3  where json_exists( x, '$.foo?(@==:x1)')
  4  and json_exists( x, '$.bar.letID?(@==:x2)') ;
select *
*
ERROR at line 1:
ORA-40597: JSON path expression syntax error ('$.foo?(@==:x1)')
JZN-00230: Unexpected characters in expression
at position 11

select json_query( doc ,'$' pretty )
from   example
where  json_exists( doc, '$.foo?(@==$foov)' passing :bind1 as "foov" )
and    json_exists( doc, '$.bar.letID?(@==$letv)' passing :bind2 as "letv" );

Team:

In this below example (was from 12.2 database), using "json_exists" in predicates uses the bitmap index.
However when using them in "json_table" conversion is not using the index.
could you please help us to understand why the optimizer is not using bitmap index in this case of JSON_TABLE conversion?

drop table t purge;
create table t( x clob check (x is json) );

declare
 l_data long;
begin
 l_data := q'# {
 "user": {
            "notifications": null, 
            "profile_use_background_image": true, 
            "statuses_count": 351, 
            "profile_background_color": "C0DEED", 
            "followers_count": 48, 
            "profile_image_url": "http://a1.twimg.com/profile_images/455128973/gCsVUnofNqqyd6tdOGevROvko1_500_normal.jpg", 
            "listed_count": 0, 
            "profile_background_image_url": "http://a3.twimg.com/a/1300479984/images/themes/theme1/bg.png", 
            "description": "watcha doin in my waters?", 
            "url": ":b1", 
            "id": :b2, 
            "contributors_enabled": false, 
            "following": null, 
            "utc_offset": -36000}
} #';

 insert /*+ append */ into t( x )
 select replace( replace( l_data, ':b1', substr( object_name,1,20) ) , ':b2', object_id )
 from all_objects , all_users
 where rownum <=100000;
 commit;
end;
/ 

exec dbms_stats.gather_table_stats(user,'T');

demo@PDB1> select count(*) ,
  2     count( distinct json_value( x, '$.user.url' error on error) ) cnt
  3  from t ;

  COUNT(*)        CNT
---------- ----------
   1000000      12883

demo@PDB1> create bitmap index t_idx on t( json_exists( x, '$.user.url') );

Index created.

demo@PDB1> set autotrace traceonly explain
demo@PDB1> select count(*)
  2  from t
  3  where json_exists(  x, '$.user.url' );

Execution Plan
----------------------------------------------------------
Plan hash value: 498595919

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |    13 |    94   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |       |     1 |    13 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |       | 10000 |   126K|    94   (0)| 00:00:01 |
|*  3 |    BITMAP INDEX FAST FULL SCAN| T_IDX |       |       |            |          |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(JSON_EXISTS2("X" FORMAT JSON , '$.user.url' FALSE ON ERROR)=1)

demo@PDB1> select count(*)
  2  from t, json_table( x, '$.user'
  3     columns(
  4             has_url number exists path '$.url') ) t2
  5  where t2.has_url = 1
  6  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3213541773

--------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |  1551 |    27M  (1)| 00:17:48 |
|   1 |  SORT AGGREGATE         |      |     1 |  1551 |            |          |
|   2 |   NESTED LOOPS          |      |    81M|   117G|    27M  (1)| 00:17:48 |
|   3 |    TABLE ACCESS FULL    | T    |  1000K|  1477M| 67518   (1)| 00:00:03 |
|*  4 |    JSONTABLE EVALUATION |      |       |       |            |          |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("P"."HAS_URL"=1)

demo@PDB1>
demo@PDB1> set autotrace off
demo@PDB1>

Sorry, but you say " this functionality is not implemented" ?

Here is the piece of testcase demonstrating the above piece of statement you quoted - which confirms that it uses index for expression (json_value) projected from JSON_TABLE clause.

but it is not working only for JSON_EXISTS condition, when projected using JSON_TABLE.

Is that only "json_exists" functionality is not implemented? Kindly advice.

drop table twitter_data1 purge;
create table twitter_data( twit clob
 constraint twitter1data_chk
 check( twit is json) );
 
declare
 l_sql long;
begin 
 l_sql := q'# {      
      "user": {
            "notifications": null, 
            "profile_use_background_image": true, 
            "statuses_count": 351, 
            "profile_background_color": "C0DEED", 
            "followers_count": 48, 
            "profile_image_url": "http://a1.twimg.com/profile_images/455128973/gCsVUnofNqqyd6tdOGevROvko1_500_normal.jpg", 
            "listed_count": 0, 
            "profile_background_image_url": "http://a3.twimg.com/a/1300479984/images/themes/theme1/bg.png", 
            "description": "watcha doin in my waters?", 
            "screen_name": "OldGREG85", 
            "default_profile": true, 
            "verified": false, 
            "time_zone": "Hawaii", 
            "profile_text_color": "333333", 
            "is_translator": false, 
            "profile_sidebar_fill_color": "DDEEF6", 
            "location": "Texas", 
            "id_str": "##x1##", 
            "default_profile_image": false, 
            "profile_background_tile": false, 
            "lang": "en", 
            "friends_count": 81, 
            "protected": false, 
            "favourites_count": 0, 
            "created_at": "Tue Oct 06 01:13:17 +0000 2009", 
            "profile_link_color": "0084B4", 
            "name": "GG", 
            "show_all_inline_media": false, 
            "follow_request_sent": null, 
            "geo_enabled": false, 
            "profile_sidebar_border_color": "C0DEED", 
            "url": "##x2##", 
            "id": 80177619, 
            "contributors_enabled": false, 
            "following": null, 
            "utc_offset": -36000
      }, 
      "id": 54691802283900930, 
      "coordinates": null, 
      "geo": null
} #';
 insert into twitter_data( twit )
 select replace( replace( l_sql, '"##x1##"', object_id ), '"##x2##"', '"'||substr( object_name,1,10)||'"' ) 
 from all_objects
 where rownum <=1000;
 commit;
end;
/ 
exec dbms_stats.gather_table_stats(user,'twitter_data',no_invalidate=>false);

demo@PDB1> create index user_id_idx
  2  on twitter_data(
  3     json_value( twit, '$.user.id_str'
  4             returning number
  5             error on error
  6             null on empty) );

Index created.

demo@PDB1> set autotrace traceonly explain
demo@PDB1> select *
  2  from twitter_data
  3  where json_value( twit ,'$.user.id_str' returning number) =5;

Execution Plan
----------------------------------------------------------
Plan hash value: 2813084340

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    10 |  1150 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TWITTER_DATA |    10 |  1150 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | USER_ID_IDX  |     4 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(JSON_VALUE("TWIT" FORMAT JSON , '$.user.id_str' RETURNING NUMBER ERROR ON
              ERROR NULL ON EMPTY)=5)

demo@PDB1> select twitter_data.*
  2  from twitter_data ,
  3      json_table( twit, '$.user'
  4          columns(
  5              id_str_val number path '$.id_str' )) t2
  6  where t2.id_str_val =5;

Execution Plan
----------------------------------------------------------
Plan hash value: 3095621313

-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              | 81680 |  9173K|   275   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                        |              | 81680 |  9173K|   275   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TWITTER_DATA |    10 |  1150 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | USER_ID_IDX  |     4 |       |     1   (0)| 00:00:01 |
|   4 |   JSONTABLE EVALUATION               |              |       |       |            |          |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(JSON_VALUE("TWIT" FORMAT JSON , '$.user.id_str' RETURNING NUMBER ERROR ON
              ERROR NULL ON EMPTY)=5)