Simple JSON sql query on string array
I have a fairly large set of json files that I'm querying but am hung up on something that seems like it should be simple, here's a small example. I'm using Oracle 19 on Windows not that it should matter. I'm hoping to get two rows back. I can get one row back if I use "0" or "1" instead of "*" but the actual source I'm using can have a varying number of array elements
with mytab as (select '{"col" : ["blah1","blah2"]}' j_text from dual)
with mytab as (select '{"col" : ["blah1","blah2"]}' j_text from dual)
select d.*
from mytab n, json_table(n.j_text, '$' ERROR ON ERROR NULL ON EMPTY
0