Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

JSON_TABLE - create column from specific array item

Andrew VOct 23 2019 — edited Oct 24 2019

I haven't really done any work with JSON before and I'm trying to wrap my head around using JSON_TABLE to parse data into my database. I have a JSON document (sample from the docs):

{"PONumber" : 1600,

  "Reference" : "ABULL-20140421",

  "Requestor" : "Alexis Bull",

  "User" : "ABULL",

  "CostCenter" : "A50",

  "ShippingInstructions" : {"name" : "Alexis Bull",

  "Address": {"street" : "200 Sporting Green",

  "city" : "South San Francisco",

  "state" : "CA",

  "zipCode" : 99236,

  "country" : "United States of America"},

  "Phone" : [

     {"type" : "Office", "number" : "909-555-7307"},

     {"type" : "Mobile", "number" : "415-555-1234"}

     ]},

  "Special Instructions" : null,

  "AllowPartialShipment" : true,

  "LineItems" : [

     {"ItemNumber" : 1,

     "Part" : {"Description" : "One Magic Christmas",

     "UnitPrice" : 19.95,

     "UPCCode" : 13131092899},

     "Quantity" : 9.0},

     {"ItemNumber" : 2,

     "Part" : {"Description" : "Lethal Weapon",

     "UnitPrice" : 19.95,

     "UPCCode" : 85391628927},

     "Quantity" : 5.0}

     ]}

What I'm trying to do is fetch a value from a nested array and display is as a column. For example, I'd want the "Office" and "Mobile" phone numbers to be displayed like this:

PONumberReferenceRequestorPhone_OfficePhone_Mobile
1600ABULL-20140421Alexis Bull909-555-7307415-555-1234

What would be the best way to do this? All the examples I see in the documentation return each phone number type as an individual row like

PONumber
typenumber
1600office909-555-7307
1600mobile415-555-1234

this isn't what I'm after.

Comments
Post Details
Added on Oct 23 2019
4 comments
1,225 views