Nested json data not captured by JSON_TABLE in oracle sql

Question

I'm using Oracle 12c(12.2) to read json data in a table.

SELECT        jt.name,
jt.employee_id, 
jt.company    
FROM JSON_TABLE ( BFILENAME ('DB_DIR', 'vv.json')

i've nested data in json output. The key:value in nested data start with a value "past_work": "N.A" for a record. for other many records below it, have actual values like

"past_work": [{ "company": "XXXXX",   "title": "XXXX"}]

but because first record done have value and start and end brackets [], oracle not capturing below records nested values. any idea how to capture below records?

Example: Actual data like below

  SELECT
      jt.company,
      jt.title
    FROM
      JSON_TABLE(
        '{
           "employee_data": [
             { "employee_id": "111",
               "past_work": "N/A"
             },
             { "employee_id": "222",
               "past_work": [
                 {"company": "XXXXX", "title": "XXXX"},
                 {"company": "YYYYY", "title": "YYYY"}
               ]
             },
             { "employee_id": "333",
               "past_work": [
                 {"company": "XXXXX", "title": "XXXX"},
                 {"company": "YYYYY", "title": "YYYY"}
                ]
             }
           ]
         }',
        '$.past_work[*]'
          COLUMNS (
            company VARCHAR2(100) PATH '$.company',
            title   VARCHAR2(100) PATH '$.title'
          )
      )
        AS jt

now when i execute above statment, i'm getting null for company values for emplyee_id 333 and below.

Thanks

Here is some useful material for how to privide useful examples to describe your needs : stackoverflow.com/help/minimal-reproducible-example — MatBailie
– MatBailie, Commented May 8, 2021 at 11:06
And what results do you want?? For past_work being N/A, what should be in the company and title columns of the result? — MatBailie
– MatBailie, Commented May 8, 2021 at 11:16
Your JSON path points to past_work element that is the root element, which is not for your case. To access those arrays, you need $.employee_data[*].past_work[*] — astentx
– astentx, Commented May 8, 2021 at 11:29
Thanks all for the Comments. Have asked product team to provide data in correct format. — Nitin Joshi
– Nitin Joshi, Commented May 11, 2021 at 8:30

MatBailie · Accepted Answer · 2021-05-08 11:22:38Z

4

First, the json snippet is malformed, it MUST be surrounded by {} in order to be parsable as a json object...

{"past_work": [{ "company": "XXXXX", "title": "XXXX"}]}

Then, you can tell the json parser that you want to pull the rows from the past_work element...

JSON_TABLE(<yourJsonString>, '$.past_work[*]')

The [*] tells the parser that past_work is an array, and to process that array in to rows of json objects, rather than just return the whole array as a single json object.

That gives something like...

SELECT
  jt.company,
  jt.title
FROM
  JSON_TABLE(
    '{
        "past_work": [
            {"company": "XXXXX", "title": "XXXX"},
            {"company": "YYYYY", "title": "YYYY"}
        ]
     }',
    '$.past_work[*]'
      COLUMNS (
        company VARCHAR2(100) PATH '$.company',
        title   VARCHAR2(100) PATH '$.title'
      )
  )
    AS jt

db<>fiddle demo

For more details, I recommend reading the docs:

https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973

EDIT: Updated example, almost a copy and paste from the docs

Please Read The Docs!

SELECT
  jt.*
FROM
  JSON_TABLE(
    '{
        "XX_data":[
          {
            "employee_id": "E1",
            "full_name":   "E1  Admin",
            "past_work":   "N/A"
          },
          {
            "employee_id": "E2",
            "full_name":   "E2  Admin",
            "past_work": [
              {"company": "E2 PW1 C", "title": "E2 PW1 T"},
              {"company": "E2 PW2 C", "title": "E2 PW2 T"},
            ]
          },
        ]
     }',
    '$.XX_data[*]'
      COLUMNS (
        employee_id VARCHAR2(100) PATH '$.employee_id',
        full_name   VARCHAR2(100) PATH '$.full_name',
        past_work   VARCHAR2(100) PATH '$.past_work',
        NESTED PATH '$.past_work[*]'
          COLUMNS (
            past_work_company VARCHAR2(100) PATH '$.company',
            past_work_title   VARCHAR2(100) PATH '$.title'
          )
      )
  )
    AS jt

Another db<>fiddle demo

edited May 8, 2021 at 11:22

answered May 8, 2021 at 10:50

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

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

2 Comments

Nitin Joshi Over a year ago

Thanks for the response. my actual data is like below and is properly formed in json format

{"XXX_data":[{"employee_id": "XXX",       "full_name": "XXXX  Admin", "past_work": [{ "company": "XXXXX",   "title": "XXXX"}]}

i just gave you nested data for example. now the there are many records and first record have "past_work": "N.A" no values and without [] but record below have many key:values. so how do i fetch that values. i'm getting null for all rows. Thanks

MatBailie Over a year ago

@NitinJoshi Edit your question to include a full and useful example json string, and the results you want from that string. Comments are not a good place for putting these details, and questions should include all the details required to describe your needs.

user5683823 · Accepted Answer · 2021-05-08 15:48:37Z

If past_work is supposed to be an array of past (company, title) pairs, then the proper way to encode "no history" is not to use a string value like "N/A", but instead you should use an empty array, as I show in the code below. If you do it your way, you can still extract the data, but it will be exceptionally messy. If you use JSON, use it correctly.

Also, you said you want to extract company and title. Just those? That makes no sense. Rather, you probably want to extract the employee id for each employee, along with the work history. In the work history, I add a column "for ordinality" (to show which company was first, which was second, etc.) If you don't need it, just leave it out.

To access nested columns, you must use the nested clause in the columns specification.

select employee_id, ord, company, title
from   json_table(
        '{
           "employee_data": [
             { "employee_id": "111",
               "past_work": [ ]
             },
             { "employee_id": "222",
               "past_work": [
                 {"company": "XXXXX", "title": "XXXX"},
                 {"company": "YYYYY", "title": "YYYY"}
               ]
             },
             { "employee_id": "333",
               "past_work": [
                 {"company": "XXXXX", "title": "XXXX"},
                 {"company": "YYYYY", "title": "YYYY"}
                ]
             }
           ]
         }',   '$.employee_data[*]'
               columns ( 
                         employee_id varchar2(10) path '$.employee_id',
                           nested path '$.past_work[*]'
                               columns (
                                         ord     for ordinality,
                                         company varchar2(10) path '$.company',
                                         title   varchar2(10) path '$.title'
                                       )
                       )
       ) jt
order by employee_id, ord;

Output:

EMPLOYEE_ID ORD COMPANY TITLE
----------- --- ------- -----
111                          
222           1 XXXXX   XXXX 
222           2 YYYYY   YYYY 
333           1 XXXXX   XXXX 
333           2 YYYYY   YYYY

Collectives™ on Stack Overflow

Nested json data not captured by JSON_TABLE in oracle sql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related