Oracle query to get nested key value pair

Question

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"
        }}}

kfinity · Accepted Answer · 2018-01-11 21:40:42Z

0

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

answered Jan 11, 2018 at 21:40

kfinity

9,1411 gold badge16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

Is there a way to return the actual key values, e.g. KEY_1, KEY_2 instead of the number that is generated by for ordinality

kfinity Over a year ago

Not easily, I think? The keys are like column names, they're hard to select without a data dictionary. It's been asked before, e.g. stackoverflow.com/questions/40424591/…

kfinity Over a year ago

In 12c it's a little easier in PL/SQL; they added a JSON_OBJECT_T type with a get_keys() function, so you can get a list of the key names, but you'd still have to write your own function, since it can't be called from SQL.

Collectives™ on Stack Overflow

Oracle query to get nested key value pair

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related