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 Go to next message
msol25
Messages: 368
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 #654726 is a reply to message #654723] Thu, 11 August 2016 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is your problem in doing so? Post what you already tried.
There are several "postcode" which one do you want?
If you answer one question don't forget to also answer the other one.

Re: Need to Retrieve data from JSON file in Oracle sql select query [message #654728 is a reply to message #654726] Thu, 11 August 2016 06:21 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Hi Michel,

I want to select the record where opportunityStatusId":"1010".I want to pick the data based on where clause like.

select  *
from    tmp
where   json_value(json,'opportunityStatusId') = '1010'

Re: Need to Retrieve data from JSON file in Oracle sql select query [message #654732 is a reply to message #654728] Thu, 11 August 2016 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I say answer ALL questions.
You could upgrade to 12c and use native JSON functions... or write your own.

Re: Need to Retrieve data from JSON file in Oracle sql select query [message #654743 is a reply to message #654732] Thu, 11 August 2016 08:42 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Thanks Michel
Re: Need to Retrieve data from JSON file in Oracle sql select query [message #654744 is a reply to message #654743] Thu, 11 August 2016 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you didn't try anythiing and are waiting for us to do your job.
I see no difficulties to get what you want using the good old SUBSTR and INSTR functions, or the REGEXP_% ones.
Just think how is JSON format.

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 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8790
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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.
Re: Need to Retrieve data from JSON file in Oracle sql select query [message #654938 is a reply to message #654785] Wed, 17 August 2016 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Re: Need to Retrieve data from JSON file in Oracle sql select query [message #667203 is a reply to message #654938] Thu, 14 December 2017 08:39 Go to previous message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Previous Topic: Help for getting information where external table used in database
Next Topic: existing state of package - Invalidated
Goto Forum:
  


Current Time: Thu Jan 18 09:02:32 CST 2018

Total time taken to generate the page: 0.01745 seconds