Skip to Main Content
  • Questions
  • No results when using two or more JSON_TABLE statements

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jorge.

Asked: March 07, 2017 - 2:13 am UTC

Last updated: March 07, 2017 - 8:07 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

I'm using JSON on a column to store language translations.
The provided livesql link has all the steps to reproduce, but here's the basics:
create table px_projects2 ( 
    id            number        generated by default on null as identity (start with 1) primary key not null 
  , name_jtl      varchar2(500)   not null constraint px_projects2_name_tl_ck CHECK (name_jtl is json(strict)) 
  , description_jtl varchar2(500) not null constraint px_projects2_desc_tl_ck CHECK (description_jtl is json(strict)) 
  , alias         varchar2(32) 
  , active_ind    varchar2(1)   not null 
  , created_by    varchar2(60) default  
                    coalesce( 
                        sys_context('APEX$SESSION','app_user') 
                      , regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*') 
                      , sys_context('userenv','session_user') 
                    ) 
                    not null 
  , created_on    date         default sysdate not null 
  , constraint px_projects2_ck_active check (active_ind in ('Y', 'N')) 
) 
enable primary key using index
/
-- Insert two rows
insert into PX_PROJECTS2 (NAME_JTL,DESCRIPTION_JTL,ALIAS,ACTIVE_IND) values ( 
'[{"l":"en","tl":"Name 1 - English"}, 
  {"l":"fr","tl":"Nom 1 - Francais"}, 
  {"l":"es","tl":"Nombre 1 - Espanol"}]' 
,'[{"l":"en","tl":"Desc 1 - English"}, 
   {"l":"fr","tl":"Desc 1 - Francais"}, 
   {"l":"es","tl":"Desc 1 - Espanol"}]' 
,'Nom 1','Y');

insert into PX_PROJECTS2 (NAME_JTL,DESCRIPTION_JTL,ALIAS,ACTIVE_IND) values ('[{"l":"en","tl":"Name 2- English"},{"l":"fr","tl":"Nom 2 - Francais"},{"l":"es","tl":"Nom 2 - Espaniol"}]','[{"l":"en","tl":"Desc 2 - English"},{"l":"fr","tl":"Desc 2 - Francais"},{"l":"es","tl":"Desc 2 - Espaniol"}]','Nom 2','Y');

-- This query correctly displays ALL the data
select t.id 
     , jdn.lang 
     , jdn.tl name 
     , jdd.lang d_lang 
     , jdd.tl description 
  from px_projects2 t 
     , json_table(t.name_jtl, '$[*]' 
        columns ( 
                 lang varchar2(10)  path '$.l'
               , tl   varchar2(200) path '$.tl'
       )) jdn 
     , json_table(t.description_jtl, '$[*]' 
        columns ( 
                 lang varchar2(10)  path '$.l'
               , tl   varchar2(200) path '$.tl'
                )) jdd 
where jdn.lang = jdd.lang
/

-- This one correctly shows the data in English (lang = 'en')
select t.id 
     , jdn.lang 
     , jdn.tl name 
     , jdd.lang d_lang 
     , jdd.tl description 
  from px_projects2 t 
     , json_table(t.name_jtl, '$[*]' 
        columns ( 
                 lang varchar2(10)  path '$.l'
               , tl   varchar2(200) path '$.tl'
       )) jdn 
     , json_table(t.description_jtl, '$[*]' 
        columns ( 
                 lang varchar2(10)  path '$.l'
               , tl   varchar2(200) path '$.tl'
                )) jdd 
where jdn.lang = jdd.lang
  and jdn.lang = 'en'
/

-- But this one returns NOTHING!
select t.id 
     , jdn.lang 
     , jdn.tl name 
     , jdd.lang d_lang 
     , jdd.tl description 
  from px_projects2 t 
     , json_table(t.name_jtl, '$[*]' 
        columns ( 
                 lang varchar2(10)  path '$.l'
               , tl   varchar2(200) path '$.tl'
       )) jdn 
     , json_table(t.description_jtl, '$[*]' 
        columns ( 
                 lang varchar2(10)  path '$.l'
               , tl   varchar2(200) path '$.tl'
                )) jdd 
where jdn.lang = jdd.lang
  and jdn.lang = 'fr'
/


I've tried rewriting as WITH statements. But I still cannot get the languages other than the first.
Am I doing something wrong? Is this a bug?

Thank you for your help!



with LiveSQL Test Case:

and Connor said...

Thanks for the test case.

Looks like a bug to me, because the MATERIALIZE hint creates a different result (which obviously should never be the case)

with t1 as (
select t.id 
     , jdn.lang 
     , jdn.tl name 
  from px_projects2 t 
     , json_table(t.name_jtl, '$[*]' 
        columns ( 
                 lang varchar2(10)  path '$.l'
               , tl   varchar2(200) path '$.tl'
       )) jdn 
),
t2 as (
select t.id 
     , jdd.lang d_lang 
     , jdd.tl description 
  from px_projects2 t 
     , json_table(t.description_jtl, '$[*]' 
        columns ( 
                 lang varchar2(10)  path '$.l'
               , tl   varchar2(200) path '$.tl'
                )) jdd 
)
select t1.id, t1.lang, t1.name, t2.description
from t1, t2
where t1.id = t2.id
and   t1.lang = t2.d_lang
and t1.lang = 'fr';

with t1 as (
select /*+ materialize */ t.id 
     , jdn.lang 
     , jdn.tl name 
  from px_projects2 t 
     , json_table(t.name_jtl, '$[*]' 
        columns ( 
                 lang varchar2(10)  path '$.l'
               , tl   varchar2(200) path '$.tl'
       )) jdn 
),
t2 as (
select /*+ materialize */  t.id 
     , jdd.lang d_lang 
     , jdd.tl description 
  from px_projects2 t 
     , json_table(t.description_jtl, '$[*]' 
        columns ( 
                 lang varchar2(10)  path '$.l'
               , tl   varchar2(200) path '$.tl'
                )) jdd 
)
select t1.id, t1.lang, t1.name, t2.description
from t1, t2
where t1.id = t2.id
and   t1.lang = t2.d_lang
and t1.lang = 'fr';


Please send this along to Support. (It also reproduces on 12.2)

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

More to Explore

JSON

Need more information on JSON? Check out the JSON dev guide for the Oracle Database