query json data from oracle 12.1 having fields value with "."

Question

I have a table that has JSON data stored and I'm using json_exists functions in the query. Below is my sample data from the column for one of the rows.

{"fields":["query.metrics.metric1.field1",
           "query.metrics.metric1.field2",
           "query.metrics.metric1.field3",
           "query.metrics.metric2.field1",
           "query.metrics.metric2.field2"]}

I want all those rows which have a particular field. So, I'm trying below.

SELECT COUNT(*) 
  FROM my_table 
 WHERE JSON_EXISTS(fields, '$.fields[*]."query.metrics.metric1.field1"');

It does not give me any results back. Not sure what I'm missing here. Please help.

Thanks

Answer 1

You can use @ operator which refers to an occurrence of the array fields such as

SELECT *
  FROM my_table    
 WHERE JSON_EXISTS(fields, '$.fields?(@=="query.metrics.metric1.field1")')

Demo

Edit : The above case works for 12R2+, considering that it doesn't work for your version(12R1), try to use JSON_TABLE() such as

SELECT fields
  FROM my_table,   
  JSON_TABLE(fields, '$.fields[*]' COLUMNS ( js VARCHAR2(90) PATH '$' )) 
 WHERE js = 'query.metrics.metric1.field1' 

Demo