OraFAQ Forum: SQL & PL/SQL » Need to Retrieve data from JSON file in Oracle sql select query

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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 #654726 is a reply to message #654723]

Thu, 11 August 2016 06:16

Michel Cadot
Messages: 68634
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.

Report message to a moderator

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

msol25
Messages: 396
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'

Report message to a moderator

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

Michel Cadot
Messages: 68634
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.

Report message to a moderator

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

msol25
Messages: 396
Registered: June 2011

Senior Member

Thanks Michel

Report message to a moderator

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

Michel Cadot
Messages: 68634
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.

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: 68634
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...

Report message to a moderator

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: 9082
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.

Report message to a moderator

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: 68634
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.

Report message to a moderator