Home » SQL & PL/SQL » SQL & PL/SQL » Need to Retrieve data from JSON file in Oracle sql select query (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Need to Retrieve data from JSON file in Oracle sql select query [message #654723] |
Thu, 11 August 2016 05:51 |
|
msol25
Messages: 396 Registered: June 2011
|
Senior Member |
|
|
Dear Friends,
I have JSON Data in Table Column. I need to retrieve, Postcode from below mentioned Json file. Please can you provide logic for retrieving postcode value from below mentioned JSON String:
{"AddressType":{"UPRN":"6181940","USRN":"22700636","DTIFranchiseName":"Tower Hamlets and Newham","DTIFranchiseId":"373","primaryAddressableObject":"40","streetName":"HESSEL STREET","town":"LONDON","postcode":"E1 2LP","geographicCoordinates":{"latitude":"51.5136217","longitude":"-.0627108"},"opportunityMember":{"opportunityId":"715076 ","opportunityStatusId":"1010","opportunityMemberStatusId":"1010","externalOpportunityIdentifier":"0","primaryOpportunityUPRN ":"6136935","opportunityMemberLock":"N"},"PAFAddress":{"streetNumber":"40","thoroughfareName":"HESSEL STREET","posttown":"LONDON","postcode":"E1 2LP"},"networkDetails":{"distanceToDuct":"13.641","nodeMatchTypeId":"5"},"addressArea":{"region":"London East","postcodeAreaDescription":"London E","postcodeSector":"E1 2","postcodeDistrict":"E1","postcodeArea":"E"},"extendedAddressProperties":{"serviceabilityState":"1","serviceabilityStateDescription ":"Default state for address UPRN","opportunityState":"1","opportunityStateDescription":"Rejected - High Rise MDU","soFlag":"1","addressStatusType":"Approved","dnaAddressType":"Residential","buildingType":"MDU","dnaBuildingType ":"MDU","streetDensityByBuilding":"6.5","streetDensityByAddress":"4.55","adjustedDigLength":"4.55","buildingHeight":"13.5 ","buildingPremiseCount":"1","adjustedCost":"218.4","adjustedCostRange":"250","allowableAddress":"Y","cableMyStreetScore ":"0"}}}
[Updated on: Thu, 11 August 2016 12:13] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
Re: Need to Retrieve data from JSON file in Oracle sql select query [message #654759 is a reply to message #654744] |
Thu, 11 August 2016 12:44 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Here are the 2 first steps hoping you'll find the next one.
You want to find a "opportunityStatusId" so:
SQL> select substr(val,instr(val,'"opportunityStatusId"')) from t;
SUBSTR(VAL,INSTR(VAL,'"OPPORTUNITYSTATUSID"'))
------------------------------------------------------------------------------------------------------------------------
"opportunityStatusId":"1010","opportunityMemberStatusId":"1010","externalOpportunityIdentifier":"0","primaryOpportunityU
PRN ":"6136935","opportunityMemberLock":"N"},"PAFAddress":{"streetNumber":"40","thoroughfareName":"HESSEL STREET","postt
own":"LONDON","postcode":"E1 2LP"},"networkDetails":{"distanceToDuct":"13.641","nodeMatchTypeId":"5"},"addressArea":{"re
gion":"London East","postcodeAreaDescription":"London E","postcodeSector":"E1 2","postcodeDistrict":"E1","postcodeArea":
"E"},"extendedAddressProperties":{"serviceabilityState":"1","serviceabilityStateDescription ":"Default state for address
UPRN","opportunityState":"1","opportunityStateDescription":"Rejected - High Rise MDU","soFlag":"1","addressStatusType":
"Approved","dnaAddressType":"Residential","buildingType":"MDU","dnaBuildingType ":"MDU","streetDensityByBuilding":"6.5",
"streetDensityByAddress":"4.55","adjustedDigLength":"4.55","buildingHeight":"13.5 ","buildingPremiseCount":"1","adjusted
Cost":"218.4","adjustedCostRange":"250","allowableAddress":"Y","cableMyStreetScore ":"0"}}}
1 row selected.
Now you want value of it, it is some characters after:
SQL> select substr(val,instr(val,'"opportunityStatusId"')+length('"opportunityStatusId"')+2) from t;
SUBSTR(VAL,INSTR(VAL,'"OPPORTUNITYSTATUSID"')+LENGTH('"OPPORTUNITYSTATUSID"')+2)
------------------------------------------------------------------------------------------------------------------------
1010","opportunityMemberStatusId":"1010","externalOpportunityIdentifier":"0","primaryOpportunityUPRN ":"6136935","opport
unityMemberLock":"N"},"PAFAddress":{"streetNumber":"40","thoroughfareName":"HESSEL STREET","posttown":"LONDON","postcode
":"E1 2LP"},"networkDetails":{"distanceToDuct":"13.641","nodeMatchTypeId":"5"},"addressArea":{"region":"London East","po
stcodeAreaDescription":"London E","postcodeSector":"E1 2","postcodeDistrict":"E1","postcodeArea":"E"},"extendedAddressPr
operties":{"serviceabilityState":"1","serviceabilityStateDescription ":"Default state for address UPRN","opportunityStat
e":"1","opportunityStateDescription":"Rejected - High Rise MDU","soFlag":"1","addressStatusType":"Approved","dnaAddressT
ype":"Residential","buildingType":"MDU","dnaBuildingType ":"MDU","streetDensityByBuilding":"6.5","streetDensityByAddress
":"4.55","adjustedDigLength":"4.55","buildingHeight":"13.5 ","buildingPremiseCount":"1","adjustedCost":"218.4","adjusted
CostRange":"250","allowableAddress":"Y","cableMyStreetScore ":"0"}}}
1 row selected.
Now the value ends at the next ", so...
|
|
|
Re: Need to Retrieve data from JSON file in Oracle sql select query [message #654763 is a reply to message #654723] |
Fri, 12 August 2016 00:34 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
If you upgrade, then:
SCOTT@orcl_12.1.0.2.0> select banner from v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
5 rows selected.
SCOTT@orcl_12.1.0.2.0> create table tmp
2 (id raw(16) not null,
3 data clob,
4 constraint tmp_pk primary key (id),
5 constraint tmp_json_ck check (data is json))
6 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert into tmp (id, data) values (sys_guid(),
2 '{"AddressType":{"UPRN":"6181940","USRN":"22700636","DTIFranchiseName":"Tower Hamlets and Newham","DTIFranchiseId":"373","primaryAddressableObject":"40","streetName":"HESSEL STREET","town":"LONDON","postcode":"E1 2LP","geographicCoordinates":{"latitude":"51.5136217","longitude":"-.0627108"},"opportunityMember":{"opportunityId":"715076 ","opportunityStatusId":"1010","opportunityMemberStatusId":"1010","externalOpportunityIdentifier":"0","primaryOpportunityUPRN ":"6136935","opportunityMemberLock":"N"},"PAFAddress":{"streetNumber":"40","thoroughfareName":"HESSEL STREET","posttown":"LONDON","postcode":"E1 2LP"},"networkDetails":{"distanceToDuct":"13.641","nodeMatchTypeId":"5"},"addressArea":{"region":"London East","postcodeAreaDescription":"London E","postcodeSector":"E1 2","postcodeDistrict":"E1","postcodeArea":"E"},"extendedAddressProperties":{"serviceabilityState":"1","serviceabilityStateDescription ":"Default state for address UPRN","opportunityState":"1","opportunityStateDescription":"Rejected - High Rise MDU","soFlag":"1","addressStatusType":"Approved","dnaAddressType":"Residential","buildingType":"MDU","dnaBuildingType ":"MDU","streetDensityByBuilding":"6.5","streetDensityByAddress":"4.55","adjustedDigLength":"4.55","buildingHeight":"13.5 ","buildingPremiseCount":"1","adjustedCost":"218.4","adjustedCostRange":"250","allowableAddress":"Y","cableMyStreetScore ":"0"}}}')
3 /
1 row created.
SCOTT@orcl_12.1.0.2.0> column postcode format a20
SCOTT@orcl_12.1.0.2.0> column opportunitystatusid format a20
SCOTT@orcl_12.1.0.2.0> select a.data.AddressType.postcode as postcode,
2 a.data.AddressType.opportunityMember.opportunityStatusId as opportunityStatusId
3 from tmp a
4 /
POSTCODE OPPORTUNITYSTATUSID
-------------------- --------------------
E1 2LP 1010
1 row selected.
SCOTT@orcl_12.1.0.2.0> select a.data.AddressType.postcode as postcode
2 from tmp a
3 where a.data.AddressType.opportunityMember.opportunityStatusId = '1010'
4 /
POSTCODE
--------------------
E1 2LP
1 row selected.
|
|
|
Re: Need to Retrieve data from JSON file in Oracle sql select query [message #654785 is a reply to message #654763] |
Fri, 12 August 2016 11:08 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Or, in all versions:
SQL> with
2 data as (
3 select substr(val,instr(val,'"postcode"')+length('"postcode"')+2) val
4 from t
5 where instr(val,'"opportunityStatusId":"1010"') != 0
6 )
7 select substr(val, 1, instr(val,'"')-1) postcode
8 from data
9 /
POSTCODE
-------------------------------------------------------------------------------
E1 2LP
1 row selected.
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 14:03:54 CDT 2024
|