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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

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

Last updated: October 07, 2022 - 2:48 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 10K+ times! This question is

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 Chris 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   

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Is there any alternate way we can achieve the same?

A reader, April 02, 2020 - 6:04 pm UTC

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
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

Rajeshwaran, Jeyabal, April 03, 2020 - 6:58 am UTC

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
April 03, 2020 - 10:11 am UTC

Yep, though needs 12c.

DatabaseError: ORA-00920: invalid relational operator

A reader, April 03, 2020 - 12:49 pm UTC

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
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>


A reader, April 03, 2020 - 12:50 pm UTC


Is it possible in regex ?

A reader, April 03, 2020 - 12:56 pm UTC

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
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

Sandip

Sandip, October 06, 2022 - 5:32 am UTC

SELECT *
FROM
tjson,
xmltable
('/json'
passing apex_json.to_xmltype ( json_data )
columns
country varchar2(50) path 'country_codes',
regions varchar2(100) path 'regions'
) x;

and data come like

"C1" "JSON_DATA" "COUNTRY" "REGIONS"
1 "{""country_codes"": [""IND""], ""regions"": [""ASIA""]}" "IND" "ASIA"
2 "{""country_codes"": [""IND"",""USA""], ""regions"": [""ASIA"",""NA""]}" "INDUSA" "ASIANA"

Not correct. Please give me suggestions for the correct data...

Connor McDonald
October 07, 2022 - 2:48 am UTC

Test case with DDL and inserts please so we don't make any typos

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.