Forum Stats

  • 3,734,237 Users
  • 2,246,916 Discussions
  • 7,857,196 Comments

Discussions

JSON - nv pair in an array

Greetings

On 19c

I am creating views on JSON objects and am struggling to figure out how I can extract the value of key-value pair inside an array when the specific key that I need could be anywhere in the array.

Attached is a file containing 2 JSON objects.

Inside the array is a field called "ga_session_id" which could be in any position of the array.

I want to extract that field always. The SQL should return 2 rows, with a populated ga_session_id key as a column.

Any help would be greatly appreciated!



Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,395 Black Diamond
    Accepted Answer
    with example(jsondata) as (
    select
    '{
        "event_date": "20210424",
        "event_timestamp": "1619218890735678",
        "event_name": "form_submit",
        "event_params": [
            {
                "key": "Form",
                "value": {
                    "string_value": "form1"
                }
            },
            {
                "key": "page_location",
                "value": {
                    "string_value": "https://abc.com"
                }
            },
            {
                "key": "ga_session_id",
                "value": {
                    "int_value": "1619218875"
                }
            },
            {
                "key": "page_referrer",
                "value": {
                    "string_value": "https://mail.google.com/mail/u/0/"
                }
            }
        ]
    }' from dual
    union all
    select
    '{
        "event_date": "20210424",
        "event_timestamp": "1619236662721602",
        "event_name": "form_submit",
        "event_params": [
            {
                "key": "ga_session_id",
                "value": {
                    "int_value": "1619236622"
                }
            },
            {
                "key": "session_engaged",
                "value": {
                    "string_value": "1"
                }
            },
            {
                "key": "page_location",
                "value": {
                    "string_value": "https://xyz.com"
                }
            },
            {
                "key": "debug_mode",
                "value": {
                    "int_value": "1"
                }
            }
        ]
    }' from dual
    union all
    select
    '{
        "event_date": "20210424",
        "event_timestamp": "1619236662721602",
        "event_name": "form_submit",
        "event_params": [
            {
                "key": "session_engaged",
                "value": {
                    "string_value": "1"
                }
            },
            {
                "key": "page_location",
                "value": {
                    "string_value": "https://xyz.com"
                }
            },
            {
                "key": "debug_mode",
                "value": {
                    "int_value": "1"
                }
            }
        ]
    }' from dual)
    select rownum,jt.* from example left join
    json_table(jsondata,'$.event_params[*]'
    columns
            key varchar2(20) path '$.key',
            val number path '$.value.int_value'
    ) jt
    on key = 'ga_session_id'
    /
    
    
        ROWNUM KEY                         VAL
    ---------- -------------------- ----------
             1 ga_session_id        1619218875
             2 ga_session_id        1619236622
             3
    
    
    SQL>
    
    

    SY.

Answers

  • cormaco
    cormaco Member Posts: 1,559 Bronze Crown

    Here is one way:

    with example(jsondata) as (
    select
    '{
        "event_date": "20210424",
        "event_timestamp": "1619218890735678",
        "event_name": "form_submit",
        "event_params": [
            {
                "key": "Form",
                "value": {
                    "string_value": "form1"
                }
            },
            {
                "key": "page_location",
                "value": {
                    "string_value": "https://abc.com"
                }
            },
            {
                "key": "ga_session_id",
                "value": {
                    "int_value": "1619218875"
                }
            },
            {
                "key": "page_referrer",
                "value": {
                    "string_value": "https://mail.google.com/mail/u/0/"
                }
            }
        ]
    }' from dual
    union all
    select
    '{
        "event_date": "20210424",
        "event_timestamp": "1619236662721602",
        "event_name": "form_submit",
        "event_params": [
            {
                "key": "ga_session_id",
                "value": {
                    "int_value": "1619236622"
                }
            },
            {
                "key": "session_engaged",
                "value": {
                    "string_value": "1"
                }
            },
            {
                "key": "page_location",
                "value": {
                    "string_value": "https://xyz.com"
                }
            },
            {
                "key": "debug_mode",
                "value": {
                    "int_value": "1"
                }
            }
        ]
    }' from dual)
    select jt.* from example,
    json_table(jsondata,'$.event_params[*]'
    columns
            key varchar2(20) path '$.key',
            val number path '$.value.int_value'
    ) jt
    where key = 'ga_session_id'
    
    
    KEY                         VAL
    -------------------- ----------
    ga_session_id        1619218875
    ga_session_id        1619236622
    
    
    
    
  • GS613
    GS613 Member Posts: 73 Bronze Badge

    Thanks for taking the time to respond I really appreciate it

    Extension to the problem...

    What if a 3rd json object did not contain the “ga_session_id” key in the array (it’s not guaranteed that the array will always contain all fields) ?

    I would still need to return the null field (3 rows).With your method I’d be filtering out the 3rd row

    Thoughts?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,395 Black Diamond
    Accepted Answer
    with example(jsondata) as (
    select
    '{
        "event_date": "20210424",
        "event_timestamp": "1619218890735678",
        "event_name": "form_submit",
        "event_params": [
            {
                "key": "Form",
                "value": {
                    "string_value": "form1"
                }
            },
            {
                "key": "page_location",
                "value": {
                    "string_value": "https://abc.com"
                }
            },
            {
                "key": "ga_session_id",
                "value": {
                    "int_value": "1619218875"
                }
            },
            {
                "key": "page_referrer",
                "value": {
                    "string_value": "https://mail.google.com/mail/u/0/"
                }
            }
        ]
    }' from dual
    union all
    select
    '{
        "event_date": "20210424",
        "event_timestamp": "1619236662721602",
        "event_name": "form_submit",
        "event_params": [
            {
                "key": "ga_session_id",
                "value": {
                    "int_value": "1619236622"
                }
            },
            {
                "key": "session_engaged",
                "value": {
                    "string_value": "1"
                }
            },
            {
                "key": "page_location",
                "value": {
                    "string_value": "https://xyz.com"
                }
            },
            {
                "key": "debug_mode",
                "value": {
                    "int_value": "1"
                }
            }
        ]
    }' from dual
    union all
    select
    '{
        "event_date": "20210424",
        "event_timestamp": "1619236662721602",
        "event_name": "form_submit",
        "event_params": [
            {
                "key": "session_engaged",
                "value": {
                    "string_value": "1"
                }
            },
            {
                "key": "page_location",
                "value": {
                    "string_value": "https://xyz.com"
                }
            },
            {
                "key": "debug_mode",
                "value": {
                    "int_value": "1"
                }
            }
        ]
    }' from dual)
    select rownum,jt.* from example left join
    json_table(jsondata,'$.event_params[*]'
    columns
            key varchar2(20) path '$.key',
            val number path '$.value.int_value'
    ) jt
    on key = 'ga_session_id'
    /
    
    
        ROWNUM KEY                         VAL
    ---------- -------------------- ----------
             1 ga_session_id        1619218875
             2 ga_session_id        1619236622
             3
    
    
    SQL>
    
    

    SY.

  • GS613
    GS613 Member Posts: 73 Bronze Badge

    You got it! Thanks so much for both answers.

    I actually got to the same point as @cormaco hence the filter question, but the left join solved it.

    @Solomon Yakobson, @cormaco - legends, both of you!

Sign In or Register to comment.