Skip to Main Content
  • Questions
  • Oracle 11g select query with Json field

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: April 02, 2020 - 12:32 pm UTC

Answered by: Chris Saxon - Last updated: April 06, 2020 - 12:17 am UTC

Category: PL/SQL - Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 1000+ times

You Asked

I want to fetch data from below table.

Table Name: Insight

Id  |  Name      |  JSON_DATA
----+------------+-------------------------------------------------------------
1   |  Sitansu.  |  {"country_codes": ["IND"],  "regions": ["ASIA"]}
2   |  Sitansu.  |  {"country_codes": ["IND","USA"],  "regions": ["ASIA","NA"]}


How to fetch data from this Insight table based on region like “NA” . But I don’t want to fetch data by using like query.

Example: country_codes == IND then it should show 2 records as per above table example. Also I want to fetch data with multiple country code.

Oracle version :

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

and we said...

On 11g your options are limited. It's probably easiest to install APEX so you can use the APEX_JSON.

This includes a JSON-to-XML conversion function. By passing the data to that, you can use XMLTable to extract the relevant fields. This does XML-to-relational conversion. So you can query the data like regular columns:

create table t (
  c1 int, json_data varchar2(100)
);

insert into t values ( 1, '{"country_codes": ["IND"], "regions": ["ASIA"]}' );
insert into t values ( 2, '{"country_codes": ["IND","USA"], "regions": ["ASIA","NA"]}' );
commit;

select * 
from   t, xmltable(
  '/json/regions/row'
  passing apex_json.to_xmltype ( json_data )
  columns
    code varchar2(100) path '/'
) x
where  x.code = 'NA';

C1    JSON_DATA                                                     CODE   
    2 {"country_codes": ["IND","USA"], "regions": ["ASIA","NA"]}    NA  


But really, if you're doing lots of JSON processing, you should upgrade so you can use the in-built JSON functions. For example, JSON_table:

select * 
from   t, json_table (
  json_data, '$.regions[*]'
  columns (
    code path '$'
  )
) j
where  j.code = 'NA';

C1    JSON_DATA                                                     CODE   
    2 {"country_codes": ["IND","USA"], "regions": ["ASIA","NA"]}    NA   

and you rated our response

  (5 ratings)

Reviews

Is there any alternate way we can achieve the same?

April 02, 2020 - 6:04 pm UTC

Reviewer: A reader

Is there any alternate way we can achieve the same?

Can we achieve same thing in PL/SQL without using Apex_Json ?
Or
Apex_Json is Open source ?

Connor McDonald

Followup  

April 03, 2020 - 2:37 am UTC

APEX_JSON comes with Application Express which is a free component of the Oracle database.

Another option to json_table

April 03, 2020 - 6:58 am UTC

Reviewer: Rajeshwaran, Jeyabal

Another option to json_table is using json_exists.

something like this:

demo@PDB1> select *
  2  from t
  3  where json_exists( json_data, '$.regions?(@=="NA")');

        C1 JSON_DATA
---------- -----------------------------------------------------------------
         2 {"country_codes": ["IND","USA"], "regions": ["ASIA","NA"]}

1 row selected.

Chris Saxon

Followup  

April 03, 2020 - 10:11 am UTC

Yep, though needs 12c.

DatabaseError: ORA-00920: invalid relational operator

April 03, 2020 - 12:49 pm UTC

Reviewer: A reader

JSON_DATA is a CLOB data type

select *
from t
where json_exists(JSON_DATA, '$.regions?(@=="NA")')

DatabaseError: ORA-00920: invalid relational operator
Connor McDonald

Followup  

April 06, 2020 - 12:16 am UTC

What version? I get this on mine

SQL>
SQL> create table t (
  2    c1 int, json_data varchar2(100)
  3  );

Table created.

SQL>
SQL> insert into t values ( 1, '{"country_codes": ["IND"], "regions": ["ASIA"]}' );

1 row created.

SQL> insert into t values ( 2, '{"country_codes": ["IND","USA"], "regions": ["ASIA","NA"]}' );

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select *
  2  from t
  3  where json_exists(JSON_DATA, '$.regions?(@=="NA")');

        C1 JSON_DATA
---------- ----------------------------------------------------------------------------------------------------
         2 {"country_codes": ["IND","USA"], "regions": ["ASIA","NA"]}

1 row selected.

SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t (
  2    c1 int, json_data clob
  3  );

Table created.

SQL>
SQL> insert into t values ( 1, '{"country_codes": ["IND"], "regions": ["ASIA"]}' );

1 row created.

SQL> insert into t values ( 2, '{"country_codes": ["IND","USA"], "regions": ["ASIA","NA"]}' );

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select *
  2  from t
  3  where json_exists(JSON_DATA, '$.regions?(@=="NA")');

        C1 JSON_DATA
---------- --------------------------------------------------------------------------------
         2 {"country_codes": ["IND","USA"], "regions": ["ASIA","NA"]}

1 row selected.

SQL>
SQL>


April 03, 2020 - 12:50 pm UTC

Reviewer: A reader


Is it possible in regex ?

April 03, 2020 - 12:56 pm UTC

Reviewer: A reader

We have very Complex Architecture. So we can not change the database version from 11g to any other latest version. Other thing is Apex_Json we can use. But need to install that in our system but it is long term solution.

If is there any other option except Apex_Json Or 12c version upgrade?

Connor McDonald

Followup  

April 06, 2020 - 12:17 am UTC

Unless you want to invent your own with SUBSTR, INSTR etc (which I would not recommend), then APEX_JSON would be your best bet.

Remember - installing APEX doesn't mean you have to USE it (even though its very cool). You just install it to get access to some of these cool utilities like APEX_JSON

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.