Oracle query to get nested key value pair

I am trying to extract the key and value pair from a nested JSON in an Oracle database. The JSON is saved as a CLOB in my database.

I am unsuccessful in getting the key name from my query:

  select key_v, var_name, sub_q
  from sampletable,
  json_table (sample_json , '$'
  columns (key_v varchar2(36) path '$."result"."exportColumnMap"',
  nested path '$."result"."exportColumnMap".*'
  columns (var_name varchar2(24) path '$."question"',
           sub_q varchar2(36) path '$."choice"' 
            )))
 where var_name is not null;

Ideally the resulting table from the select statement ends up as

KEY_1, ANSWER1, QUESTION1
KEY_2, ANSWER2, QUESTION2
KEY_3, ANSWER3, QUESTION3

sample_json:

{"result": 
    "exportColumnMap": {
        "KEY_1": {
            "choice": "ANSWER1", 
            "question": "QUESTION1"
        }, 
        "KEY_2": {
            "choice": "ANSWER2", 
            "question": "QUESTION2"
        }, 
        "KEY_3": {
            "choice": "ANSWER3", 
            "question": "QUESTION3"
        }}}

1 answer

  • answered 2018-01-11 19:50 kfinity

    So, a couple things. First, your JSON is invalid. The top-level layout is "{KEY: KEY: {VALUE}}", when it should be "{KEY: { KEY: {VALUE} }}", like so:

    {"result": 
        {"exportColumnMap": {
            "KEY_1": {
                "choice": "ANSWER1", 
                "question": "QUESTION1"
            }, 
            "KEY_2": {
                "choice": "ANSWER2", 
                "question": "QUESTION2"
            }, 
            "KEY_3": {
                "choice": "ANSWER3", 
                "question": "QUESTION3"
            }}}}
    

    Second, your query looks pretty good, but unfortunately I don't really know of an easy way to retrieve JSON key names. Your key_v column retrieves the value of KEY_1, KEY2, etc, which look like {"choice": "ANSWER1",...}, not the key name of "KEY_1". It would be much easier to use an ordinality column, like this, and if you really need the column values to have "KEY_" in front of them, paste that in later on. Or change the structure of your JSON to store all of the values you want to retrieve as values instead of key names.

    select key_v, var_name, sub_q
      from sampletable,
      json_table (sample_json , '$'
        columns (key_v FOR ORDINALITY,
          nested path '$."result"."exportColumnMap".*'
            columns (var_name varchar2(24) path '$."question"',
                    sub_q varchar2(36) path '$."choice"' 
      )))
      where var_name is not null;
    

    Output for this will look like:

    1, ANSWER1, QUESTION1
    2, ANSWER2, QUESTION2
    3, ANSWER3, QUESTION3