I have a table in Oracle that has a JSON field, and I would access a key name instead of it's value, because it is variable and there's no way to know it.
For example, you can imagine that the first element in json root is this item, so you have one time $.keyname1 and another time $.keyname2.
I would check if the $[0] key name is the first or the second one.
As far as I know, it's still surprisingly hard to do this, since Oracle has not really been taking JSON seriously.
If you have Oracle 12.2 or above, you can use the get_keys() function for the PL/SQL type JSON_OBJECT_T.
If you have an earlier version (or would prefer an alternative to JSON_OBJECT_T), you can use the get_keys() function in the popular pljson library.
If you're in some other kind of restricted environment, that's too bad. You'll probably have to do some kind of awful substr on the JSON string. I don't recommend this.
select regexp_substr('{a:100, b:200, c:300}', '[^{:]+') as first_node from dual;
If you can't use one of the get_keys() functions and you want a better solution, I'd also suggest that you could modify the json-generation part of your application to include the variable node/key names using a known key name, so you can access them. E.g.
{ variable_keys : ["keyname1","keyname2"],
keyname1 : "value1",
keyname2 : "value2" }
But you'll still need PL/SQL for this, since the SQL JSON functions (json_value, json_query) will only accept string literals for the JSON Path Expression.
You can also use the sql aggregate function json_dataguide, or plsql DBMS_JSON.get_index_dataguide to get a list of keys. Wil take extra work to strip the names out of the results.
SQL example:
select dataguide.*
from (
select json_dataguide(json_column ) da
from json_table
where key = :1
) d ,
json_table(
da , '$[*]' COLUMNS (
f_path VARCHAR2(128 CHAR) PATH '$."o:path"'
, f_type VARCHAR2(128 CHAR) PATH '$."type"'
, f_length VARCHAR2(128 CHAR) PATH '$."o:length"'
)
) dataguide ;
