Query json dictionary data in SQL

Use JSON_TABLE and then UNPIVOT if you want the values in rows instead of columns:

SELECT *
FROM   (
  SELECT p.*
  FROM   table_name t
         CROSS JOIN
         JSON_TABLE(
           t.value,
           '$'
           COLUMNS (
             a PATH '$.a',
             b PATH '$.b',
             c PATH '$.c'
           )
         ) p
)
UNPIVOT ( value FOR key IN ( a, b, c ) );

So for some sample data:

CREATE TABLE table_name (
  value CLOB CONSTRAINT ensure_json CHECK (value IS JSON)
);

INSERT INTO table_name ( value ) VALUES ( '{"a":"value1", "b":"value2", "c":"value3"}' );

This outputs:

KEY | VALUE 
:-- | :-----
A   | value1
B   | value2
C   | value3

db<>fiddle here

---

If you want to do it dynamically then you can parse the JSON in PL/SQL and use GET_KEYS to get a collection of key names and then access the correct one by its position and correlate that to the value using FOR ORDINALITY:

CREATE FUNCTION get_key(
  pos  IN PLS_INTEGER,
  json IN CLOB
) RETURN VARCHAR2 
AS
  doc_keys JSON_KEY_LIST;
BEGIN
  doc_keys := JSON_OBJECT_T.PARSE ( json ).GET_KEYS;
  RETURN doc_keys( pos );
END get_key;
/

Then:

SELECT get_key( j.pos, t.value ) AS key,
       j.value
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.value,
         '$.*'
         COLUMNS (
           pos   FOR ORDINALITY,
           value PATH '$'
         )
       ) j;

Outputs:

KEY | VALUE 
:-- | :-----
a   | value1
b   | value2
c   | value3

db<>fiddle here