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!

Retireve value from Oracle feild for JSON string

S567May 22 2019 — edited Jun 4 2019

Hi Expert's,

I need help from you.I need SQL help to pull the customerfirstname from the order feild (which is a json string) ID 123.

Below is the table structure...

CREATE TABLE POINTS

( "ID" NUMBER(38,0) NOT NULL ENABLE,

"ORDER" CLOB

);

Below is one sample record please use for loading into above table.

ORDER

123

{"supplemental":{"orderTotal":549.99,"clientIpAddress":"10.32.0.202","recordedIpAddresses":["10.32.0.202","142.136.156.252"],"clientUserAgent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.121 Safari/537.36","orderType":"REGULAR","orderId":"24192","orderItems":[{"productSku":"887276269993","purchasePrice":"549.99","financePrice":"22.92"}]},"customerFirstName":"ROBERT","customerLastName":"FERGUSON","customerEmail":"KGHCGJKCADIFB@GMAIL.COM","customerPhone":"2111192985","billingAddressFirstName":"Robert","billingAddressLastName":"Ferguson","billingAddressLine1":"1729 LITTLE TEXAS RD","billingAddressLine2":"APT A13445","billingCity":"TRAVELERS REST","billingState":"SC","billingZipcode":"296908404","billingCountry":"US","shippingAddressFirstName":"Robert","shippingAddressLastName":"Ferguson","shippingAddressLine1":"1729 LITTLE TEXAS RD","shippingAddressLine2":"APT A13445","shippingCity":"TRAVELERS REST","shippingState":"SC","shippingZipcode":"296908404","shippingCountry":"US","shippingMethod":"FDX2DISG","taxes":33,"comcastAccountGuid":"80788791336","comcastAccountNo":"8245123940258286","customerDOB":"uyfCuHkQkM5oID9MDJganzE6fCtV8cElH+CvN16BH74GW3Oe3gmAvWyP9pnT7db71Sy2Zw==","customerSsnLastFour":"xssS+j8Iz6db9Edpgsg5Jcz4foklqx2R82yzsZ7gxrS748fMOPvHOP3lGaFlLoDmF2Eagg==","ccIssuer":"MasterCard","ccExpDate":"202112","ccLastFour":"5114","ccToken":"511234NDHDCY5114","agentId":"NoAgent","salesChannel":"Online","salesLocation":"Web","optinMarkComm":true,"optinSMSComm":true,"termsAndConditionsAccepted":true,"orderItems":[{"productSku":"887276269993","deviceImei":"","deviceIccid":"","ratePlan":"BTGSP002","insurancePlanId":"SMPP12","category":"device","phoneNumber":"","comcastUserGuid":"80788791336","userRole":"PRIMARY","recipientName":"Regular Order1","portingEligibilityValidated":false,"willPortPhoneNumber":false,"phoneCarrier":"","isFinanced":true,"waiveActivationFee":false,"isUpgrade":false}],"shippingAddressType":"RESIDENTIAL","customerType":"RESIDENTIAL","device":{"hdim":"VGa44j1eJNlY5BSo9z4ofjb75PaK4Vpjt.gEngMQEjZr_WhXTA2s.XTVV26y8GGEDd5ihORoVyFGh8cmvSuCKzIlnY6xljQlpRD_KQnH0FACGUPm8LKfAaZ4pAJZ7OQuyPBB2SE0XkzoMpwoNJ9z4oYYLzZ1kzDlSgyyITL5q8sgEV18u1.BUs_43wuZPup_nH2t05oaYAhrcpMxE6DBUr5xj6KkuKKjC74.gp4xf7_OLgiPFMtrs1OeyjaY2Koji0ainOctJVQdpPgPiAnDdYejftckuyPBDjaY2ftckkCoq75uQ0I5J.0cTlVVIU.1s2dI_AIQjvEodUW2vqCReif3ecGYieuFB8WNHRJlmX6QStNMtXjoay85icCmeug.ugBFY5CljQlpRxvEWMZyr6U5lY6SFGY5BNBypj.lRKOQj9QsqIUe7Sj9Oy3fw9juNokl_ie4VcvaeSPid_2AVvOIza.ELUmsBihIa0B9NOzEodbSTrLxFBc8rLLfjRckrHgJ10ibau6RAxyXiUSlKXVPgZ60_dyA3nt8I8.hWrUGOKiJ64XVA4.L9.gJ14Nc0RnA4.K9.mlF1aBONc5jaYZtNc5jaYZtNc6Mf9gJ.eig9lF1tW1gJ2bUV8ahrDzSjqL9.Mgen0pnBMCGTrvwLNApw.Eql","ipAddress":"10.32.0.202","serverTime":"2019-03-15T15:51:35.464Z","jsc":"NGa44j1eJNlY5BSo9z4ofjb75PaK4Vpjt.gEngMQEjZr_WhXTA2s.XTVV26y8GGEDd5ihORoVyFGh8cmvSuCKzIlnY6xljQlpRD_KQnH0FACGUPm8LKfAaZ4pAJZ7OQuyPBB2SE0XkzoMpwoNJ9z4oYYLzZ1kzDlSgyyITL5q8sgEV18u1.BUs_43wuZPup_nH2t05oaYAhrcpMxE6DBUr5xj6KkuKKjC74.gp4xf7_OLgiPFMtrs1OeyjaY2Koji0ainOctJVQdpPgPiAnDdYejftckuyPBDjaY2ftckkCoq75uQ0I5J.0cTlVVNk.i.uJtHoqvynx9MsFyxYM9fel9e3NvViZ21NL3b4BRcWqrTL4yNfx8jXGfe2Reif.Df237lYRQqwSMETnsKtEhlrlgNlrJ3NlY52EhQkBM4LLvmayCZruX6QmaUW9z1fyaCBBOQeWN9DtebLfeOFms8ocOzV2nfpkSFFnz.NmXVbuVSXaCTc5J3vfAKriJNQdL90.rInS2i4yzGMEiselFCLH2Vmoz4XOwZdZVLkvjcger92XlF4XVA4.L901gJ.iO_XV9_.ONc0I5BgJ2MnGmRgJ2MnGmRgJ2fp_L9.KML4Nc0RkWL90njk3nLvbUirvA4.AqKOVutaAacjQxyAB4uy..9B"},"dnisInfo":{}}

This post has been answered by Gaz in Oz on May 22 2019

Jump to Answer

Added on May 22 2019

15 comments

2,264 views