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:
PONumber | Reference | Requestor | Phone_Office | Phone_Mobile
|
---|
1600 | ABULL-20140421 | Alexis Bull | 909-555-7307 | 415-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
| type | number |
---|
1600 | office | 909-555-7307 |
1600 | mobile | 415-555-1234 |
this isn't what I'm after.