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

Answer 2

I have no idea how to "pattern match" on the array element, but just parsing the whole thing and filtering does the job.

with t(x, json) as (
  select 1, q'|{"fields":["a", "b"]}|' from dual union all
  select 2, q'|{"fields":["query.metrics.metric1.field1","query.metrics.metric1.field2","query.metrics.metric1.field3","query.metrics.metric2.field1","query.metrics.metric2.field2"]}|' from dual
)
select t.*
from t
where exists (
  select null
  from json_table(
    t.json,
    '$.fields[*]'
    columns (
      array_element varchar2(100) path '$'
    )
  )
  where array_element = 'query.metrics.metric1.field1'
);

In your code, you are accessing the field "query.metrics.metric1.field1" of an object in the fields array, and there is no such object (the elements are strings)...